首先是三张表， cno对应的是课程，在这里我就粘贴了。
主表
人名表
按照常规查询
select s.sname, c.cname,s2.scgrade
from s s inner join sc s2 on s2.sno = s.sno inner join c c on c.cno = s2.cno
那么结果是这样的
但是这是横表 不是我想看到的结果。
我们要看到这样的结果：
那么怎么办呢？
第一种写法：
复制代码 代码如下:
select w.sname,
sum(case when w.cno= 1 then w.scgrade else 0 end) as ‘语文’,
sum(case when w.cno =2 then w.scgrade else 0 end) as ‘数学’,
sum(case when w.cno= 3 then w.scgrade else 0 end) as ‘英语’
from
(select s.sno,s.sname, s2.cno, s2.scgrade from s s inner join sc s2 on s2.sno = s.sno where s.sno in (select c.sno from sc c group by c.sno ))
as w group by w.sname
第二种写法：
复制代码 代码如下:
select s.sname,
sum(case when s2.cno= 1 then s2.scgrade else 0 end) as ‘语文’,
sum(case when s2.cno =2 then s2.scgrade else 0 end) as ‘数学’,
sum(case when s2.cno= 3 then s2.scgrade else 0 end) as ‘英语’
from
s s inner join sc s2 on s2.sno = s.sno
inner join c c on c.cno = s2.cno
group by s.sno,
s.sname
这是我工作遇到过得情况，总结下来。如果有遇到这种情况的话可以参考下。