sql

有张变态的表 submit_channel中有三个字段1.2.3 需求呢就是

其实就是行转列

上代码

1 建表 并插数据

CREATE TABLE `submit_channel_month` (
  `count` int(10) NOT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `submit_channel` int(1) DEFAULT NULL COMMENT '1:实盘开户；2：预约开户；3：模拟开户',
  `from_page` varchar(30) COLLATE utf8_bin DEFAULT NULL COMMENT '来源那个页面',
  `create_time` varchar(30) COLLATE utf8_bin DEFAULT NULL COMMENT '创建时间',
  `order_id` varchar(30) COLLATE utf8_bin DEFAULT NULL,
  `create_date` varchar(20) COLLATE utf8_bin DEFAULT NULL COMMENT '日期',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


INSERT INTO `submit_channel_month` VALUES ('1', '19', '1', null, '2015-02', null, null);
INSERT INTO `submit_channel_month` VALUES ('5', '20', '2', null, '2015-02', null, null);
INSERT INTO `submit_channel_month` VALUES ('10', '21', '3', null, '2015-02', null, null);
INSERT INTO `submit_channel_month` VALUES ('6', '33', '3', null, '2015-01', null, null);

2 行转列

  SELECT create_time createTime,
    MAX(CASE submit_channel WHEN '1' THEN COUNT ELSE 0 END )shipanCount,
    MAX(CASE submit_channel WHEN '2' THEN COUNT ELSE 0 END )yuyueCount,
    MAX(CASE submit_channel WHEN '3' THEN COUNT ELSE 0 END )moniCount
    FROM submit_channel_month t
    GROUP BY t.create_time

另外要做一个总的统计

    SELECT
        MAX(CASE aa.submitChannel WHEN '1' THEN aa.count ELSE 0 END ) shipanCount,
        MAX(CASE aa.submitChannel WHEN '2' THEN aa.count ELSE 0 END ) yuyueCount,
        MAX(CASE aa.submitChannel WHEN '3' THEN aa.count ELSE 0 END ) moniCount
        FROM (
        SELECT SUM(count) count,submit_channel submitChannel,id  FROM submit_channel_month
        GROUP BY submit_channel) aa 

这句话关键是起了个别名aa  曾经尝试着不用别名 然后老是不成功

项目又有需求改进啦

多了后边的共计一行

为了完成这个需求首先在

MAX(CASE aa.submitChannel WHEN '3' THEN aa.count ELSE 0 END ) moniCount

        FROM (
        SELECT SUM(count) count,submit_channel submitChannel,id  FROM submit_channel_month

中间加入 SUM(aa.count) AS'count'

变成了

  SELECT aa.createDate,
MAX(CASE aa.submitChannel WHEN '1' THEN aa.count ELSE 0 END )shipanCount, 
MAX(CASE aa.submitChannel WHEN '2' THEN aa.count ELSE 0 END )yuyueCount, 
MAX(CASE aa.submitChannel WHEN '3' THEN aa.count ELSE 0 END )moniCount,
SUM(aa.count) AS'count'
  FROM 
  (  
  SELECT t.id AS id, t.submit_channel AS submitChannel, t.from_page AS fromPage, t.create_time AS  createTime,COUNT(submit_channel) AS COUNT,t.create_date AS createDate
  FROM submit_channel t 

就获得了每天的共计 但是还有总计 就是最右下角上边那个

所以又写了一条sql语句用来计算最后一行总计

上代码

SELECT 
MAX(CASE aa.submitChannel WHEN '1' THEN aa.count ELSE 0 END ) shipanCount, 
MAX(CASE aa.submitChannel WHEN '2' THEN aa.count ELSE 0 END ) yuyueCount, 
MAX(CASE aa.submitChannel WHEN '3' THEN aa.count ELSE 0 END ) moniCount,
sum(aa.count) count
FROM
(SELECT COUNT(submit_channel) COUNT,submit_channel submitChannel,id FROM submit_channel  GROUP BY submit_channel) aa

ok 大功告成