成绩sql 查询

发布网友发布时间：2022-04-25 05:41

共6个回答

热心网友时间：2022-04-14 11:15

执行下面的代码：
select max(usrtname)as username,
max('en') as en,
sum(case item when 'en' then score else '' end) as score,
max('ma') as ma,
sum(case item when 'ma' then score else '' end) as score
from 表名
group by username
得到的结果是：
username en score ma seore
_______________________________________
kenny en 80 ma 90
mary en 70

你看满足你的要求不

热心网友时间：2022-04-14 12:33

select t1.username,t1.item,t1.score,t2.item,t2.score
from test02 t1 left join test02 t2
on t1.username=t2.username and t1.item<>t2.item
where t1.item='en';

**********
测试log：
**********

[TEST@ORA1] SQL>select * from test02;

USERN IT SCORE
----- -- ----------
kenny en 80
kenny ma 90
mary en 70

[TEST@ORA1] SQL>select t1.username,t1.item,t1.score,t2.item,t2.score
2 from test02 t1 left join test02 t2
3 on t1.username=t2.username and t1.item<>t2.item
4 where t1.item='en';

USERN IT SCORE IT SCORE
----- -- ---------- -- ----------
kenny en 80 ma 90
mary en 70

---
以上，希望对你有所帮助。

热心网友时间：2022-04-14 14:08

转成那个格式反而不好,还不如转成以下格式:
username en ma
---------- ----------- -----------
kenny 80 90
mary 70 0

语句可参考如下:
select username,
max(case item when 'en' then score else 0 end) en,
max(case item when 'ma' then score else 0 end) ma
from t
group by username

热心网友时间：2022-04-14 15:59

select t1.name,
'en',case Max(case when item='en' then score else 0 end) when 0 then null else Max(case when item='en' then score else 0 end) end,
'ma',case Max(case when item='ma' then score else 0 end) when 0 then null else Max(case when item='ma' then score else 0 end) end
from
table t1
group by t1.name

热心网友时间：2022-04-14 18:07

Select AA.Username,BB.Item,bb.scope,cc.item,cc.scope
From
(Select Username from T1 Group by Username) AA,
(Select Scope from T1 where item='en') BB,
(Select Scope from t1 where item='ma') CC
where aa.Username*=bb.username and aa.Username*=cc.username

热心网友时间：2022-04-14 20:32

有两种情况:固定列和到不固定列