求一SQL语句,请高手帮忙???userName type scoreAAA 语文 77
AAA 数学 88
BBB 语文 89
BBB 数学 99
CCC 语文 88
CCC 数学 98
CCC 英语 101要求:用一条SQL语句求出每门功课的分数都高于80分的人,并且计算出各人的平均分???
AAA 数学 88
BBB 语文 89
BBB 数学 99
CCC 语文 88
CCC 数学 98
CCC 英语 101要求:用一条SQL语句求出每门功课的分数都高于80分的人,并且计算出各人的平均分???
(select userName from tb where score <= 80)
group by userName
from tb t
where not exists(select 1 from tb where username=t.username and score<=80)
group by username
insert into tb values('AAA', '语文', 77)
insert into tb values('AAA', '数学', 88)
insert into tb values('BBB', '语文', 89)
insert into tb values('BBB', '数学', 99)
insert into tb values('CCC', '语文', 88)
insert into tb values('CCC', '数学', 98)
insert into tb values('CCC', '英语', 101)
goselect userName , cast(avg(score*1.0) as decimal(18,2)) avg_score from tb where userName not in
(select distinct userName from tb where score <= 80)
group by userNamedrop table tb/*
userName avg_score
---------- --------------------
BBB 94.00
CCC 95.67(所影响的行数为 2 行)
*/
userName varchar(10), type varchar(10),score int)insert tb select 'AAA','语文', 77
insert tb select 'AAA','数学', 88
insert tb select 'BBB','语文', 89
insert tb select 'BBB','数学', 99
insert tb select 'CCC','语文', 88
insert tb select 'CCC','数学', 98
insert tb select 'CCC','英语', 101
select userName,avg(score) as avg_score from tb a where not exists(select 1 from tb b where a.username=b.username and b.score<=80)
group by userNameuserName avg_score
---------- -----------
BBB 94
CCC 95(2 行受影响)
username,cast(avg(score*1.0) as dec(18,2)) as avg_score
from tb t
where
not exists(select 1 from tb where username=t.username and score<=80)
group by
username
2.
select userName , cast(avg(score*1.0) as decimal(18,2)) avg_score
from tb --where userName not in (select distinct userName from tb where score <= 80)
group by userName
1.
select userName --, cast(avg(score*1.0) as decimal(18,2)) avg_score
from tb where userName not in (select distinct userName from tb where score <= 80)
group by userName