• 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
  • 相关阅读:
    C# Trace 信息写入日志文件
    C# 获取操作系统版本和Service Pack版本的方法
    C# 控制系统服务
    log4net记录日志过程中一直占用日志文件的解决方法
    FileStream类学习小结
    Centos7 部署.net core2.1 详细步骤
    linux 安装redis 完整步骤
    Asp.Net Core Linux环境下 找不到配置文件、静态文件的问题
    关于Clipboard剪切板获取值为NULL问题解决方法
    STA和MTA线程模式的区别
  • 原文地址：https://www.cnblogs.com/lihaoyang/p/8649338.html
Copyright © 2020-2023  润新知