22,301
社区成员




with kc(kid,kname) as
(
select 1,'语文' union all
select 2,'数学' union all
select 3,'英语'
),
stu(sid,sname) as
(
select 1,'张三' union all
select 2,'李四' union all
select 3,'王五'
),
cj(kid,sid,cj) as
(
SELECT 1,1,90 UNION ALL
SELECT 2,1,80
),
tb as
(
select b.kname,c.sname,a.cj from kc b
left join cj a on a.kid=b.kid
left join stu c on c.sid=a.sid
)
select kname,
sum(case when sname='张三' then cj else 0 end)'张三',
sum(case when sname='李四' then cj else 0 end)'李四',
sum(case when sname='王五' then cj else 0 end)'王五'
from tb group by kname