假设表名 tab1
试试这个语句select jno from
(SELECT pno,JNO from
tab1 t1 where pno in
(select pno from tab1 where sno = 'S1')
)T2 group by jno having count(JNO) = (select count(1) from tab1 where sno = 'S1')
试试这个语句select jno from
(SELECT pno,JNO from
tab1 t1 where pno in
(select pno from tab1 where sno = 'S1')
)T2 group by jno having count(JNO) = (select count(1) from tab1 where sno = 'S1')
解决方案 »
- 奇怪问题,请教大家,有点邪门。
- 怎么把两个没有关系的表拼到一起
- 关于写sql语句
- 高手请帮忙sql语句统计???
- 谁有SSIS的资料啊?
- 连接数据库的问题
- 存储过程能跨数据库创建吗?
- 连接局域网内sqlserver2005出错,provider:命名管道提供程序,error:26 - 无法打开sqlserver的连接
- 能帮我解决一个对我来说很难的SELECT语句问题么?急
- sql server的问题,急死了,分数改天奉上
- 怎样用slq命令显示keys字段中包含这些单词"ok","wait","sended","deleted"中的任意一个或多个单词的记录?
- 加急!!!求各位帮忙看看这段存储过程错在哪儿?
drop table tempif exists(select * from sysobjects where name='up_temp' and type='p')
drop proc up_temp
gocreate proc up_temp @SNO varchar(20)
as
declare @PNO varchar(20)create table #result(JNO varchar(20))declare find_PNO cursor for select distinct PNO from temp where SNO=@SNO
open find_PNO
fetch next from find_PNO into @PNO
insert #result select distinct JNO from temp where PNO=@PNO
while @@fetch_status=0
begin
delete #result where not exists( select 1 from temp where PNO=@PNO and #result.JNO=JNO)
fetch next from find_PNO into @PNO
end
close find_PNO
deallocate find_PNO
select * from #result
gocreate table temp(SNO varchar(20),PNO varchar(20),JNO varchar(20))
go
insert temp
select 'S1',' P1',' J1' union all
select 'S1',' P2',' J3' union all
select 'S2',' P2',' J1' union all
select 'S3',' P3',' J1'select * from temp
/*
SNO PNO JNO
S1 P1 J1
S1 P2 J3
S2 P2 J1
S3 P3 J1
*/exec up_temp 'S1'
/*
JNO
J1
*/
exec up_temp 'S2'
/*
JNO
J1
J3
*/
exec up_temp 'S3'
/*
JNO
J1
*/
drop table tempif exists(select * from sysobjects where name='up_temp' and type='p')
drop proc up_temp
gocreate proc up_temp @SNO varchar(20)
as
declare @PNO varchar(20)create table #result(JNO varchar(20))declare find_PNO cursor for select distinct PNO from temp where SNO=@SNO
open find_PNO
fetch next from find_PNO into @PNO
insert #result select distinct JNO from temp where PNO=@PNO
while @@fetch_status=0
begin
delete #result where not exists( select 1 from temp where PNO=@PNO and #result.JNO=JNO)
fetch next from find_PNO into @PNO
end
close find_PNO
deallocate find_PNO
select * from #result
gocreate table temp(SNO varchar(20),PNO varchar(20),JNO varchar(20))
go
insert temp
select 'S1',' P1',' J1' union all
select 'S1',' P2',' J3' union all
select 'S2',' P2',' J1' union all
select 'S3',' P3',' J1'select * from temp
/*
SNO PNO JNO
S1 P1 J1
S1 P2 J3
S2 P2 J1
S3 P3 J1
*/exec up_temp 'S1'
/*
JNO
J1
*/select jno from (select jno,count(distinct pno) cnt from temp where PNO in(select distinct pno from temp where sno='S1') group by jno)a where cnt=(select count(distinct pno) cnt from temp where sno='S1')
/*
JNO
J1
*/
我假设表名为spjselect distinct jno from spj a
where not exists
(select * from spj b where b.sno='s1' and not exists
(select *
from spj c
where c.jno=a.jno and b.pno=c.pno)
)