Mysql 行转列

Mysql 行转列

/*
Navicat MySQL Data Transfer

Source Server : 160
Source Server Version : 50629
Source Host : 192.168.1.160:3306
Source Database : data_report

Target Server Type : MYSQL
Target Server Version : 50629
File Encoding : 65001

Date: 2018-04-24 19:18:28
*/

SET FOREIGN_KEY_CHECKS=0;

---

– Table structure for t_datareport_realname_countdetail

---

DROP TABLE IF EXISTS t_datareport_realname_countdetail;
CREATE TABLE t_datareport_realname_countdetail (
id bigint(20) NOT NULL AUTO_INCREMENT,
reportdate varchar(10) NOT NULL DEFAULT ” COMMENT ‘日期’,
reporttime datetime NOT NULL COMMENT ‘报表时间’,
sub_record int(10) DEFAULT ‘0’ COMMENT ‘提交记录数’,
pass_num int(10) DEFAULT ‘0’ COMMENT ‘通过人数’,
unpass_num int(10) DEFAULT NULL COMMENT ‘不通过人数’,
remark varchar(100) DEFAULT NULL COMMENT ‘不通过原因’,
type int(2) DEFAULT ‘0’ COMMENT ‘类型 0:诺正通 1：芝麻信用’,
nzt_overdue_num int(10) DEFAULT ‘0’ COMMENT ‘身份证过期人数’,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8mb4 COMMENT=’实名认证报表统计详情’;

---

– Records of t_datareport_realname_countdetail

---

INSERT INTO t_datareport_realname_countdetail VALUES (‘24’, ‘2017-10-19’, ‘2017-10-10 18:34:36’, ‘1’, ‘2’, ‘3’, ‘的方法’, ‘0’, ‘0’);
INSERT INTO t_datareport_realname_countdetail VALUES (‘25’, ‘2017-10-19’, ‘2017-10-10 18:34:43’, ‘4’, ‘5’, ‘6’, ‘3地方’, ‘1’, ‘0’);
INSERT INTO t_datareport_realname_countdetail VALUES (‘26’, ‘2017-10-20’, ‘2017-10-10 18:34:36’, ‘7’, ‘8’, ‘9’, ‘水电费’, ‘0’, ‘0’);
INSERT INTO t_datareport_realname_countdetail VALUES (‘27’, ‘2017-10-20’, ‘2017-10-10 18:34:43’, ‘10’, ‘11’, ‘12’, ‘的方法’, ‘1’, ‘0’);
INSERT INTO t_datareport_realname_countdetail VALUES (‘28’, ‘2018-02-06’, ‘2018-03-21 14:23:38’, ‘0’, ‘0’, ‘0’, ”, ‘0’, ‘0’);
INSERT INTO t_datareport_realname_countdetail VALUES (‘29’, ‘2018-02-06’, ‘2018-03-21 14:23:38’, ‘0’, ‘0’, ‘0’, ”, ‘1’, ‘0’);

SELECT
t.reportdate AS date,
SUM(CASE type WHEN 0 THEN t.unpass_num ELSE 0 END) AS nuozt_unpass_num,
SUM(CASE type WHEN 0 THEN t.pass_num ELSE 0 END) AS nuozt_pass_num,
SUM(CASE type WHEN 0 THEN t.sub_record ELSE 0 END) AS nuozt_sub_record,
SUM(CASE type WHEN 1 THEN t.unpass_num ELSE 0 END) AS zhima_unpass_num,
SUM(CASE type WHEN 1 THEN t.pass_num ELSE 0 END) AS zhima_pass_num,
SUM(CASE type WHEN 1 THEN t.sub_record ELSE 0 END) AS zhima_sub_record
FROM t_datareport_realname_countdetail t
GROUP BY t.reportdate
ORDER BY t.reportdate DESC
;