有那么一個(gè)小的需求，我現(xiàn)在有一張產(chǎn)品表，那么業(yè)務(wù)部門需要讓我們進(jìn)行一個(gè)統(tǒng)計(jì)：
他告訴了我們“我們需要一張擁有我指定的同樣配置的產(chǎn)品列表！”
話音剛落尸疆，苦逼的我們程序員就要開始執(zhí)行任務(wù)啦！
其實(shí)業(yè)務(wù)場(chǎng)景很簡(jiǎn)單惶岭，寫這個(gè)小文章只是為了讓有同樣需求的同學(xué)們少走彎路寿弱，特此記錄了下來(lái)。
表結(jié)構(gòu)：（我利用臨時(shí)表）
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE name LIKE '%#ProductPropertyInfo%' and TYPE='U')
DROP TABLE #ProductInfo
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE name LIKE '%#ProductPropertyInfo%' and TYPE='U')
DROP TABLE #ProductPropertyInfo
CREATE TABLE #ProductInfo(
[產(chǎn)品ID] [int] NULL,
[產(chǎn)品名稱] nvarchar(50) NULL,
[產(chǎn)品型號(hào)] nvarchar(50) NULL
)
CREATE TABLE #ProductPropertyInfo(
[產(chǎn)品ID] [int] NULL,
[屬性配置] nvarchar(50) NULL
)
插入臨時(shí)數(shù)據(jù)
INSERT INTO #ProductInfo([產(chǎn)品ID],[產(chǎn)品名稱],[產(chǎn)品型號(hào)])VALUES(1,'長(zhǎng)城汽車','C50');
INSERT INTO #ProductInfo([產(chǎn)品ID],[產(chǎn)品名稱],[產(chǎn)品型號(hào)])VALUES(2,'長(zhǎng)城汽車','C30');
INSERT INTO #ProductInfo([產(chǎn)品ID],[產(chǎn)品名稱],[產(chǎn)品型號(hào)])VALUES(3,'長(zhǎng)城汽車','H5');
INSERT INTO #ProductInfo([產(chǎn)品ID],[產(chǎn)品名稱],[產(chǎn)品型號(hào)])VALUES(4,'長(zhǎng)城汽車','H6');
INSERT INTO #ProductInfo([產(chǎn)品ID],[產(chǎn)品名稱],[產(chǎn)品型號(hào)])VALUES(5,'奧迪汽車','A4');
INSERT INTO #ProductInfo([產(chǎn)品ID],[產(chǎn)品名稱],[產(chǎn)品型號(hào)])VALUES(6,'奧迪汽車','A6');
INSERT INTO #ProductInfo([產(chǎn)品ID],[產(chǎn)品名稱],[產(chǎn)品型號(hào)])VALUES(7,'奧迪汽車','A8');
INSERT INTO #ProductInfo([產(chǎn)品ID],[產(chǎn)品名稱],[產(chǎn)品型號(hào)])VALUES(8,'奧迪汽車','A3');
INSERT INTO #ProductPropertyInfo([產(chǎn)品ID],[屬性配置])VALUES(1,'天窗');
INSERT INTO #ProductPropertyInfo([產(chǎn)品ID],[屬性配置])VALUES(1,'電加熱座椅');
INSERT INTO #ProductPropertyInfo([產(chǎn)品ID],[屬性配置])VALUES(1,'渦輪增壓');
INSERT INTO #ProductPropertyInfo([產(chǎn)品ID],[屬性配置])VALUES(2,'天窗');
INSERT INTO #ProductPropertyInfo([產(chǎn)品ID],[屬性配置])VALUES(3,'渦輪增壓');
INSERT INTO #ProductPropertyInfo([產(chǎn)品ID],[屬性配置])VALUES(3,'電加熱座椅');
INSERT INTO #ProductPropertyInfo([產(chǎn)品ID],[屬性配置])VALUES(3,'天窗');
INSERT INTO #ProductPropertyInfo([產(chǎn)品ID],[屬性配置])VALUES(4,'渦輪增壓');
INSERT INTO #ProductPropertyInfo([產(chǎn)品ID],[屬性配置])VALUES(4,'精英輪轂');
INSERT INTO #ProductPropertyInfo([產(chǎn)品ID],[屬性配置])VALUES(4,'電加熱座椅');
INSERT INTO #ProductPropertyInfo([產(chǎn)品ID],[屬性配置])VALUES(4,'天窗');
INSERT INTO #ProductPropertyInfo([產(chǎn)品ID],[屬性配置])VALUES(4,'GPS');
INSERT INTO #ProductPropertyInfo([產(chǎn)品ID],[屬性配置])VALUES(8,'天窗');
INSERT INTO #ProductPropertyInfo([產(chǎn)品ID],[屬性配置])VALUES(8,'渦輪增壓');
INSERT INTO #ProductPropertyInfo([產(chǎn)品ID],[屬性配置])VALUES(8,'電加熱座椅');
INSERT INTO #ProductPropertyInfo([產(chǎn)品ID],[屬性配置])VALUES(5,'自動(dòng)泊車');
INSERT INTO #ProductPropertyInfo([產(chǎn)品ID],[屬性配置])VALUES(5,'電加熱座椅');
INSERT INTO #ProductPropertyInfo([產(chǎn)品ID],[屬性配置])VALUES(5,'天窗');
INSERT INTO #ProductPropertyInfo([產(chǎn)品ID],[屬性配置])VALUES(5,'渦輪增壓');
INSERT INTO #ProductPropertyInfo([產(chǎn)品ID],[屬性配置])VALUES(6,'自動(dòng)泊車');
INSERT INTO #ProductPropertyInfo([產(chǎn)品ID],[屬性配置])VALUES(6,'電加熱座椅');
INSERT INTO #ProductPropertyInfo([產(chǎn)品ID],[屬性配置])VALUES(6,'天窗');
INSERT INTO #ProductPropertyInfo([產(chǎn)品ID],[屬性配置])VALUES(6,'渦輪增壓');
INSERT INTO #ProductPropertyInfo([產(chǎn)品ID],[屬性配置])VALUES(6,'精英輪轂');
INSERT INTO #ProductPropertyInfo([產(chǎn)品ID],[屬性配置])VALUES(6,'GPS');
INSERT INTO #ProductPropertyInfo([產(chǎn)品ID],[屬性配置])VALUES(7,'自動(dòng)泊車');
INSERT INTO #ProductPropertyInfo([產(chǎn)品ID],[屬性配置])VALUES(7,'電加熱座椅');
INSERT INTO #ProductPropertyInfo([產(chǎn)品ID],[屬性配置])VALUES(7,'天窗');
INSERT INTO #ProductPropertyInfo([產(chǎn)品ID],[屬性配置])VALUES(7,'渦輪增壓');
INSERT INTO #ProductPropertyInfo([產(chǎn)品ID],[屬性配置])VALUES(7,'精英輪轂');
INSERT INTO #ProductPropertyInfo([產(chǎn)品ID],[屬性配置])VALUES(7,'防彈玻璃');
INSERT INTO #ProductPropertyInfo([產(chǎn)品ID],[屬性配置])VALUES(7,'GPS');
查看數(shù)據(jù)效果：
從模擬數(shù)據(jù)上可以直觀的看到我們所要查詢的數(shù)據(jù)按灶，我們要根據(jù)配置來(lái)選車症革，根據(jù)我們想要的功能來(lái)查看有那款車子符合我的需求：
語(yǔ)句：（假設(shè)我目前需要擁有：“精英輪轂”和“GPS”功能特性的車子）
SELECT * FROM #ProductInfo p INNER JOIN (
SELECT
[產(chǎn)品ID]
FROM #ProductPropertyInfo WHERE [屬性配置] IN ('精英輪轂','GPS')
GROUP BY [產(chǎn)品ID] HAVING(COUNT(DISTINCT [屬性配置]) > 1)) y ON p.[產(chǎn)品ID] = y.[產(chǎn)品ID]
查詢特性的語(yǔ)句完全取決于：[屬性配置] IN ('精英輪轂','GPS') 想要什么配置就有什么配置啦！
本文章只作為基礎(chǔ)SQL語(yǔ)句的功能實(shí)現(xiàn)部分,如果有更好的方式也可以一起交流鸯旁。
