create table #tt (name varchar(10),kechen varchar(10),fenshu int)insert into #tt values('张三','语文',82) insert into #tt values('张三','英语',81) insert into #tt values('张三','数学',87) insert into #tt values('李四','英语',65) insert into #tt values('李四','数学',90) select distinct name from #tt a where (select count(1) from #tt where name=a.name)=(select count(1) from #tt where name=a.name and fenshu>=80) name ---------- 张三(1 行受影响)
select * from students where fenshu > 80 and name not in (select name from students where fenshu < 80);
create table students ( name varchar(10), kechen varchar(10), fenshu int )insert students select '张三','语文',82 insert students select '李四','英语',65 insert students select '张三','数学',87 insert students select '李四','数学',90 insert students select '张三','英语',81select * from students A where name not in (select name from students where fenshu<80)
count(1)是什么意思? 那如果学生有一百多个那不是要写一条inset into
--or select * from students A where not exists (select 1 from students where A.name=name and fenshu<80)
A.name=name有什么区别呀,有都是同一张表吗
select * from #students A where name not in (select name from #students where fenshu<80) select name,sum(case when kechen=N'语文' then fenshu else null end) '语文', sum(case when kechen=N'数学' then fenshu else null end) '数学', sum(case when kechen=N'英语' then fenshu else null end) '英语' from #students group by name
谢谢 最后问下 select * from #students A where name not in (select name from #students where fenshu<80) 什么意思呀,不懂#students和A不都是同一张表吗
select * from #students where name not in (select name from #students where fenshu <80) 这样就可以,A是#students的别名
select * from students A where not exists (select 1 from students where A.name=name and fenshu<80)select * from students A where not exists (select 1 from students B where A.name=B.name and B.fenshu<80)
LZ不懂就用 select * from #students where name not in(select name from #students where fenshu<80)
超簡單語句 select name from students where fenshu>80 group by name having count(kechen)=3 說明,如只有三門課程,上面就是3,如有四門課,就寫成aving count(kechen)=4,依此類推
SELECT * FROM aa a WHERE NOT EXISTS (SELECT 1 FROM aa WHERE a.name=NAME AND COUNT<80)
create table #tt (name varchar(10),kechen varchar(10),fenshu int)insert into #tt values('张三','语文',82)
insert into #tt values('张三','英语',81)
insert into #tt values('张三','数学',87)
insert into #tt values('李四','英语',65)
insert into #tt values('李四','数学',90)
select distinct name from #tt a where (select count(1) from #tt where name=a.name)=(select count(1) from #tt where name=a.name and fenshu>=80)
name
----------
张三(1 行受影响)
select *
from students
where fenshu > 80
and name not in (select name from students where fenshu < 80);
create table students
(
name varchar(10),
kechen varchar(10),
fenshu int
)insert students select '张三','语文',82
insert students select '李四','英语',65
insert students select '张三','数学',87
insert students select '李四','数学',90
insert students select '张三','英语',81select *
from students A
where name not in (select name from students where fenshu<80)
那如果学生有一百多个那不是要写一条inset into
--or
select *
from students A
where not exists (select 1 from students where A.name=name and fenshu<80)
select *
from #students A
where name not in (select name from #students where fenshu<80)
select name,sum(case when kechen=N'语文' then fenshu else null end) '语文',
sum(case when kechen=N'数学' then fenshu else null end) '数学',
sum(case when kechen=N'英语' then fenshu else null end) '英语'
from #students
group by name
最后问下
select *
from #students A
where name not in (select name from #students where fenshu<80)
什么意思呀,不懂#students和A不都是同一张表吗
from #students
where name not in (select name from #students where fenshu <80) 这样就可以,A是#students的别名
给这几个别分别设别名a,b,就可以用a.ID,b.ID,
而不用写表名,就是用a,b代替表名
from students A
where not exists (select 1 from students where A.name=name and fenshu<80)select *
from students A
where not exists (select 1 from students B
where A.name=B.name and B.fenshu<80)
select *
from #students
where name not in(select name from #students where fenshu<80)
超簡單語句
select name from students
where fenshu>80
group by name
having count(kechen)=3
說明,如只有三門課程,上面就是3,如有四門課,就寫成aving count(kechen)=4,依此類推
FROM aa a
WHERE NOT EXISTS (SELECT 1 FROM aa WHERE a.name=NAME AND COUNT<80)