一道SQL面试题的解题思路

题目

数据库中有三张表，如下：

 

写一段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面试题还是比较经典的，中间考到的知识点也挺多的，比如说行合并，行转列，递归查询什么的。

本文仅仅只是个人的思路而已，不是什么最优解法，也希望大家指出更好的思路。