sql的问题

[奇乐二二二]

id class grade
a uu 12
a uu 24
b uu 64
a uu 64
c uu 64

先是统计每个人的平均分，然后统计不同平均分的对应人数，这个该怎么写？

[chuanzhang5687]

select id,class,avg(grade) from tb group by id,class

[AcHerat]

select b.acnt,count(distinct a.id) scnt
from tb a join (select id,avg(grade) acnt from tb group by id) b on a.id = b.id
group by b.acnt

[chuanzhang5687]

select id,class,avg(grade) as grade,count(id) as num from tb group by id ,class

[chuanzhang5687]

create table tb
(
	id		varchar(10),
	class	varchar(10),
	grade	int
)
insert into tb values('a','uu',12)
insert into tb values('a','uu',24)
insert into tb values('b','uu',64)
insert into tb values('a','uu',64)
insert into tb values('c','uu',64)

select id,class,avg(grade) as grade,count(id) as num from tb group by id ,class

[chuanzhang5687]

/*
id,class,grade,num
a,uu,33,3
b,uu,64,1
c,uu,64,1

(3 行受影响)

[奇乐二二二]

可能是我前面说的有点乱，我的意思是先是统计每个人的平均分，然后统计不同平均分的人的个数

[AcHerat]

试试2楼，我没环境测试！

[奇乐二二二]

先是select avg(grade) from tb group by id，
然后在这个查询结果当中查询不同平均分的人数

[oO寒枫Oo]

select id avg(grade) as avg_grade from table_name group by id

seletc count(distinct avg_grade) as diff_avg_grade
from (select id avg(grade) as avg_grade from table_name group by id) a

[oO寒枫Oo]

select id ,avg(grade) as avg_grade from table_name group by id

seletc count(distinct avg_grade) as diff_avg_grade
from (select avg(grade) as avg_grade from table_name group by id) a

[奇乐二二二]

seletc count(distinct avg_grade) as diff_avg_grade
from (select id avg(grade) as avg_grade from table_name group by id)
大概就是这个语句的的意思 但是为什么报错Every derived table must have its own alias

[AcHerat]

seletc count(distinct avg_grade) as diff_avg_grade
from (select id avg(grade) as avg_grade from table_name group by id) t

[AcHerat]

seletc avg_grade,count(distinct id) as diff_avg_grade
from (select id,avg(grade) as avg_grade from table_name group by id) t
group by avg_grade

[oO寒枫Oo]

[Quote=引用 11 楼 chhxxc 的回复:]

seletc count(distinct avg_grade) as diff_avg_grade
from (select id avg(grade) as avg_grade from table_name group by id)
大概就是这个语句的的意思 但是为什么报错Every derived table must have its own alias
[/Quote]
用十楼的 9楼少了个逗号

[L1101561491]

乱逛来的