select jno from spj where sno='s1' group by jno having(count(*)=(select count(distinct pno) from spj where si))
@WaterGG
语法有错~
至少用了S1供应商所提供全部零件的工程号JNO
CREATE TABLE [dbo].[SPJ] ( [SNO] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [PNO] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [JNO] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [QTY] [int] NULL ) ON [PRIMARY] GOinsert SPJ select 'S1','P1','J1',200 union all select 'S1','P1','J3',100 union all select 'S1','P1','J4',700 union all select 'S1','P2','J2',100 union all select 'S2','P3','J1',400 union all select 'S2','P3','J2',200 union all select 'S2','P3','J4',500 union all select 'S2','P3','J5',400 union all select 'S2','P5','J1',400 union all select 'S2','P5','J2',100 union all select 'S3','P1','J1',200 union all select 'S3','P3','J1',200 union all select 'S4','P5','J1',100 union all select 'S4','P6','J3',300 union all select 'S4','P6','J4',200 union all select 'S5','P2','J4',100
select jno from spj where sno='s1' group by jno having(count(*)=(select count(distinct pno) from spj where sno='s1')) 但是结果好像没有符合的
SELECT JNO FROM [SPJ] WHERE [SNO]='S1' GROUP BY JNO HAVING COUNT(DISTINCT [PNO])=(SELECT COUNT(DISTINCT [PNO]) FROM [SPJ] WHERE [SNO]='S1')
drop table [SPJ] CREATE TABLE [dbo].[SPJ] ( [SNO] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [PNO] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [JNO] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [QTY] [int] NULL ) ON [PRIMARY] GOinsert SPJ select 'S1','P1','J1',200 union all select 'S1','P1','J3',100 union all select 'S1','P1','J4',700 union all select 'S1','P2','J2',100 union all select 'S2','P3','J1',400 union all select 'S2','P3','J2',200 union all select 'S2','P3','J4',500 union all select 'S2','P3','J5',400 union all select 'S2','P5','J1',400 union all select 'S2','P5','J2',100 union all select 'S3','P1','J1',200 union all select 'S3','P3','J1',200 union all select 'S4','P5','J1',100 union all select 'S4','P6','J3',300 union all select 'S4','P6','J4',200 union all select 'S5','P2','J4',100SELECT a.JNO FROM [SPJ] a join (SELECT DISTINCT [PNO] FROM [SPJ] WHERE [SNO]='S1') b on a.PNO=b.PNO group by a.JNO having COUNT(DISTINCT a.[PNO])=(SELECT count(DISTINCT [PNO]) FROM [SPJ] WHERE [SNO]='S1') ----- JNO J4
select jno from spj
where sno='s1'
group by jno
having(count(*)=(select count(distinct pno) from spj where si))
语法有错~
[SNO] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[PNO] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[JNO] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[QTY] [int] NULL
) ON [PRIMARY]
GOinsert SPJ select 'S1','P1','J1',200
union all select 'S1','P1','J3',100
union all select 'S1','P1','J4',700
union all select 'S1','P2','J2',100
union all select 'S2','P3','J1',400
union all select 'S2','P3','J2',200
union all select 'S2','P3','J4',500
union all select 'S2','P3','J5',400
union all select 'S2','P5','J1',400
union all select 'S2','P5','J2',100
union all select 'S3','P1','J1',200
union all select 'S3','P3','J1',200
union all select 'S4','P5','J1',100
union all select 'S4','P6','J3',300
union all select 'S4','P6','J4',200
union all select 'S5','P2','J4',100
where sno='s1'
group by jno
having(count(*)=(select count(distinct pno) from spj where sno='s1'))
但是结果好像没有符合的
FROM [SPJ]
WHERE [SNO]='S1'
GROUP BY JNO
HAVING COUNT(DISTINCT [PNO])=(SELECT COUNT(DISTINCT [PNO]) FROM [SPJ] WHERE [SNO]='S1')
CREATE TABLE [dbo].[SPJ] (
[SNO] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[PNO] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[JNO] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[QTY] [int] NULL
) ON [PRIMARY]
GOinsert SPJ select 'S1','P1','J1',200
union all select 'S1','P1','J3',100
union all select 'S1','P1','J4',700
union all select 'S1','P2','J2',100
union all select 'S2','P3','J1',400
union all select 'S2','P3','J2',200
union all select 'S2','P3','J4',500
union all select 'S2','P3','J5',400
union all select 'S2','P5','J1',400
union all select 'S2','P5','J2',100
union all select 'S3','P1','J1',200
union all select 'S3','P3','J1',200
union all select 'S4','P5','J1',100
union all select 'S4','P6','J3',300
union all select 'S4','P6','J4',200
union all select 'S5','P2','J4',100SELECT a.JNO
FROM [SPJ] a join
(SELECT DISTINCT [PNO] FROM [SPJ] WHERE [SNO]='S1') b on a.PNO=b.PNO
group by a.JNO
having COUNT(DISTINCT a.[PNO])=(SELECT count(DISTINCT [PNO]) FROM [SPJ] WHERE [SNO]='S1')
-----
JNO
J4