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

2015-07-21

3433
0
SQL
2015-07-24

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

環境

SQL 2012

問題

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

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

範例


--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;

所以如要找「客戶」為「亂馬客，並且「金額大於「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 = '亂馬客');

因為 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 = '亂馬客'
	);
要找客戶為亂馬客，或是金額大於 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 = '亂馬客'
	);
因為針對 ITEM_NAME & ITEM_VALUE 去 Search ，如果資料多的話，所以可以針對 ITEM_NAME 建立 Index 哦!
CREATE INDEX IX_FORM_DETAILS ON dbo.FORM_DETAILS
(ITEM_NAME, FORM_ID);
所以如果大家有遇到這種狀況，可以用這種方式哦!

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

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