[SQL]Key/Value pair Table 的查詢方式

  • 2916
  • 0
  • SQL
  • 2015-07-24

有 Key/Value pair 的Table嗎?
要針對 Value 要如何查詢呢?

環境

SQL 2012

問題

我們有個系統它的資料是以 KeyValuePair 的方式儲存。

如果要針對這樣的方式要如何查詢呢? 例如以下的UI,

image

 

範例

 


--DROP TABLE FORMS;
CREATE TABLE FORMS
(
ID INT PRIMARY KEY,
FORM_NAME NVARCHAR(100) 
);
go
CREATE TABLE FORM_DETAILS
(
ID INT IDENTITY(1,1) PRIMARY KEY,
FORM_ID INT,
ITEM_NAME VARCHAR(30),
ITEM_VALUE NVARCHAR(128)
);
go
-- 新增測試資料 表單 1 ~ 5
INSERT INTO FORMS(ID, FORM_NAME) VALUES(1, '表單-1');
INSERT INTO FORM_DETAILS(FORM_ID, ITEM_NAME, ITEM_VALUE)
VALUES(1, '客戶', '亂馬客');
INSERT INTO FORM_DETAILS(FORM_ID, ITEM_NAME, ITEM_VALUE)
VALUES(1, '金額', '100');

INSERT INTO FORMS(ID, FORM_NAME) VALUES(2, '表單-2');
INSERT INTO FORM_DETAILS(FORM_ID, ITEM_NAME, ITEM_VALUE)
VALUES(2, '客戶', '小小兵');
INSERT INTO FORM_DETAILS(FORM_ID, ITEM_NAME, ITEM_VALUE)
VALUES(2, '金額', '1000');

INSERT INTO FORMS(ID, FORM_NAME) VALUES(3, '表單-3');
INSERT INTO FORM_DETAILS(FORM_ID, ITEM_NAME, ITEM_VALUE)
VALUES(3, '客戶', '大天狗');
INSERT INTO FORM_DETAILS(FORM_ID, ITEM_NAME, ITEM_VALUE)
VALUES(3, '金額', '900');


INSERT INTO FORMS(ID, FORM_NAME) VALUES(4, '表單-4');
INSERT INTO FORM_DETAILS(FORM_ID, ITEM_NAME, ITEM_VALUE)
VALUES(4, '客戶', '亂馬客');
INSERT INTO FORM_DETAILS(FORM_ID, ITEM_NAME, ITEM_VALUE)
VALUES(4, '金額', '9000');

INSERT INTO FORMS(ID, FORM_NAME) VALUES(5, '表單-5');
INSERT INTO FORM_DETAILS(FORM_ID, ITEM_NAME, ITEM_VALUE)
VALUES(5, '客戶', '亂馬客');
INSERT INTO FORM_DETAILS(FORM_ID, ITEM_NAME, ITEM_VALUE)
VALUES(5, '金額', '500');

SELECT * FROM FORMS;
SELECT * FROM FORM_DETAILS;

image

 

所以如要找「客戶」為「亂馬客,並且「金額大於「100」小於「1,000」的表單 (FORM_ID為5)。

如果直接將這些條件組合起來,就無法找到資料,如下,


SELECT * 
FROM FORM_DETAILS D
WHERE (D.ITEM_NAME = '金額' AND IIF(TRY_PARSE(D.ITEM_VALUE AS INT) IS NULL, 0, CAST(D.ITEM_VALUE AS INT)) > 100)
AND (D.ITEM_NAME = '金額' AND IIF(TRY_PARSE(D.ITEM_VALUE AS INT) IS NULL, 0, CAST(D.ITEM_VALUE AS INT)) < 1000)
AND (D.ITEM_NAME = '客戶' AND D.ITEM_VALUE = '亂馬客');

image

因為 D.ITEM_NAME = '客戶' AND D.ITEM_NAME = '金額' 的查詢條件就無法查到任何的資料。

 

解決方式

所以要將這些查詢條件拆解開來組合,由 M.ID 去 IN 那些條件 ,如下,


SELECT * 
FROM FORMS M
WHERE 
	M.ID IN 
	(SELECT D.FORM_ID
	FROM FORM_DETAILS D
	WHERE D.ITEM_NAME = '金額'
	AND IIF(TRY_PARSE(D.ITEM_VALUE AS INT) IS NULL, 0, CAST(D.ITEM_VALUE AS INT)) > 100 
	)
AND
	M.ID IN 
	(
	SELECT D.FORM_ID
	FROM FORM_DETAILS D
	WHERE D.ITEM_NAME = '金額'
	AND IIF(TRY_PARSE(D.ITEM_VALUE AS INT) IS NULL, 0, CAST(D.ITEM_VALUE AS INT)) < 1000
	)
AND
	M.ID IN 
	(
	SELECT D.FORM_ID
	FROM FORM_DETAILS D
	WHERE D.ITEM_NAME = '客戶' AND D.ITEM_VALUE = '亂馬客'
	);

image

 

要找客戶為亂馬客,或是 金額大於 100 小於 1000 的表單 呢?  (FORM_ID為1, 3, 4, 5)


SELECT * 
FROM FORMS M
WHERE 
	M.ID IN 
	(SELECT D.FORM_ID
	FROM FORM_DETAILS D
	WHERE D.ITEM_NAME = '金額'
	AND IIF(TRY_PARSE(D.ITEM_VALUE AS INT) IS NULL, 0, CAST(D.ITEM_VALUE AS INT)) > 100 
	)
AND
	M.ID IN 
	(
	SELECT D.FORM_ID
	FROM FORM_DETAILS D
	WHERE D.ITEM_NAME = '金額'
	AND IIF(TRY_PARSE(D.ITEM_VALUE AS INT) IS NULL, 0, CAST(D.ITEM_VALUE AS INT)) < 1000
	)
OR
	M.ID IN 
	(
	SELECT D.FORM_ID
	FROM FORM_DETAILS D
	WHERE D.ITEM_NAME = '客戶' AND D.ITEM_VALUE = '亂馬客'
	);

image

 

因為針對 ITEM_NAME & ITEM_VALUE 去 Search ,如果資料多的話,所以可以針對 ITEM_NAME 建立 Index 哦!


CREATE INDEX IX_FORM_DETAILS ON dbo.FORM_DETAILS
(ITEM_NAME, FORM_ID);

所以如果大家有遇到這種狀況,可以用這種方式哦!

我們就是搭配 jQuery QueryBuilder 來呈現UI的哦!

如果有更好的方式,也請讓我知道,謝謝大家。

參考資料

Key/Value pair table design and SQL query question

Hi, 

亂馬客Blog已移到了 「亂馬客​ : Re:從零開始的軟體開發生活

請大家繼續支持 ^_^