查找具有在许多完全匹配记录,以一对多的关系
我有一个看起来像这三个表:查找具有在许多完全匹配记录,以一对多的关系
PROD
Prod_ID|Desc
------------
P1|Foo1
P2|Foo2
P3|Foo3
P4|Foo4
...
RAM
Ram_ID|Desc
------------
R1|Bar1
R2|Bar2
R3|Bar3
R4|Bar4
...
PROD_RAM
Prod_ID|Ram_ID
------------
P1|R1
P2|R2
P3|R1
P3|R2
P3|R3
P4|R3
P5|R1
P5|R2
...
之间PROD和RAM有由PROD_RAM表中描述的许多一对多的关系。
给定一组Ram_ID
像(R1,R3)
我想找到所有具有完全相同ONE或在给定的RAM
的ALL的PROD
。
鉴于(R1,R3)
应返回例如P1
,P4
和P5
; P3
不应退还,因为有R1
和R3
,但也R2
。
什么来获得最快的查询都具有完全相同ONE或给定RAM
集的Ram_ID
的ALL的PROD
?
编辑:
的PROD_RAM
表可以包含关系比1-> 3更大,所以,对于计数 “硬编码” 检查= 1 OR = 2不是一个可行的解决方案。
你可以尝试的速度另一种解决办法是这样的
;WITH CANDIDATES AS (
SELECT pr1.Prod_ID
, pr2.Ram_ID
FROM PROD_RAM pr1
INNER JOIN PROD_RAM pr2 ON pr2.Prod_ID = pr1.Prod_ID
WHERE pr1.Ram_ID IN ('R1', 'R3')
)
SELECT *
FROM CANDIDATES
WHERE CANDIDATES.Prod_ID NOT IN (
SELECT Prod_ID
FROM CANDIDATES
WHERE Ram_ID NOT IN ('R1', 'R3')
)
,或者如果你不喜欢重复设置条件
;WITH SUBSET (Ram_ID) AS (
SELECT 'R1'
UNION ALL SELECT 'R3'
)
, CANDIDATES AS (
SELECT pr1.Prod_ID
, pr2.Ram_ID
FROM PROD_RAM pr1
INNER JOIN PROD_RAM pr2 ON pr2.Prod_ID = pr1.Prod_ID
INNER JOIN SUBSET s ON s.Ram_ID = pr1.Ram_ID
)
, EXCLUDES AS (
SELECT Prod_ID
FROM CANDIDATES
LEFT OUTER JOIN SUBSET s ON s.Ram_ID = CANDIDATES.Ram_ID
WHERE s.Ram_ID IS NULL
)
SELECT *
FROM CANDIDATES
LEFT OUTER JOIN EXCLUDES ON EXCLUDES.Prod_ID = CANDIDATES.Prod_ID
WHERE EXCLUDES.Prod_ID IS NULL
测试了你的第一个解决方案a nd它的工作:) – systempuntoout 2011-06-14 09:12:03
@systempuntoout - 现在你让我想知道...和第二? – 2011-06-14 17:26:45
还没有尝试过 – systempuntoout 2011-06-15 06:29:06
SELECT Prod_ID
FROM
(SELECT Prod_ID
, COUNT(*) AS cntAll
, COUNT(CASE WHEN Ram_ID IN (1,3)
THEN 1
ELSE NULL
END
) AS cntGood
FROM PROD_RAM
GROUP BY Prod_ID
) AS grp
WHERE cntAll = cntGood
AND (cntGood = 1
OR cntGood = 2 --- number of items in list (1,3)
)
完全不知道这是否是最快的方法。您必须尝试使用不同的方法来编写此查询(使用JOIN
s和NOT EXISTS
)并测试速度。要做到这一点
一种方式是类似以下内容:
SELECT PROD.Prod_ID FROM PROD WHERE
(SELECT COUNT(*) FROM PROD_RAM WHERE PROD_RAM.Prod_ID = PROD.Prod_ID) > 0 AND
(SELECT COUNT(*) FROM PROD_RAM WHERE PROD_RAM.Prod_ID = PROD.Prod_ID AND PROD.Ram_ID <>
IFNULL((SELECT TOP 1 Ram_ID FROM PROD_RAM WHERE PROD_RAM.Prod_ID = PROD.Prod_ID),0)) = 0
+1问清楚可理解的问题 – Stuti 2011-06-14 07:38:19