• 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
  • 相关阅读:
    iOS7中都Bar的透明问题
    iOS 如何使用自定义字体
    iOS xib中TableView创建的2种模式
    iOS 保存CGRect，CGPoint到NSArray'的方法
    iOS 中通过使用Google API获得Google服务
    转载一篇ios7的新API文章
    移动开发常用的第三方控件下载网页
    iOS 查看系统字体效果的网页
    iOS 基于UIWebView的应用特点
    Cocos2d 中的Sprite大小调整问题
  • 原文地址：https://www.cnblogs.com/lihaoyang/p/8649338.html
Copyright © 2020-2023  润新知