mysql 行转列

行转列 案例一

1.准备表和数据

 

CREATE TABLE test_user (
  name varchar(50) DEFAULT NULL,
  subject varchar(50) DEFAULT NULL,
  score int(11) DEFAULT NULL
);
insert into test_user values
('zhangsan' , 'chinese' , 10),
('zhangsan' , 'math' , 20),
('zhangsan' , 'english' , 30),
('lily' , 'chinese' , 40),
('lily' , 'math' , 50),
('lily' , 'english' , 60),
('mini' , 'chinese' , 70),
('mini' , 'math' , 80),
('mini' , 'english' , 90);

效果图

 

 

2.行转列

 

select name,
 max(IF(subject = 'chinese',score,0)) as 'chinese',
 max(IF(subject = 'math',score,0)) as 'math',
 max(IF(subject = 'english',score,0)) as 'english',
 sum(score) as'total'
from test_user
group by name

 

 

 

 

 

 

IF(subject = 'chinese',score,0)，指定列的值，若，是指定列，则列的值为score或者0，所以，会有max函数的出现

max()，最大值，根据分组，会出现三条数据，当前score的分数、0、0，使用max函数，则匹配到需求数据

 

tips：

此种方法，需要提前知道列名，有一定局限性。如果需要套公式的效果，则可能需要存储过程，一条sql可能搞不定

 

 

行转列 案例二

问题：

   一张表 表里有两个字段 剧场 月份 

   要求按照图片在控制台输出每个月每个剧场的总数 （剧场数量不限 月份1——12月）

   剧场是剧场名称，月份存的是演出的时间yyyy-MM-dd hh:mm:ss

分析了下，这个问题，主要有2个点，解决了这2个点，就能解决问题

第一个点：时间搓-->月份。

  这个不难，写个方法就可以了。

第二个点：行转列

  这个也比较常见。

  于是，就开始解题了

1.表中数据

2.行转列

SELECT
    t.tname,
    MAX(IF(t.ttime = '1月', 1, 0)) AS '1月',
    MAX(IF(t.ttime = '2月', 1, 0)) AS '2月',
    MAX(IF(t.ttime = '3月', 1, 0)) AS '3月',
    MAX(IF(t.ttime = '4月', 1, 0)) AS '4月',
    MAX(IF(t.ttime = '5月', 1, 0)) AS '5月',
    MAX(IF(t.ttime = '6月', 1, 0)) AS '6月',
    MAX(IF(t.ttime = '7月', 1, 0)) AS '7月',
    MAX(IF(t.ttime = '8月', 1, 0)) AS '8月',
    MAX(IF(t.ttime = '9月', 1, 0)) AS '9月',
    MAX(IF(t.ttime = '10月', 1, 0)) AS '10月',
    MAX(IF(t.ttime = '11月', 1, 0)) AS '11月',
    MAX(IF(t.ttime = '12月', 1, 0)) AS '12月'
FROM
    (
        SELECT
            tname AS tname,
            checkMonth (ttime) AS ttime
        FROM
            `theatre` th
    ) t
GROUP BY
    t.tname;

满怀欢喜的运行，然而，结果并不如人意。

很明显，7月的“上海大舞台”应该是2，才对，但是，查询出来却是1！

想了下，原来我在select 月份中，写的有问题，这个地方必须是动态的值

在select中计算肯定是不行的，必须得嵌套一个子查询了

修改后，如下

select
    ttt.tname,
 max(if(ttt.ttime = '1月',ttt.total,0)) as '1月',
 max(if(ttt.ttime = '2月',ttt.total,0)) as '2月',
 max(if(ttt.ttime = '3月',ttt.total,0)) as '3月',
 max(if(ttt.ttime = '4月',ttt.total,0)) as '4月',
 max(if(ttt.ttime = '5月',ttt.total,0)) as '5月',
 max(if(ttt.ttime = '6月',ttt.total,0)) as '6月',
 max(if(ttt.ttime = '7月',ttt.total,0)) as '7月',
 max(if(ttt.ttime = '8月',ttt.total,0)) as '8月',
 max(if(ttt.ttime = '9月',ttt.total,0)) as '9月',
 max(if(ttt.ttime = '10月',ttt.total,0)) as '10月',
 max(if(ttt.ttime = '11月',ttt.total,0)) as '11月',
 max(if(ttt.ttime = '12月',ttt.total,0)) as '12月'
FROM
(
SELECT
    tt.tname,
    tt.ttime,
    count(1) AS 'total'
FROM
    (
        SELECT
            tname AS tname,
            checkMonth (ttime) AS ttime
        FROM
            `theatre` th
    ) tt
GROUP BY
    tt.tname,
    tt.ttime
) ttt
GROUP BY 
  ttt.tname

执行结果