select id,class,avg(grade) from tb group by id,class
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
select id,class,avg(grade) as grade,count(id) as num from tb group by id ,class
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
先是select avg(grade) from tb group by id, 然后在这个查询结果当中查询不同平均分的人数
select id avg(grade) as avg_grade from table_name group by idseletc count(distinct avg_grade) as diff_avg_grade from (select id avg(grade) as avg_grade from table_name group by id) a
select id ,avg(grade) as avg_grade from table_name group by idseletc 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
seletc count(distinct avg_grade) as diff_avg_grade from (select id avg(grade) as avg_grade from table_name group by id) t
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
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
(
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
id,class,grade,num
a,uu,33,3
b,uu,64,1
c,uu,64,1(3 行受影响)
然后在这个查询结果当中查询不同平均分的人数
select id avg(grade) as avg_grade from table_name group by idseletc count(distinct avg_grade) as diff_avg_grade
from (select id avg(grade) as avg_grade from table_name group by id) a
select id ,avg(grade) as avg_grade from table_name group by idseletc count(distinct avg_grade) as diff_avg_grade
from (select avg(grade) as avg_grade from table_name group by id) a
from (select id avg(grade) as avg_grade from table_name group by id)
大概就是这个语句的的意思 但是为什么报错Every derived table must have its own alias
from (select id avg(grade) as avg_grade from table_name group by id) t
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