数据库中有三张表，如下：
写一段SQL语句，统计出下图数据集（期数为N期，不是固定的2期）
创建表
CREATE TABLE dbo.tb_product ( pr_id INT PRIMARY KEY NOT NULL, pr_no NVARCHAR(50) NOT NULL, pr_name NVARCHAR(50) NOT NULL, pr_price DECIMAL(18,2) NULL, pr_fatherid INT NULL, pr_isleaf BIT NOT NULL ) CREATE TABLE dbo.tb_order ( or_no NVARCHAR(50) NOT NULL, pr_id INT NOT NULL, or_quanity INT NOT NULL, pe_id NVARCHAR(50) NOT NULL ) CREATE TABLE dbo.tb_period ( pe_id NVARCHAR(50) NOT NULL, pe_order NVARCHAR(50) NOT NULL, pe_startdate DATETIME NOT NULL, pe_enddate DATETIME NOT NULL )
添加测试数据
INSERT INTO dbo.tb_product(pr_id,pr_no,pr_name,pr_price,pr_fatherid,pr_isleaf) VALUES(1,'a','电子设备',NULL,NULL,0) INSERT INTO dbo.tb_product(pr_id,pr_no,pr_name,pr_price,pr_fatherid,pr_isleaf) VALUES(2,'b','pc',NULL,1,0) INSERT INTO dbo.tb_product(pr_id,pr_no,pr_name,pr_price,pr_fatherid,pr_isleaf) VALUES(3,'c','联想',4399,2,1) INSERT INTO dbo.tb_product(pr_id,pr_no,pr_name,pr_price,pr_fatherid,pr_isleaf) VALUES(4,'d','戴尔',4799,2,1) INSERT INTO dbo.tb_product(pr_id,pr_no,pr_name,pr_price,pr_fatherid,pr_isleaf) VALUES(5,'e','手机',NULL,1,0) INSERT INTO dbo.tb_product(pr_id,pr_no,pr_name,pr_price,pr_fatherid,pr_isleaf) VALUES(6,'f','苹果',8099,5,1) INSERT INTO dbo.tb_product(pr_id,pr_no,pr_name,pr_price,pr_fatherid,pr_isleaf) VALUES(7,'g','华为',6500,5,1) INSERT INTO dbo.tb_order(or_no,pr_id,or_quanity,pe_id) VALUES('N001',3,2,'GUIDA') INSERT INTO dbo.tb_order(or_no,pr_id,or_quanity,pe_id) VALUES('N002',4,3,'GUIDB') INSERT INTO dbo.tb_order(or_no,pr_id,or_quanity,pe_id) VALUES('N003',6,2,'GUIDA') INSERT INTO dbo.tb_order(or_no,pr_id,or_quanity,pe_id) VALUES('N004',7,3,'GUIDB') INSERT INTO dbo.tb_period(pe_id,pe_order,pe_startdate,pe_enddate) VALUES('GUIDA',1,'2018-1-1','2018-1-15') INSERT INTO dbo.tb_period(pe_id,pe_order,pe_startdate,pe_enddate) VALUES('GUIDB',2,'2018-1-16','2018-1-31')
从题目上来看，主要的难点有2个：
1、要求的是N列，N就是tb_period里面的数据
2、要求展示成树型的结构且父节点要求和
我一开始的想法就是改题目，先不做动态列，先不做父节点的求和。由简到繁的一步步走
1、以产品表为主表，既然暂时先不求和，就只查所有叶节点的数据就好了
SELECT p.pr_id,p.pr_fatherid,p.pr_isleaf,p.pr_no,p.pr_name FROM dbo.tb_product p WHERE p.pr_isleaf=1
2、将订单数据按产品和期数分求和连接到上表中
SELECT p.pr_id,p.pr_fatherid,p.pr_isleaf,p.pr_no,p.pr_name,o.totalQuantity,p.pr_price*o.totalQuantity totalAmount FROM dbo.tb_product p LEFT JOIN (SELECT pr_id, o.pe_id, SUM(o.or_quanity) totalQuantity FROM dbo.tb_order o GROUP BY pr_id, o.pe_id) o ON o.pr_id = p.pr_id WHERE p.pr_isleaf=1
3、将分期数据按数量和金额查询连接到上表中
SELECT p.pr_id,p.pr_fatherid,p.pr_isleaf,p.pr_no,p.pr_name,o.totalQuantity,p.pr_price*o.totalQuantity totalAmount,pe.quantityByPeriod,pe.amountByPeriod FROM dbo.tb_product p LEFT JOIN (SELECT pr_id, o.pe_id, SUM(o.or_quanity) totalQuantity FROM dbo.tb_order o GROUP BY pr_id, o.pe_id) o ON o.pr_id = p.pr_id LEFT JOIN (SELECT pe_id,('第' + pe_order + '期数量') quantityByPeriod,('第' + pe_order + '期金额') amountByPeriod FROM dbo.tb_period) pe ON pe.pe_id = o.pe_id WHERE p.pr_isleaf=1
4、以上基础数据都弄好，现在就开始行转列，使用PIVOT行转列，需要注意的是PIVOT只能接在表格后面，所以前面的查询SQL只能放到一个子查询里面
SELECT * FROM (
SELECT p.pr_id,p.pr_fatherid,p.pr_isleaf,p.pr_no,p.pr_name,o.totalQuantity,p.pr_price*o.totalQuantity totalAmount,pe.quantityByPeriod,pe.amountByPeriod FROM dbo.tb_product p
LEFT JOIN (SELECT pr_id, o.pe_id, SUM(o.or_quanity) totalQuantity FROM dbo.tb_order o GROUP BY pr_id, o.pe_id) o ON o.pr_id = p.pr_id
LEFT JOIN (SELECT pe_id,('第' + pe_order + '期数量') quantityByPeriod,('第' + pe_order + '期金额') amountByPeriod FROM dbo.tb_period) pe ON pe.pe_id = o.pe_id
WHERE p.pr_isleaf=1) t
PIVOT (SUM(totalQuantity) FOR quantityByPeriod IN (第1期数量,第2期数量)) PivotTable
5、可以看出来，金额并没有做行转列的统计，原因是PIVOT只能转一个列，要转多个列就要多次嵌套
SELECT * FROM (SELECT * FROM ( SELECT p.pr_id,p.pr_fatherid,p.pr_isleaf,p.pr_no,p.pr_name,o.totalQuantity,p.pr_price*o.totalQuantity totalAmount,pe.quantityByPeriod,pe.amountByPeriod FROM dbo.tb_product p LEFT JOIN (SELECT pr_id, o.pe_id, SUM(o.or_quanity) totalQuantity FROM dbo.tb_order o GROUP BY pr_id, o.pe_id) o ON o.pr_id = p.pr_id LEFT JOIN (SELECT pe_id,('第' + pe_order + '期数量') quantityByPeriod,('第' + pe_order + '期金额') amountByPeriod FROM dbo.tb_period) pe ON pe.pe_id = o.pe_id WHERE p.pr_isleaf=1) t PIVOT (SUM(totalQuantity) FOR quantityByPeriod IN (第1期数量,第2期数量)) t2) t2 PIVOT (SUM(totalAmount) FOR amountByPeriod IN (第1期金额,第2期金额)) t3
6、叶节点的数据完成了，现在要用到递归查询对上级节点求和
WITH Report AS ( SELECT t3.pr_no,t3.pr_name,t3.pr_fatherid,t3.第1期数量,t3.第1期金额,t3.第2期数量,t3.第2期金额 FROM (SELECT * FROM ( SELECT p.pr_id,p.pr_fatherid,p.pr_isleaf,p.pr_no,p.pr_name,o.totalQuantity,p.pr_price*o.totalQuantity totalAmount,pe.quantityByPeriod,pe.amountByPeriod FROM dbo.tb_product p LEFT JOIN (SELECT pr_id, o.pe_id, SUM(o.or_quanity) totalQuantity FROM dbo.tb_order o GROUP BY pr_id, o.pe_id) o ON o.pr_id = p.pr_id LEFT JOIN (SELECT pe_id,('第' + pe_order + '期数量') quantityByPeriod,('第' + pe_order + '期金额') amountByPeriod FROM dbo.tb_period) pe ON pe.pe_id = o.pe_id WHERE p.pr_isleaf=1) t PIVOT (SUM(totalQuantity) FOR quantityByPeriod IN (第1期数量,第2期数量)) t2) t2 PIVOT (SUM(totalAmount) FOR amountByPeriod IN (第1期金额,第2期金额)) t3 UNION ALL SELECT pp.pr_no,pp.pr_name,pp.pr_fatherid,Report.第1期数量,Report.第1期金额,Report.第2期数量,Report.第2期金额 FROM Report INNER JOIN dbo.tb_product pp ON pp.pr_id=Report.pr_fatherid ) SELECT Report.pr_no 编号,Report.pr_name 名称, ISNULL(SUM(Report.第1期数量),0) 第1期数量,ISNULL(SUM(Report.第1期金额),0) 第1期金额, ISNULL(SUM(Report.第2期数量),0) 第2期数量,ISNULL(SUM(Report.第2期金额),0) 第2期金额 FROM Report GROUP BY Report.pr_no,Report.pr_name ORDER BY Report.pr_no
7、最后一步处理动态列，我们需要查询出动态的列名，然后把他拼接到一个变量里面去，使用EXEC执行SQL语句
SELECT STUFF((SELECT ',第' + pe_order + '期数量,第' + pe_order + '期金额' FROM dbo.tb_period FOR XML PATH('')),1,1,'')
完整的拼接如下：
DECLARE @Columns NVARCHAR(2000)SELECT @Columns=STUFF((SELECT ',第' + pe_order + '期数量,第' + pe_order + '期金额' FROM dbo.tb_period FOR XML PATH('')),1,1,'')DECLARE @Sql NVARCHAR(4000)=' WITH Report AS ( SELECT t3.pr_no,t3.pr_name,t3.pr_fatherid,' + @Columns + ' FROM (SELECT * FROM ( SELECT p.pr_id,p.pr_fatherid,p.pr_isleaf,p.pr_no,o.totalQuantity,p.pr_price*o.totalQuantity totalAmount,p.pr_name,pe.Quantity,pe.Amount FROM dbo.tb_product p LEFT JOIN (SELECT pr_id, o.pe_id, SUM(o.or_quanity) totalQuantity FROM dbo.tb_order o GROUP BY pr_id, o.pe_id) o ON o.pr_id = p.pr_id LEFT JOIN (SELECT pe_id,(''第'' + pe_order + ''期数量'') Quantity,(''第'' + pe_order + ''期金额'') Amount FROM dbo.tb_period) pe ON pe.pe_id = o.pe_id WHERE p.pr_isleaf=1) t PIVOT(SUM(totalQuantity) FOR Quantity IN (第1期数量,第2期数量)) t2) t2 PIVOT(SUM(totalAmount) FOR Amount IN (第1期金额,第2期金额)) t3 UNION ALL SELECT pp.pr_no,pp.pr_name,pp.pr_fatherid,' + @Columns + ' FROM Report INNER JOIN dbo.tb_product pp ON pp.pr_id=Report.pr_fatherid ) SELECT Report.pr_no 编号,Report.pr_name 名称, ISNULL(SUM(Report.第1期数量),0) 第1期数量,ISNULL(SUM(Report.第1期金额),0) 第1期金额, ISNULL(SUM(Report.第2期数量),0) 第2期数量,ISNULL(SUM(Report.第2期金额),0) 第2期金额 FROM Report GROUP BY Report.pr_no,Report.pr_name ORDER BY Report.pr_no' PRINT @Sql EXEC(@Sql)
这上面的代码只拼接了一处地方，其他的地方是一样
这道SQL面试题还是比较经典的，中间考到的知识点也挺多的，比如说行合并，行转列，递归查询什么的。
本文仅仅只是个人的思路而已，不是什么最优解法，也希望大家指出更好的思路。
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。