行转列案例

mysql@行转列
题二:有以下三张表

商品表：tp

pcode	pname
1	cpu
2	内存
3	硬盘

城市表：tc

cno	cname
101	广州
102	深圳
103	上海

销售表：ts

pcode	cno	count
1	101	10000
1	102	500
1	103	20000
2	101	40000
2	103	30000
3	102	90000

问题：需要得到以下结构的统计结果，写出SQL语句：

	广州	深圳	上海	总计
CPU	10000	5000	20000	35000
内存	40000	0	30000	70000
硬盘	0	90000	0	90000
总计	50000	95000	50000	195000

---

原文：https://blog.youkuaiyun.com/yangyi22/article/details/7493041

尝试解答思路如下：
（如有不足，请指正）
1、一般情况下，会这样做

select pname,cname,count from ts 
left join tp
on ts.pcode=tp.pcode
left join tc
on ts.cno=tc.cno;

但结果并不是该题目所要的
2、尝试静态行转列

select pname,cname,
max(case tc.cname when '广州' then ts.count else 0 end) '广州'  from ts 
left join tp
on ts.pcode=tp.pcode
left join tc
on ts.cno=tc.cno;

select pname,
max(case tc.cname when '广州' then ifnull(ts.count,0) else 0 end) '广州' ,
max(case tc.cname when '深圳' then ifnull(ts.count,0) else 0 end) '深圳' ,
max(case tc.cname when '上海' then ifnull(ts.count,0) else 0 end) '上海'  from ts 
left join tp
on ts.pcode=tp.pcode
left join tc
on ts.cno=tc.cno
group by pname;

SELECT pname AS "配件名",
    SUM(IF(tc.cname="广州",ts.count,0)) AS "广州",
        SUM(IF(tc.cname="深圳",ts.count,0)) AS "深圳",
        SUM(IF(tc.cname="上海",ts.count,0)) AS "上海",
        SUM(ts.count) AS "总计"
FROM ts left join tp on ts.pcode=tp.pcode
        left join tc on ts.cno=tc.cno
GROUP BY pname
UNION ALL

    SELECT pname AS "配件名",
        SUM(IF(tc.cname="广州",ts.count,0)) AS "广州",
        SUM(IF(tc.cname="深圳",ts.count,0)) AS "深圳",
        SUM(IF(tc.cname="上海",ts.count,0)) AS "上海",
        SUM(ts.count) AS "总计"
    FROM ts left join tp on ts.pcode=tp.pcode
        left join tc on ts.cno=tc.cno
    GROUP BY pname;

目前我还只会写出行总计，希望大神可以给出列总计的写法，万分感谢