--> 测试数据：[S1]
if object_id('[S1]') is not null 
drop table [S1]
create table [S1](
[SNO] varchar(2),
[SNAME] varchar(10),
[CITY] varchar(4)
)
insert [S1]
select 'S1','大连机床厂','大连' union all
select 'S2','北京机床厂','北京'
--> 测试数据：[P2]
if object_id('[P2]') is not null 
drop table [P2]
create table [P2](
[PNO] varchar(2),
[PNAME] varchar(4),
[COLOR] varchar(4),
[WEIGHT] int
)
insert [P2]
select 'P1','螺母','红色',12 union all
select 'P2','螺栓','蓝色',17
--> 测试数据：[J3]
if object_id('[J3]') is not null 
drop table [J3]
create table [J3](
[JNO] varchar(2),
[JNAME] varchar(10),
[CITY] varchar(4)
)
insert [J3]
select 'J1','不夜城','大连' union all
select 'J2','长春火车站','长春'
--> 测试数据：[SPJ]
if object_id('[SPJ]') is not null 
drop table [SPJ]
create table [SPJ](
[SNO] varchar(2),
[PNO] varchar(2),
[JNO] varchar(2),
[QTY] int
)
insert [SPJ]
select 'S1','P1','J1',200 union all
select 'S1','P1','J3',100

select JNO,JNAME from( 
select d.*,c.PNO,c.PNAME,c.COLOR from [SPJ] a 
inner join [P2] c on a.PNO=c.PNO
inner join [J3] d on a.JNO=d.JNO
where c.COLOR='红色')t group by JNO,JNAME
having COUNT(1)=(select COUNT(1) from [P2] where COLOR='红色')
/*
JNO    JNAME
J1    不夜城
*/