一. select 学生ID from 表A group by 学生ID having count(学生ID)=(select count(*) from 表B)二. select b.学生ID from (select max(a.数量) as '数量' from (select 学生ID,count(学生ID) as '数量' from 表A group by 学生ID) a) c, (select 学生ID,count(学生ID) as '数量' from 表A group by 学生ID) b where c.数量=b.数量
--ta:学生表(stuid,sname) --tb:选课表(stuid,cno,grade) --tc:课程表(cno,cname --1选了所有课程的学生ID及姓名 select stuid,sname from ta a where not exists (select 1 from tc c where not exists (select * from tb b where a.stuid=b.stuid and c.cno=b.cno ) ) --or 或只取学生ID,通过两表 select distinct stuid from tb a where not exists ( select 1 from tc b where not exists ( select * from tb c where c.stuid=a.stuid and c.cno=b.cno ) ) --2表A中,选的课程最多的学生ID及姓名 select stuid from tb a where not exists (select 1 from tb group by stuid having count(1)>(select count(1) from tb where stuid=a.stuid) )
--ta:学生表(stuid,sname) --tb:选课表(stuid,cno,grade) --tc:课程表(cno,cname --1选了所有课程的学生ID及姓名 select stuid,sname from ta a where not exists (select 1 from tc c where not exists (select * from tb b where a.stuid=b.stuid and c.cno=b.cno ) ) --or 或只取学生ID,通过两表select distinct stuid from tb a where not exists ( select 1 from tc b where not exists ( select * from tb c where c.stuid=a.stuid and c.cno=b.cno ) ) --2表A中,选的课程最多的学生ID及姓名 select * from ta a where not exists ( select 1 from tb group by stuid having count(1)>(select count(1) from tb where stuid=a.stuid) )--or 或只取学生ID,通过两表 select distinct stuid from tb a where not exists (select 1 from tb group by stuid having count(1)>(select count(1) from tb where stuid=a.stuid) )
create table A ( 学生Id int, 课程Id int )insert A select 1,1 insert A select 1,2 insert A select 1,3 insert A select 1,4 insert A select 2,1 insert A select 2,2 insert A select 2,3 insert A select 3,1 insert A select 3,2create table B ( 课程Id int ) insert B select 1 insert B select 2 insert B select 3 insert B select 41. select A.学生Id from (select 学生Id,count(*) as num from A group by 学生Id) A, (select count(*) as num from B)B where A.num=B.num2. select top 1 WiTh Ties T.学生ID from A T order by (select count(*) from A where T.学生ID=学生ID) DESC or select distinct * from ( select top 1 WiTh Ties T.学生ID from A T order by (select count(*) from A where T.学生ID=学生ID) DESC) T
2. select top 1 WiTh Ties T.学生ID from A T order by (select count(*) from A where T.学生ID=学生ID) DESC --or select distinct * from ( select top 1 WiTh Ties T.学生ID from A T order by (select count(*) from A where T.学生ID=学生ID) DESC) T
select 学生ID from 表A group by 学生ID having count(学生ID)=(select count(*) from 表B)二.
select b.学生ID from
(select max(a.数量) as '数量' from (select 学生ID,count(学生ID) as '数量' from 表A group by 学生ID) a) c,
(select 学生ID,count(学生ID) as '数量' from 表A group by 学生ID) b
where c.数量=b.数量
--tb:选课表(stuid,cno,grade)
--tc:课程表(cno,cname
--1选了所有课程的学生ID及姓名
select stuid,sname from ta a
where not exists
(select 1 from tc c where not exists
(select * from tb b where a.stuid=b.stuid and c.cno=b.cno )
)
--or 或只取学生ID,通过两表
select distinct stuid
from tb a
where not exists
(
select 1 from tc b where not exists
(
select * from tb c where c.stuid=a.stuid and c.cno=b.cno
)
)
--2表A中,选的课程最多的学生ID及姓名
select stuid
from tb a
where
not exists
(select 1 from tb group by stuid
having count(1)>(select count(1) from tb where stuid=a.stuid) )
--tb:选课表(stuid,cno,grade)
--tc:课程表(cno,cname
--1选了所有课程的学生ID及姓名
select stuid,sname from ta a
where not exists
(select 1 from tc c where not exists
(select * from tb b where a.stuid=b.stuid and c.cno=b.cno )
)
--or 或只取学生ID,通过两表select distinct stuid
from tb a
where not exists
(
select 1 from tc b where not exists
(
select * from tb c where c.stuid=a.stuid and c.cno=b.cno
)
)
--2表A中,选的课程最多的学生ID及姓名
select * from ta a
where not exists
(
select 1 from tb group by stuid
having count(1)>(select count(1) from tb where stuid=a.stuid)
)--or 或只取学生ID,通过两表
select distinct stuid
from tb a
where
not exists
(select 1 from tb group by stuid
having count(1)>(select count(1) from tb where stuid=a.stuid) )
(
学生Id int,
课程Id int
)insert A select 1,1
insert A select 1,2
insert A select 1,3
insert A select 1,4
insert A select 2,1
insert A select 2,2
insert A select 2,3
insert A select 3,1
insert A select 3,2create table B
(
课程Id int
)
insert B select 1
insert B select 2
insert B select 3
insert B select 41.
select A.学生Id
from
(select 学生Id,count(*) as num from A group by 学生Id) A,
(select count(*) as num from B)B
where A.num=B.num2.
select top 1 WiTh Ties T.学生ID from A T order by (select count(*) from A where T.学生ID=学生ID) DESC
or
select distinct * from (
select top 1 WiTh Ties T.学生ID from A T order by (select count(*) from A where T.学生ID=学生ID) DESC) T
select top 1 WiTh Ties T.学生ID from A T order by (select count(*) from A where T.学生ID=学生ID) DESC
--or
select distinct * from (
select top 1 WiTh Ties T.学生ID from A T order by (select count(*) from A where T.学生ID=学生ID) DESC) T