group by 是分组 统计,比如统计各个班级的,人数 select 班级,county(*)as 人数 from tb group by 班级 exists 是当子句没有任何值时,返回真,not再取反
exists 比如 获取相同id中,时间最早的一个select * from tb t where not exists(select * from tb where id=t.id and sj<t.sj)
SELECT A,MAX(B)B FROM TB GROUP BY A SELECT A,B FROM TB T WHERE NOT EXISTS(SELECT 1 FROM TB WHERE A=T.A AND B>T.B) SELECT A,B FROM TB T WHERE B=(SELECT MAX(B) FROM TB WHERE A=T.A)
HOHO,是各有所用,不相交的地方也有呀,主要是看应用
呵呵刚看了一个帖子转一下数据啊hohoif object_id('stuscore') is not null drop table stuscore go create table stuscore(name varchar(50) , subject varchar(50) , score int) go insert into stuscore(name, subject, score) select '张三','数学',89 UNION ALL SELECT '张三','语文',80 UNION ALL SELECT '张三','英语',90 UNION ALL SELECT '李四','数学',90 UNION ALL SELECT '李四','语文',70 UNION ALL SELECT '李四','英语',80 UNION ALL SELECT '李四','物理',80;这个表结果需要实现的:所有课程成绩都大于80分的学生姓名 group by 的实现方法:select name from stuscore where score>=80 group by name having count(name)=(select count(distinct subject) from stuscore)not exists的用法:select distinct name from stuscore a where not exists(select 1 from stuscore where name = a.name and score <80)77说的很对各有各个的方法但是我想问下有没有那种默认的使用方法 比如说要实现这种功能可能用not exists比较简单点 等等。 还有上面用not exists实现的时候没有判断出张三没有考物理
select 班级,county(*)as 人数 from tb group by 班级
exists 是当子句没有任何值时,返回真,not再取反
exists 比如 获取相同id中,时间最早的一个select * from tb t
where not exists(select * from tb where id=t.id and sj<t.sj)
SELECT A,B FROM TB T WHERE NOT EXISTS(SELECT 1 FROM TB WHERE A=T.A AND B>T.B)
SELECT A,B FROM TB T WHERE B=(SELECT MAX(B) FROM TB WHERE A=T.A)
drop table stuscore
go
create table stuscore(name varchar(50) , subject varchar(50) , score int)
go
insert into stuscore(name, subject, score)
select
'张三','数学',89 UNION ALL SELECT
'张三','语文',80 UNION ALL SELECT
'张三','英语',90 UNION ALL SELECT
'李四','数学',90 UNION ALL SELECT
'李四','语文',70 UNION ALL SELECT
'李四','英语',80 UNION ALL SELECT
'李四','物理',80;这个表结果需要实现的:所有课程成绩都大于80分的学生姓名
group by 的实现方法:select name
from stuscore
where score>=80
group by name
having count(name)=(select count(distinct subject) from stuscore)not exists的用法:select distinct name from stuscore a
where not exists(select 1 from stuscore where name = a.name and score <80)77说的很对各有各个的方法但是我想问下有没有那种默认的使用方法
比如说要实现这种功能可能用not exists比较简单点
等等。
还有上面用not exists实现的时候没有判断出张三没有考物理