22,210
社区成员
发帖
与我相关
我的任务
分享
select t1.name,count(t1.subject) from tb t1
where exists ( select 1 from tb t2 where t2.name=t1.name
and t2.subject in ('语文','数学','外语')
having count(subject)=3 )
group by t1.name
having count(t1.subject)=3;
select distinct name from tb t1
where exists ( select 1 from tb t2 where t2.name=t1.name
and t2.subject in ('语文','数学','外语')
having count(subject)=3 );
--1 如何显示同时选了语文 数学 外语的学生
select * from tb where name in
(
select name from
(
select distinct name from tb where subject = '语文'
union all
select distinct name from tb where subject = '数学'
union all
select distinct name from tb where subject = '外语'
) t
group by name having count(1) >= 3
)
--2 如何显示同时只选了语文 数学 外语的学生
select * from tb where name in
(
select name from
(
select distinct name from tb where subject = '语文'
union all
select distinct name from tb where subject = '数学'
union all
select distinct name from tb where subject = '外语'
) t
group by name having count(1) = 3
)
use test
go
drop table #score
go
create table #score
(
Name varchar(100),
subject varchar(100)
)
go
insert into #score values('张三', '语文')
insert into #score values('张三', '数学')
insert into #score values('张三', '外语')
insert into #score values('张三', '物理')
insert into #score values('李四', '数学')
insert into #score values('李四', '语文')
insert into #score values('李四', '外语')
insert into #score values('王五', '物理')
insert into #score values('赵六', '数学')
insert into #score values('陈七', '外语')
insert into #score values('陈七', '语文')
insert into #score values('陈七', '数学')
insert into #score values('钱八', '地理')
insert into #score values('钱八', '语文')
go
select name from #score
where subject in ('语文', '数学', '外语')
group by name
having count(subject) = 3
go ---- 显示同时选 语文 数学 外语 三门课的人
select distinct name from #score
where name not in (
select name from #score
where subject not in ('语文', '数学', '外语')
group by name)
--- 显示只选择 语文 数学 外语 三种课的学生