求一条简单的sql语句
有学生表：t_s
name_no name
1 x1
2 x2
3 x3
4 x4
5 x5
课程表：t_c
kecheng_no kecheng
1 A
2 B
3 C
4 D
5 E
学生选课表：t_x
id kecheng_no name_no
1 1 1
2 1 2
3 2 1
4 2 2
5 2 3
6 4 1
7 4 2
8 5 1
......
n n n
求选修了所有课程的学生名单
[解决办法]
select * from t_s where name_no in
(select name_no from t_x group by name_no having count(*) = (select count(*) from t_c))
[解决办法]
select * from t_s as A
where not exists(
select 1 from t_s as B
where not exists(select 1 from t_c where kecheng_no=B.kecheng_no and name_no=A.name_no)
)
[解决办法]
--錯了, 改改
create table t_s(name_no int, name varchar(10))
insert t_s select 1, 'x1 '
union all select 2, 'x2 '
union all select 3, 'x3 '
union all select 4, 'x4 '
union all select 5, 'x5 '
create table t_c(kecheng_no int, kecheng varchar(10))
insert t_c select 1, 'A '
union all select 2, 'B '
union all select 3, 'C '
union all select 4, 'D '
union all select 5, 'E '
create table t_x(id int, kecheng_no int, name_no int)
insert t_x select 1, 1, 1
union all select 2, 1, 2
union all select 3, 2, 1
union all select 4, 2, 2
union all select 5, 2, 3
union all select 6, 4, 1
union all select 7, 4, 2
union all select 8, 5, 1
union all select 9, 3, 1 --新增
select * from t_s as A
where not exists(
select 1 from t_c as B
where not exists(select 1 from t_x where kecheng_no=B.kecheng_no and name_no=A.name_no)
)
[解决办法]
--result
name_no name
----------- ----------
1 x1
(1 row(s) affected)
[解决办法]
弱弱的问一下潇洒老乌龟:
having count(*) 这里的count是count哪个表里的纪录呀?
[解决办法]
t_x的
