没有相同的数据啊SELECT * FROM t
WHERE P_ID='A1' /*
P_ID P_PROPERTY P_QUANTITY
------ ---------- -----------
A1 X1 2
A1 X2 3
A1 X3 4
A1 X4 4
*/
WHERE P_ID='A1' /*
P_ID P_PROPERTY P_QUANTITY
------ ---------- -----------
A1 X1 2
A1 X2 3
A1 X3 4
A1 X4 4
*/
我要得到的结果就是A4这个产品.
我要得到的结果就是A4这个产品.
FROM t
WHERE p_id IN (
SELECT a.p_id
FROM t a INNER JOIN (
SELECT P_PROPERTY,P_QUANTITY,(SELECT count(1)FROM t WHERE P_ID='A1' ) [count] FROM t
WHERE P_ID='A1'
GROUP BY P_PROPERTY,P_QUANTITY)b on a.P_PROPERTY=b.P_PROPERTY AND a.P_QUANTITY=b.P_QUANTITY
GROUP BY a.P_ID,b.[COUNT]
HAVING COUNT(1)=b.[COUNT]
) AND p_id<>'A1'/*
P_ID P_PROPERTY P_QUANTITY
------ ---------- -----------
A4 X1 2
A4 X2 3
A4 X3 4
A4 X4 4
*/
select a.P_ID
from T a
where a.P_ID!='A1'
and exists(select 1
from T b
where b.P_ID='A1'
and b.P_PROPERTY=a.P_PROPERTY
and b.P_QUANTITY=a.P_QUANTITY)
group by a.P_ID
having count(1)=(select count(1)
from T
where P_ID='A1')/*
P_ID
------
A4(1 row(s) affected)
*/
所以 having count(1)=(select count(1)
from T
where P_ID='A1')这一部分可以去掉不呢?
此处是判断每个P_ID的记录数必须与P_ID=A1的记录数(即4)相等.