行转列经典案例

创建数据库 并添加数据

CREATE TABLE t_company (
comp_id varchar(32) NOT NULL COMMENT ‘公司ID’,
comp_name varchar(64) NOT NULL COMMENT ‘公司名字’,
type varchar(20) DEFAULT NULL COMMENT ‘公司类型’,
declaration varchar(255) DEFAULT NULL COMMENT ‘招聘宣言’,
comp_total int(11) DEFAULT NULL COMMENT ‘公司人数’,
comp_positin varchar(32) DEFAULT NULL COMMENT ‘创建者职位’,
bank_type varchar(24) DEFAULT NULL COMMENT ‘银行类型’,
comp_addr varchar(30) DEFAULT NULL COMMENT ‘公司地址’,
comp_represent varchar(30) DEFAULT NULL COMMENT ‘企业法定代表人’,
comp_creditcode varchar(60) DEFAULT NULL COMMENT ‘社会统一信用代码’,
bank_account varchar(30) DEFAULT NULL COMMENT ‘公司银行账号’,
payroll_account varchar(30) DEFAULT NULL COMMENT ‘工资发放账号’,
class_id varchar(32) DEFAULT NULL COMMENT ‘课程id’,
create_time datetime DEFAULT NULL COMMENT ‘创建时间’,
update_time datetime DEFAULT NULL COMMENT ‘更新时间’,
PRIMARY KEY (comp_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=‘公司信息表’;

/*Data for the table t_company */

insert into t_company(comp_id,comp_name,type,declaration,comp_total,comp_positin,bank_type,comp_addr,comp_represent,comp_creditcode,bank_account,payroll_account,class_id,create_time,update_time) values

(‘2e333c198d1849ba93fc5751280e69c9’,‘养猪企业’,‘breed’,‘招聘’,6,‘总经理-场长’,‘ICBC’,‘北京市海淀区66号’,‘王建国’,‘201911081450103335’,‘6212263519661711390’,‘6212268921971324052’,‘3eab02f25bcd464cb5a5d52c9cfc4638’,‘2019-11-08 06:50:11’,NULL),

(‘373fc4619880425fbfde261fdcf00dc2’,‘养殖公司’,‘breed’,‘招聘’,6,‘总经理-场长’,‘ICBC’,‘北京市海淀区66号’,‘王建国’,‘201911081500270745’,‘621226236926787017’,‘6212269980703429538’,‘2ee385c740da4875b024021515d10732’,‘2019-11-08 07:00:27’,NULL),

(‘3d38c1e77c224ff085d8d787bbab510b’,‘养殖公司1’,‘breed’,‘招聘’,6,‘总经理-场长’,‘ICBC’,‘北京市海淀区66号’,‘王建国’,‘201911081508372368’,‘621226935524414735’,‘6212269457641246436’,‘0512b41f699a4e3b9663d452933d6f71’,‘2019-11-08 07:08:37’,NULL),

(‘4755c5c602104d2e9705a25772b2d6c6’,‘11’,‘breed’,‘1’,6,‘总经理-场长’,NULL,‘北京市海淀区66号’,‘1’,‘Fri Nov 08 13:17:00 CST 20197893’,NULL,NULL,‘3eab02f25bcd464cb5a5d52c9cfc4638’,‘2019-11-08 05:17:18’,NULL),

(‘481f27ee43114d2f867b97a451443de1’,‘1234’,‘breed’,‘123’,6,‘总经理-场长’,NULL,‘北京市海淀区66号’,‘123’,‘Fri Nov 08 13:19:00 CST 20197618’,NULL,NULL,‘3eab02f25bcd464cb5a5d52c9cfc4638’,‘2019-11-08 05:19:16’,NULL),

(‘5b65188b40b34730b46f68ac71e46f2c’,‘1232123’,‘breed’,‘12321’,6,‘总经理-场长’,‘ABC’,‘北京市海淀区66号’,‘3213123’,‘201911081331330644’,NULL,NULL,‘3eab02f25bcd464cb5a5d52c9cfc4638’,‘2019-11-08 05:31:34’,NULL),

(‘5c158a8788854686b0b69e0740fed744’,‘321’,‘cultivation’,‘312’,6,‘总经理-场长’,‘ICBC’,‘北京市海淀区66号’,‘312’,‘201911081333343380’,NULL,NULL,‘3eab02f25bcd464cb5a5d52c9cfc4638’,‘2019-11-08 05:33:35’,NULL),

(‘76658f8f22014a598d44aa407a4ddeb3’,‘中国农业银行’,‘ABC’,‘11’,2,‘银行经理’,NULL,‘北京市海淀区66号’,‘1’,‘Fri Nov 08 13:17:00 CST 20195737’,NULL,NULL,‘3eab02f25bcd464cb5a5d52c9cfc4638’,‘2019-11-08 05:17:29’,NULL),

(‘772790c7aec7436abaf81874251da92e’,‘3213’,‘breed’,‘321’,6,‘总经理-场长’,‘ICBC’,‘北京市海淀区66号’,‘321’,‘201911081424125116’,‘6212268426858434227’,‘621226275854973285’,‘3eab02f25bcd464cb5a5d52c9cfc4638’,‘2019-11-08 06:24:12’,NULL),

(‘a9e97422ed644372985f212a18a8c4c0’,‘养殖公司33’,‘breed’,‘招聘’,6,‘总经理-场长’,‘ICBC’,‘北京市海淀区66号’,‘王钢蛋’,‘201911081514106797’,‘6212266926652462563’,‘6212266589948491519’,‘09964adbf63c4beea0a9fa6247b678be’,‘2019-11-08 07:14:11’,NULL),

(‘abd7d0ac3ed74c80a6579c0ae7173651’,‘中国工商银行’,‘ICBC’,‘欢迎来到工商银行’,2,‘银行经理’,’’,‘北京市海淀区66号’,‘卡卡西’,‘201911081457173020’,‘6228249972041879510’,‘6228270583549677485’,‘3eab02f25bcd464cb5a5d52c9cfc4638’,‘2019-11-08 06:57:18’,NULL),

(‘b2bddb9e80034e3492b3f958dac17a77’,‘432’,‘cultivation’,‘42’,6,‘总经理-场长’,‘ICBC’,‘北京市海淀区66号’,‘423’,‘201911081335533370.0430101596166’,NULL,NULL,‘3eab02f25bcd464cb5a5d52c9cfc4638’,‘2019-11-08 05:35:54’,NULL),

(‘b964fbca93e34492af67a776e96375d5’,‘123’,‘cultivation’,‘123’,6,‘总经理-场长’,NULL,‘北京市海淀区66号’,‘123’,‘Fri Nov 08 13:16:00 CST 20199827’,NULL,NULL,‘3eab02f25bcd464cb5a5d52c9cfc4638’,‘2019-11-08 05:16:09’,NULL),

(‘bef7b6c8205a4716b88279f937b71397’,‘121’,‘breed’,‘121’,6,‘总经理-场长’,‘ICBC’,‘北京市海淀区66号’,‘121’,‘Fri Nov 08 13:20:00 CST 20193254’,NULL,NULL,‘3eab02f25bcd464cb5a5d52c9cfc4638’,‘2019-11-08 05:20:51’,NULL),

(‘f3dd676d5b764a059ca55027ea74281a’,‘养殖公司34’,‘breed’,‘招聘’,6,‘总经理-场长’,‘ICBC’,‘北京市海淀区66号’,‘王钢蛋’,‘201911081514329294’,‘6212263451974483072’,‘6212263634047470097’,‘09964adbf63c4beea0a9fa6247b678be’,‘2019-11-08 07:14:33’,NULL),

(‘f6eb132bee164f7395453eb914d1284a’,‘1’,‘cultivation’,‘1’,6,‘总经理-场长’,‘ICBC’,‘北京市海淀区66号’,‘王钢蛋’,‘201911081537277729’,‘6212267162513973417’,‘6212261720269562049’,‘09964adbf63c4beea0a9fa6247b678be’,‘2019-11-08 07:37:27’,NULL),

(‘f81751d172854d3294a6f8b0ca4c5971’,‘12’,‘breed’,‘1’,6,‘总经理-场长’,‘ICBC’,‘北京市海淀区66号’,‘王钢蛋’,‘201911081539542253’,‘621226445285685389’,‘621226746515106007’,‘09964adbf63c4beea0a9fa6247b678be’,‘2019-11-08 07:39:55’,NULL);

首选我们看本来 得代码

SELECT COUNT(1),tc.type FROM t_company tc
GROUP BY tc.type

然后我们看我们改变得代码
SELECT
SUM(CASE tc.type WHEN ‘cultivation’ THEN COUNT ELSE 0 END) cultivation,
SUM(CASE tc.type WHEN ‘breed’ THEN COUNT ELSE 0 END) breed,
SUM(CASE tc.type WHEN ‘supermarket’ THEN COUNT ELSE 0 END) supermarket,
SUM(CASE tc.type WHEN ‘service’ THEN COUNT ELSE 0 END) service,
SUM(CASE tc.type WHEN ‘ICBC’ THEN COUNT ELSE 0 END) ICBC,
SUM(CASE tc.type WHEN ‘ABC’ THEN COUNT ELSE 0 END) ABC,
SUM(CASE tc.type WHEN ‘insureance’ THEN COUNT ELSE 0 END) insureance,
SUM(CASE tc.type WHEN ‘conglomerate’ THEN COUNT ELSE 0 END) conglomerate
FROM (
SELECT COUNT(1) COUNT,tc1.type FROM t_company tc1
WHERE tc1.class_id = ‘3eab02f25bcd464cb5a5d52c9cfc4638’
GROUP BY tc1.type
) tc

运行结果为

新增一个

SELECT
  CODE,
  /* if col1 matches the name string of this CASE, return col2, otherwise return NULL /
  / Then, the outer MAX() aggregate will eliminate all NULLs and collapse it down to one row per id */
  MAX(CASE WHEN (class = ‘语文’) THEN score ELSE NULL END) AS 语文,
  MAX(CASE WHEN (class = ‘数学’) THEN score ELSE NULL END) AS 数学,
  MAX(CASE WHEN (class = ‘英语’) THEN score ELSE NULL END) AS 英语
FROM
  score
GROUP BY CODE