34,576
社区成员
发帖
与我相关
我的任务
分享一个SQL查询
use tempdb
if object_id('t') is not null drop table t
go
create table t([Sno] varchar(50),[Cno] INT,[Grade] INT)
insert into t
select '95001',1,92 union all
select '95001',2,85 union all
select '95001',3,88 union all
select '95001',4,23 union all
select '95001',5,34 union all
select '95001',6,56 union all
select '95001',7,86 union all
select '95001',8,88 union all
select '95002',2,90 union all
select '95002',3,80 union all
select '95003',1,50 union all
select '95003',3,98 union all
select '95005',3,NULL
go
select * from t查询 至少选修了[学生95002选修全部课程]的学生号码
...全文
请发表友善的回复…
发表回复
smntbk 2009-12-16
- 打赏
- 举报
非常感谢!
开阔了思路,谢谢!
开阔了思路,谢谢!
--小F-- 2009-12-16
- 打赏
- 举报
select
distinct Sno
from
tb t
where
not exists
(selec
1
from
tb a
where
Sno='95002'
and
not exists (select 1 from tb where Sno=t.Sno and Cno=a.Cno)
lxyhjh051 2009-12-16
- 打赏
- 举报
select Sno from sc where (select Cno from sc where exist (select * from sc where Sno='95002')) and Sno<>'95002'
group by Sno
having count(*) >= (select count(*) from sc where Sno='95002');
select Sno from sc where exist (select * from sc where Sno='95002') and Sno<>'95002'
group by Sno
having count(*) >= (select count(*) from sc where Sno='95002');
以上两句怎么都出错了。要是用exists应该怎么写啊？！先谢过了
group by Sno
having count(*) >= (select count(*) from sc where Sno='95002');
select Sno from sc where exist (select * from sc where Sno='95002') and Sno<>'95002'
group by Sno
having count(*) >= (select count(*) from sc where Sno='95002');
以上两句怎么都出错了。要是用exists应该怎么写啊？！先谢过了
Rotel-刘志东 2009-12-16
- 打赏
- 举报
use tempdb
if object_id('t') is not null drop table t
go
create table t([Sno] varchar(50),[Cno] INT,[Grade] INT)
insert into t
select '95001',1,92 union all
select '95001',2,85 union all
select '95001',3,88 union all
select '95001',4,23 union all
select '95001',5,34 union all
select '95001',6,56 union all
select '95001',7,86 union all
select '95001',8,88 union all
select '95002',2,90 union all
select '95002',3,80 union all
select '95003',1,50 union all
select '95003',3,98 union all
select '95005',3,NULL
go
select sno from t
where sno <> '95002' and cno in (select cno from t where sno = '95002')
group by sno having count(1) = (select count(1) from t where sno = '95002') --小F-- 2009-12-16
- 打赏
- 举报
---嵌套太多 效率也不高
select
distinct Sno
from
tb t
where
not exists
(selec
1
from
tb a
where
Sno='95002'
and
not exists (select 1 from t where Sno=t.Sno and Cno=a.Cno)
ACMAIN_CHM 2009-12-16
- 打赏
- 举报
1> select * from t
2> go
Sno |Cno |Grade
----------------------------------------|-----------|-----------
95001 | 1| 92
95001 | 2| 85
95001 | 3| 88
95001 | 4| 23
95001 | 5| 34
95001 | 6| 56
95001 | 7| 86
95001 | 8| 88
95002 | 2| 90
95002 | 3| 80
95003 | 1| 50
95003 | 3| 98
95005 | 3| NULL
(13 rows affected)
1> select distinct Sno
2> from t c
3> where not exists (
4> select 1 from t a
5> where Sno='95002'
6> and not exists (select 1 from t where Sno=c.Sno and Cno=a.Cno)
7> )
8> go
Sno
----------------------------------------
95001
95002
(2 rows affected)
1>
sinpoal 2009-12-16
- 打赏
- 举报
select sno from t where
cno =any (select cno from t where sno = '95002') and sno <> '95002'
group by sno having count(*) >= (select count(*) from t where sno = '95002')
dawugui 2009-12-16
- 打赏
- 举报
create table t([Sno] varchar(50),[Cno] INT,[Grade] INT)
insert into t
select '95001',1,92 union all
select '95001',2,85 union all
select '95001',3,88 union all
select '95001',4,23 union all
select '95001',5,34 union all
select '95001',6,56 union all
select '95001',7,86 union all
select '95001',8,88 union all
select '95002',2,90 union all
select '95002',3,80 union all
select '95003',1,50 union all
select '95003',3,98 union all
select '95005',3,NULL
go
select sno from t
where sno <> '95002' and cno in (select cno from t where sno = '95002')
group by sno having count(1) = (select count(1) from t where sno = '95002')
drop table t
/*
sno
--------------------------------------------------
95001
（所影响的行数为 1 行）
*/
nianran520 2009-12-16
- 打赏
- 举报
--貌似没啥高效的写法
select sno from sc
where cno in (select cno from sc where sno = '95002')
and sno <> '95002'
group by sno
having count(*) = (select count(*) from sc where sno = '95002')
破折号 2009-12-16
- 打赏
- 举报
declare @t table([Sno] varchar(50),[Cno] INT,[Grade] INT)
insert into @t
select '95001',1,92 union all
select '95001',2,85 union all
select '95001',3,88 union all
select '95001',4,23 union all
select '95001',5,34 union all
select '95001',6,56 union all
select '95001',7,86 union all
select '95001',8,88 union all
select '95002',2,90 union all
select '95002',3,80 union all
select '95003',1,50 union all
select '95003',3,98 union all
select '95005',3,NULL
select * from @t
select distinct c.Sno from @t c where not exists(
select * from @t b where Sno='95002' and not exists(
select * from @t a where a.Cno=b.Cno and a.Sno=c.Sno))
破折号 2009-12-16
- 打赏
- 举报
select distinct c.Sno from @t c where not exists(
select * from @t b where Sno='95002' and not exists(
select * from @t a where a.Cno=b.Cno and a.Sno=c.Sno))
guguda2008 2009-12-16
- 打赏
- 举报
我这个好像也不怎么快，呵呵
use tempdb
if object_id('t') is not null drop table t
go
create table t([Sno] varchar(50),[Cno] INT,[Grade] INT)
insert into t
select '95001',1,92 union all
select '95001',2,85 union all
select '95001',3,88 union all
select '95001',4,23 union all
select '95001',5,34 union all
select '95001',6,56 union all
select '95001',7,86 union all
select '95001',8,88 union all
select '95002',2,90 union all
select '95002',3,80 union all
select '95003',1,50 union all
select '95003',3,98 union all
select '95005',3,NULL
go
select DISTINCT SNO
from t T1
WHERE NOT EXISTS(SELECT 1 FROM T T2 WHERE T2.SNO='95002'
AND T2.CNO NOT IN (SELECT CNO FROM T T3 WHERE T3.SNO=T1.SNO)
) AND T1.SNO<>'95002'
--95001smntbk 2009-12-16
- 打赏
- 举报
select sno from sc
where cno in (select cno from sc where sno = '95002')
and sno <> '95002'
group by sno
having count(*) = (select count(*) from sc where sno = '95002')这是我的查询,学习高手高效的写法
