SQL Server SELECT查询数据转置无多子查询写法优化问询

需求说明 #

现有查询可以得到预期结果，但需要避免使用t1、t2、t3这类嵌套子查询，核心需求是将dbo.ReviewsData的纵表数据转置为单行，和dbo.Products、dbo.Reviews表关联后的结果合并输出。

原始参考代码 #

-- 建表语句和测试数据
CREATE TABLE dbo.Products (
idProduct int identity,
product_title varchar(100)
PRIMARY KEY (idProduct)
);
INSERT INTO dbo.Products VALUES
(1001, 'poptart'),
(1002, 'coat hanger'),
(1003, 'sunglasses');

CREATE TABLE dbo.Reviews (
Rev_IDReview int identity,
Rev_IDProduct int
PRIMARY KEY (Rev_IDReview)
FOREIGN KEY (Rev_IDProduct) REFERENCES dbo.Products(idProduct)
);
INSERT INTO dbo.Reviews VALUES
(456, 1001),
(457, 1002),
(458, 1003);

CREATE TABLE dbo.ReviewFields (
RF_IDField int identity,
RF_FieldName varchar(32),
PRIMARY KEY (RF_IDField)
);
INSERT INTO dbo.ReviewFields VALUES
(1, 'Customer Name'),
(2, 'Review Title'),
(3, 'Review Message');

CREATE TABLE dbo.ReviewData (
RD_idData int identity,
RD_IDReview int,
RD_IDField int,
RD_FieldContent varchar(100)
PRIMARY KEY (RD_idData)
FOREIGN KEY (RD_IDReview) REFERENCES dbo.Reviews(Rev_IDReview)
);
INSERT INTO dbo.ReviewData VALUES
(79, 456, 1, 'Daniel'),
(80, 456, 2, 'Love this item!'),
(81, 456, 3, 'Works well...blah blah'),
(82, 457, 1, 'Joe!'),
(84, 457, 2, 'Pure Trash'),
(85, 457, 3, 'It was literally a used banana peel'),
(86, 458, 1, 'Karen'),
(87, 458, 2, 'Could be better'),
(88, 458, 3, 'I can always find something wrong');        

-- 原有查询语句
SELECT P.product_title as "item", t1.ReviewedBy, t2.ReviewTitle, t3.ReviewContent
        FROM dbo.Reviews R
        
        INNER JOIN dbo.Products P
        ON P.idProduct = R.Rev_IDProduct
        
        INNER JOIN (
           SELECT D.RD_FieldContent AS "ReviewedBy", D.RD_IDReview
           FROM dbo.ReviewsData D
           WHERE D.RD_IDField = 1
        ) t1
        ON t1.RD_IDReview = R.Rev_IDReview
        
        INNER JOIN (
           SELECT D.RD_FieldContent AS "ReviewTitle", D.RD_IDReview
           FROM dbo.ReviewsData D
           WHERE D.RD_IDField = 2
        ) t2
        ON t2.RD_IDReview = R.Rev_IDReview
        
        INNER JOIN (
           SELECT D.RD_FieldContent AS "ReviewContent", D.RD_IDReview
           FROM dbo.ReviewsData D
           WHERE D.RD_IDField = 3
        ) t3
        ON t3.RD_IDReview = R.Rev_IDReview

优化后查询代码 #

推荐用条件聚合实现行转列，无需嵌套子查询，仅需关联一次ReviewsData表，兼容性可覆盖绝大多数数据库：

SELECT 
    P.product_title AS item,
    MAX(CASE WHEN D.RD_IDField = 1 THEN D.RD_FieldContent END) AS ReviewedBy,
    MAX(CASE WHEN D.RD_IDField = 2 THEN D.RD_FieldContent END) AS ReviewTitle,
    MAX(CASE WHEN D.RD_IDField = 3 THEN D.RD_FieldContent END) AS ReviewContent
FROM dbo.Reviews R
INNER JOIN dbo.Products P 
    ON P.idProduct = R.Rev_IDProduct
INNER JOIN dbo.ReviewData D 
    ON D.RD_IDReview = R.Rev_IDReview
GROUP BY R.Rev_IDReview, P.product_title

如果使用的是SQL Server数据库，也可以用原生PIVOT语法实现：

SELECT 
    P.product_title AS item,
    [1] AS ReviewedBy,
    [2] AS ReviewTitle,
    [3] AS ReviewContent
FROM dbo.Reviews R
INNER JOIN dbo.Products P 
    ON P.idProduct = R.Rev_IDProduct
INNER JOIN dbo.ReviewData D 
    ON D.RD_IDReview = R.Rev_IDReview
PIVOT (
    MAX(RD_FieldContent)
    FOR RD_IDField IN ([1],[2],[3])
) AS PivotTable

优化优势 #

• 减少ReviewsData表的扫描次数，从原写法的3次降为1次，数据量越大性能提升越明显
• 没有嵌套子查询，代码逻辑更简洁，可读性和可维护性更高
• 条件聚合写法兼容性更强，可在MySQL、PostgreSQL、SQL Server等绝大多数数据库中通用
• 后续需要新增展示字段时，仅需新增对应CASE WHEN逻辑即可，无需新增关联逻辑

内容的提问来源于stack exchange，提问作者DanCan