假设表名 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')
解决方案 »
- 求教子查询的写法?
- report server连接超时.....
- 英文版SQL Server插入中文字符乱码的问题!
- 一个Group by 问题.
- sa用户进入不了sql_server的EnterpriseManager
- 有三个字段,T1, T2, T3,请问怎么返回一个字段,值是这三个字段的最大值?
- 帮我看看这个表如何逆向求出最小的id值 ?
- mssql数据库创建一个数据库后,存储过程中系统默认创建的存储过程看不见。为什么,谢谢
- 我做了个传入表名输出XML的过程
- SQLServer 怎样查询出一个表的最新插入的100条记录
- 怎样用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)
)