SQL怎么合并同表查询

来源：百度知道 编辑：UC知道 时间：2024/06/24 05:48:56
score表
id name subject results
1 jack english 77.8
2 nice english 67.6
3 mani english 79.0
4 koko chinese 68.9
1 jack chinese 91.2
5 jon chinese 57.3
2 nice chinese 80.5
5 jon english 73.2
4 koko english 60.1
3 mani english 55.2

查询 english>70 且 chinese>70 的同学
要求一次查询显示字段为
id name englishresults chineseresults

求查询语句...各位大大帮帮忙哦

select id,name,
(select results as englishresults from score where subject='english' and results>70),
(select results as chineseresults from score where subject='chinese' and results>70)
from score;
加一个子查询就行。
看不明白问我。

select id,name
,max(case subject when 'english' then results else 0 end) englishresults
,max(case subject when 'chinese' then results else 0 end) chineseresults
from score
group by id,name
having max(case subject when 'english' then results else 0 end)>70
and max(case subject when 'chinese' then results else 0 end)>70

select id,min(name)名字,
(select results from score where subject='english' and id = a.id)英语,
(select results from score where subject='chinese' and id = a.id)语文
from score a group by id having min(results) > 70

select id,name,sum(decode(subject,'english',results,0)) as englishresults