sql行转列 - 我俩绝配

sql

CREATE TABLE `t_merchandise` (
  `FId` varchar(20) NOT NULL,
  `FNumber` varchar(20) DEFAULT NULL,
  `FName` varchar(20) DEFAULT NULL,
  `FPrice` int(11) DEFAULT NULL,
  PRIMARY KEY (`FId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of t_merchandise
-- ----------------------------
INSERT INTO `t_merchandise` VALUES ('00001', '1', 'Bacon', '30');
INSERT INTO `t_merchandise` VALUES ('00002', '2', 'Cake', '2');
INSERT INTO `t_merchandise` VALUES ('00003', '3', 'Apple', '6');

-- ----------------------------
-- Table structure for t_person
-- ----------------------------
DROP TABLE IF EXISTS `t_person`;
CREATE TABLE `t_person` (
  `FId` varchar(20) NOT NULL,
  `FNumber` varchar(20) DEFAULT NULL,
  `FName` varchar(20) DEFAULT NULL,
  `FManagerId` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`FId`),
  KEY `FManagerId` (`FManagerId`),
  CONSTRAINT `t_person_ibfk_1` FOREIGN KEY (`FManagerId`) REFERENCES `t_person` (`FId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of t_person
-- ----------------------------
INSERT INTO `t_person` VALUES ('00001', '1', 'Robert', null);
INSERT INTO `t_person` VALUES ('00002', '2', 'John', '00001');
INSERT INTO `t_person` VALUES ('00003', '3', 'Tom', '00001');
INSERT INTO `t_person` VALUES ('00004', '4', 'Jim', '00003');
INSERT INTO `t_person` VALUES ('00005', '5', 'Lily', '00002');
INSERT INTO `t_person` VALUES ('00006', '6', 'Merry', '00003');

-- ----------------------------
-- Table structure for t_purchasebill
-- ----------------------------
DROP TABLE IF EXISTS `t_purchasebill`;
CREATE TABLE `t_purchasebill` (
  `FId` varchar(20) NOT NULL,
  `FNumber` varchar(20) DEFAULT NULL,
  `FBillMakerId` varchar(20) DEFAULT NULL,
  `FMakeDate` datetime DEFAULT NULL,
  `FConfirmDate` datetime DEFAULT NULL,
  PRIMARY KEY (`FId`),
  KEY `FBillMakerId` (`FBillMakerId`),
  CONSTRAINT `t_purchasebill_ibfk_1` FOREIGN KEY (`FBillMakerId`) REFERENCES `t_person` (`FId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of t_purchasebill
-- ----------------------------
INSERT INTO `t_purchasebill` VALUES ('00001', '1', '00006', '2007-02-15 00:00:00', '2007-02-15 00:00:00');
INSERT INTO `t_purchasebill` VALUES ('00002', '2', '00004', '2003-02-25 00:00:00', '2006-03-03 00:00:00');
INSERT INTO `t_purchasebill` VALUES ('00003', '3', '00001', '2007-02-12 00:00:00', '2007-07-12 00:00:00');
INSERT INTO `t_purchasebill` VALUES ('00004', '4', '00002', '2007-05-25 00:00:00', '2007-06-15 00:00:00');
INSERT INTO `t_purchasebill` VALUES ('00005', '5', '00002', '2007-03-17 00:00:00', '2007-04-15 00:00:00');
INSERT INTO `t_purchasebill` VALUES ('00006', '6', null, '2006-02-03 00:00:00', '2006-11-20 00:00:00');

-- ----------------------------
-- Table structure for t_purchasebilldetail
-- ----------------------------
DROP TABLE IF EXISTS `t_purchasebilldetail`;
CREATE TABLE `t_purchasebilldetail` (
  `FId` varchar(20) NOT NULL,
  `FBillId` varchar(20) DEFAULT NULL,
  `FMerchandiseId` varchar(20) DEFAULT NULL,
  `FCount` int(11) DEFAULT NULL,
  PRIMARY KEY (`FId`),
  KEY `FBillId` (`FBillId`),
  KEY `FMerchandiseId` (`FMerchandiseId`),
  CONSTRAINT `t_purchasebilldetail_ibfk_1` FOREIGN KEY (`FBillId`) REFERENCES `t_purchasebill` (`FId`),
  CONSTRAINT `t_purchasebilldetail_ibfk_2` FOREIGN KEY (`FMerchandiseId`) REFERENCES `t_merchandise` (`FId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of t_purchasebilldetail
-- ----------------------------
INSERT INTO `t_purchasebilldetail` VALUES ('00001', '00001', '00002', '12');
INSERT INTO `t_purchasebilldetail` VALUES ('00002', '00001', '00001', '20');
INSERT INTO `t_purchasebilldetail` VALUES ('00003', '00002', '00001', '32');
INSERT INTO `t_purchasebilldetail` VALUES ('00004', '00002', '00003', '18');
INSERT INTO `t_purchasebilldetail` VALUES ('00005', '00002', '00002', '88');
INSERT INTO `t_purchasebilldetail` VALUES ('00006', '00003', '00003', '19');
INSERT INTO `t_purchasebilldetail` VALUES ('00007', '00003', '00002', '6');
INSERT INTO `t_purchasebilldetail` VALUES ('00008', '00003', '00001', '2');
INSERT INTO `t_purchasebilldetail` VALUES ('00009', '00004', '00001', '20');
INSERT INTO `t_purchasebilldetail` VALUES ('00010', '00004', '00003', '18');
INSERT INTO `t_purchasebilldetail` VALUES ('00011', '00005', '00002', '19');
INSERT INTO `t_purchasebilldetail` VALUES ('00012', '00005', '00001', '26');
INSERT INTO `t_purchasebilldetail` VALUES ('00013', '00006', '00003', '3');
INSERT INTO `t_purchasebilldetail` VALUES ('00014', '00006', '00001', '22');
INSERT INTO `t_purchasebilldetail` VALUES ('00015', '00006', '00002', '168');

-- ----------------------------
-- Table structure for t_salebill
-- ----------------------------
DROP TABLE IF EXISTS `t_salebill`;
CREATE TABLE `t_salebill` (
  `FId` varchar(20) NOT NULL,
  `FNumber` varchar(20) DEFAULT NULL,
  `FBillMakerId` varchar(20) DEFAULT NULL,
  `FMakeDate` datetime DEFAULT NULL,
  `FConfirmDate` datetime DEFAULT NULL,
  PRIMARY KEY (`FId`),
  KEY `FBillMakerId` (`FBillMakerId`),
  CONSTRAINT `t_salebill_ibfk_1` FOREIGN KEY (`FBillMakerId`) REFERENCES `t_person` (`FId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of t_salebill
-- ----------------------------
INSERT INTO `t_salebill` VALUES ('00001', '1', '00006', '2007-03-15 00:00:00', '2007-05-15 00:00:00');
INSERT INTO `t_salebill` VALUES ('00002', '2', null, '2006-01-25 00:00:00', '2006-02-03 00:00:00');
INSERT INTO `t_salebill` VALUES ('00003', '3', '00001', '2006-02-12 00:00:00', '2007-01-11 00:00:00');
INSERT INTO `t_salebill` VALUES ('00004', '4', '00003', '2008-05-25 00:00:00', '2008-06-15 00:00:00');
INSERT INTO `t_salebill` VALUES ('00005', '5', '00005', '2008-03-17 00:00:00', '2007-04-15 00:00:00');
INSERT INTO `t_salebill` VALUES ('00006', '6', '00002', '2002-02-03 00:00:00', '2007-11-11 00:00:00');

-- ----------------------------
-- Table structure for t_salebilldetail
-- ----------------------------
DROP TABLE IF EXISTS `t_salebilldetail`;
CREATE TABLE `t_salebilldetail` (
  `FId` varchar(20) NOT NULL,
  `FBillId` varchar(20) DEFAULT NULL,
  `FMerchandiseId` varchar(20) DEFAULT NULL,
  `FCount` int(11) DEFAULT NULL,
  PRIMARY KEY (`FId`),
  KEY `FBillId` (`FBillId`),
  KEY `FMerchandiseId` (`FMerchandiseId`),
  CONSTRAINT `t_salebilldetail_ibfk_1` FOREIGN KEY (`FBillId`) REFERENCES `t_salebill` (`FId`),
  CONSTRAINT `t_salebilldetail_ibfk_2` FOREIGN KEY (`FMerchandiseId`) REFERENCES `t_merchandise` (`FId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of t_salebilldetail
-- ----------------------------
INSERT INTO `t_salebilldetail` VALUES ('00001', '00001', '00003', '20');
INSERT INTO `t_salebilldetail` VALUES ('00002', '00001', '00001', '30');
INSERT INTO `t_salebilldetail` VALUES ('00003', '00001', '00002', '22');
INSERT INTO `t_salebilldetail` VALUES ('00004', '00002', '00003', '12');
INSERT INTO `t_salebilldetail` VALUES ('00005', '00002', '00002', '11');
INSERT INTO `t_salebilldetail` VALUES ('00006', '00003', '00001', '60');
INSERT INTO `t_salebilldetail` VALUES ('00007', '00003', '00002', '2');
INSERT INTO `t_salebilldetail` VALUES ('00008', '00003', '00003', '5');
INSERT INTO `t_salebilldetail` VALUES ('00009', '00004', '00001', '16');
INSERT INTO `t_salebilldetail` VALUES ('00010', '00004', '00002', '8');
INSERT INTO `t_salebilldetail` VALUES ('00011', '00004', '00003', '9');
INSERT INTO `t_salebilldetail` VALUES ('00012', '00005', '00001', '6');
INSERT INTO `t_salebilldetail` VALUES ('00013', '00005', '00003', '26');
INSERT INTO `t_salebilldetail` VALUES ('00014', '00006', '00001', '66');
INSERT INTO `t_salebilldetail` VALUES ('00015', '00006', '00002', '518');

结果集转置
使用SQL语句可以创建非常复杂的报表，不过出于美观性和兼容使用者喜欢的考虑经常需要调整报表的输出格式，通过结果集转置可以快速调整输出格式，本节将介绍几种常见的结果集转置方式。

1 将结果集转置为一行

假设只有Bacon、Cake和Apple三种产品，要求统计每种产品的销售量。要求以下面的格式显示：

将订单详情表和商品表进行join，查询出每种商品的名称、id、销售量

select m.fid mId,m.fname mName,SUM(sd.fcount) saleCount from t_salebilldetail sd JOIN t_merchandise m ON sd.fmerchandiseid=m.fid GROUP BY m.fname,m.fid

思路，一行要想出来3列，肯定要用标量查询，对每一行的某个列进行判断，如果满足某个条件，则显示为什么名字。如对商品id列进行判断，如果id=00003 则显示Apple，如果id=00001则显示Bacon，如果id=00002则显示Cake，判断则需要case when

这样判断三次，就出来了三列：

select CASE A.mId WHEN '00003' THEN saleCount  ELSE 0 END  AS 'Apple',
 CASE A.mId WHEN '00001' THEN saleCount  ELSE 0 END  AS 'Bacon',
 CASE A.mId WHEN '00002' THEN saleCount  ELSE 0 END  AS 'Cake'
 FROM
(
select m.fid mId,m.fname mName,SUM(sd.fcount) saleCount from t_salebilldetail sd JOIN t_merchandise m ON sd.fmerchandiseid=m.fid GROUP BY m.fname,m.fid
)A

由于select每一行的时候，都是case when 的商品id，当case第一行的时候，只有第一个when满足条件，其他的两个都不满足所以是0，这样就出现了上图的结果，下一步再对每一列进行sum即可：

select sum(CASE A.mId WHEN '00003' THEN saleCount  ELSE 0 END)  AS 'Apple',
 sum(CASE A.mId WHEN '00001' THEN saleCount  ELSE 0 END)  AS 'Bacon',
 sum(CASE A.mId WHEN '00002' THEN saleCount  ELSE 0 END)  AS 'Cake'
 FROM
(
select m.fid mId,m.fname mName,SUM(sd.fcount) saleCount from t_salebilldetail sd JOIN t_merchandise m ON sd.fmerchandiseid=m.fid GROUP BY m.fname,m.fid
)A

欢迎关注个人公众号一起交流学习：

发表于 2018-03-26 10:34 我俩绝配阅读(203) 评论(0) 编辑收藏举报