SQL未知行转列问题

SQL未知行转列问题

问题：找出A省的A药品在2019-2020年的销售情况，输出结果为包含的列名有HospitalId、HospitalName、日期1、日期2…

输出结果如下

HospitalId	HospitalName	2019-01	2019-02	2019-03	2019-04	2019-10
1	一	246.00	NULL	123.00	NULL	NULL
2	二	NULL	NULL	NULL	123.00	NULL

建表

CREATE TABLE `hospital` (
    `HospitalId` varchar(36) NOT NULL,
    `HospitalName` varchar(100) DEFAULT NULL,
    `ProvinceName` varchar(45) DEFAULT NULL,
    `CityName` varchar(45) DEFAULT NULL,
    `Disabled` bit(1) NULL DEFAULT 0,
    PRIMARY KEY (`HospitalId`)
  ) COMMENT='医院表'
select * from hospital;
CREATE TABLE `product` (
  `ProductId` varchar(36) NOT NULL,
  `ProductName` varchar(100) NULL,
  `Price` decimal(10, 2) NOT NULL COMMENT '单价',
  PRIMARY KEY (`ProductId`)
  ) COMMENT='产品表';
CREATE TABLE `sales` (
  `SalesId` varchar(36) NOT NULL,
  `HospitalId` varchar(36) NOT NULL,
  `Period` date NOT NULL COMMENT '日期yyyy/mm/dd',
  `ProductId` varchar(36) NOT NULL,
  `SalesVolume` decimal(10, 2) NOT NULL COMMENT '销售数量',
  PRIMARY KEY (`SalesId`),
  CONSTRAINT `fk_hospitalid` FOREIGN KEY (`HospitalId`) REFERENCES `hospital` (`HospitalId`),
  CONSTRAINT `fk_productid` FOREIGN KEY (`ProductId`) REFERENCES `product` (`ProductId`)
  ) COMMENT='销量表';

插入数据

insert into `hospital` values(1,'一','A','A',1);
insert into `hospital` values(2,'二','A','B',1);
insert into `hospital` values(3,'三','A','C',0);
insert into `hospital` values(4,'四','A','D',1);
insert into `hospital` values(5,'五','B','A',1);
insert into `hospital` values(6,'六','B','B',0);
insert into `hospital` values(7,'七','B','C',1);
insert into `hospital` values(8,'八','B','D',1);
insert into `product` values(1,'A药品',1.23);
insert into `product` values(2,'B药品',2.23);
insert into `product` values(3,'C药品',3.23);
insert into `product` values(4,'D药品',4.23);
insert into `product` values(5,'E药品',10.23);
insert into `sales` values(1,1,'2019/1/2',1,100);
insert into `sales` values(2,1,'2019/3/7',2,100);
insert into `sales` values(3,2,'2019/1/2',2,100);
insert into `sales` values(4,3,'2019/3/2',2,100);
insert into `sales` values(5,1,'2019/3/2',1,100);
insert into `sales` values(6,1,'2019/2/12',2,100);
insert into `sales` values(7,2,'2019/1/2',2,100);
insert into `sales` values(8,3,'2019/1/2',3,100);
insert into `sales` values(9,2,'2019/4/5',1,100);

创建存储过程

delimiter //
create procedure pro_1()
begin
set @sql=null; 
select group_concat(distinct 
  concat('max(case when date_format(Period,"%Y-%m") = 
  ''',date_format(Period,"%Y-%m"),'''
  then SalesId end) as 
  ''',date_format(Period,"%Y-%m"),'''')) 
  into @sql from sales;
set @sql= concat('select 
  hospital.HospitalId,
  hospital.HospitalName,
  ',@sql,'
  from sales 
  left join product 
  on sales.ProductId=product.ProductId 
  left join hospital 
  on sales.HospitalId=hospital.HospitalId 
  where product.ProductName="A药品" 
  and hospital.ProvinceName="A" 
  and year(Period)>=2018 
  and year(Period)<=2019 
  group by hospital.HospitalName，hospital.HospitalName
  having sum(sales.SalesVolume * product.price) < 100000');

prepare tmp from @sql;
execute tmp;
deallocate prepare tmp;
end//

查看存储过程的结果

call pro_1()//

以上题型是找工作过程中遇见的题目，如有侵权，请联系删除