如何查找与P_ID(产品编号)为A1完全相同的产品呢?就是,P_PROPERTY与P_QUANTITY都要相同.
表结构如下:
create table T ( P_ID VARCHAR(6) NOT NULL ,P_PROPERTY VARCHAR(6) NOT NULL ,
P_QUANTITY INTEGER NOT NULL ,PRIMARY KEY (P_ID,P_PROPERTY) ) ;
INSERT INTO T (P_ID,P_PROPERTY,P_QUANTITY) VALUES ('A1','X1',2) ;
INSERT INTO T (P_ID,P_PROPERTY,P_QUANTITY) VALUES ('A1','X2',3) ;
INSERT INTO T (P_ID,P_PROPERTY,P_QUANTITY) VALUES ('A1','X3',4) ;
INSERT INTO T (P_ID,P_PROPERTY,P_QUANTITY) VALUES ('A1','X4',4) ;
INSERT INTO T (P_ID,P_PROPERTY,P_QUANTITY) VALUES ('A2','X1',1) ;
INSERT INTO T (P_ID,P_PROPERTY,P_QUANTITY) VALUES ('A2','X2',2) ;
INSERT INTO T (P_ID,P_PROPERTY,P_QUANTITY) VALUES ('A2','X3',3) ;
INSERT INTO T (P_ID,P_PROPERTY,P_QUANTITY) VALUES ('A2','X4',4) ;
INSERT INTO T (P_ID,P_PROPERTY,P_QUANTITY) VALUES ('A3','X1',1) ;
INSERT INTO T (P_ID,P_PROPERTY,P_QUANTITY) VALUES ('A3','X2',4) ;
INSERT INTO T (P_ID,P_PROPERTY,P_QUANTITY) VALUES ('A3','X3',5) ;
INSERT INTO T (P_ID,P_PROPERTY,P_QUANTITY) VALUES ('A3','X4',4) ;
INSERT INTO T (P_ID,P_PROPERTY,P_QUANTITY) VALUES ('A4','X1',2) ;
INSERT INTO T (P_ID,P_PROPERTY,P_QUANTITY) VALUES ('A4','X2',3) ;
INSERT INTO T (P_ID,P_PROPERTY,P_QUANTITY) VALUES ('A4','X3',4) ;
INSERT INTO T (P_ID,P_PROPERTY,P_QUANTITY) VALUES ('A4','X4',4) ;
------解决方案--------------------
SELECT *
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)
*/