时间戳

首页 > 解决方案 > 如何将映射表中的几行汇总到列中？

sql - 如何将映射表中的几行汇总到列中？

问题描述

我目前有一个可以简化为的表结构：

CREATE TABLE Users (
    Id INT NOT NULL PRIMARY KEY,
    Email VARCHAR(100) NOT NULL
);

CREATE TABLE Orders (
    Id INT NOT NULL PRIMARY KEY,
    UserId INT NOT NULL FOREIGN KEY REFERENCES Users(Id)
);

CREATE TABLE Logos (
    Id INT NOT NULL PRIMARY KEY,
    [Name] VARCHAR(100) NOT NULL
);

CREATE TABLE OrderLogos (
    Order_Id INT NOT NULL FOREIGN KEY REFERENCES Orders(Id),
    Logo_Id INT NOT NULL FOREIGN KEY REFERENCES Logos(Id)
);

INSERT INTO Users(Id, Email)
VALUES (1, 'test@email.com');

INSERT INTO Logos(Id, [Name])
VALUES (1, 'First Logo'), (2, 'Second Logo'), (3, 'Third Logo'), (4, 'Fourth Logo');

INSERT INTO Orders(Id, UserId)
VALUES (1, 1), (2, 1);

INSERT INTO OrderLogos(Order_Id, Logo_Id)
VALUES (1, 1), (1, 2), (1, 3), (1, 4), (2, 1), (2, 3);

可以看到，和之间的关系Orders是Logos多对多的，映射表是OrderLogos。

我现在需要编写一个查询，每个订单将返回 1 行，前 4 个徽标映射到它，例如

SELECT
    o.Id AS OrderId,
    u.[Email] AS UserEmail,
    ? AS Logo1,
    ? AS Logo2,
    ? AS Logo3,
    ? AS Logo4
FROM Orders o
JOIN Users u ON o.UserId = u.Id
? - somehow join to Logos while retaining 1 row per order

其中Logo1-Logo4将是表中的Name字段Logos。我已经研究过使用PIVOTand FOR XML，但这些功能对我来说是新的，我不太明白如何在这种情况下应用其中任何一个。

根据上面的示例数据，我希望结果行是：

1, 'test@email.com', 'First Logo', 'Second Logo', 'Third Logo', 'Fourth Logo'
2, 'test@email.com', 'First Logo', 'Third Logo', NULL, NULL

标签： sqlsql-server

解决方案

也许使用子查询？如果您只想要任何订单的 4 个第一个徽标，您可以使用如下子查询：

SELECT
o.Id AS OrderId,
u.[Email] AS UserEmail,
l1.Logo1,
l2.Logo2,
l3.Logo3,
l4.Logo4
FROM Orders o
JOIN Users u ON o.UserId = u.Id
OUTER APPLY (SELECT l.Name AS Logo1 
             FROM OrderLogos ol JOIN #Logos l ON ol.Logo_Id = l.Id 
             WHERE Order_Id = o.id 
             ORDER BY Logo_Id  
             OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY) L1

OUTER APPLY (SELECT l.Name AS Logo2 
             FROM OrderLogos ol 
             JOIN Logos l ON ol.Logo_Id = l.Id 
             WHERE Order_Id = o.id 
             ORDER BY Logo_Id  
             OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY) L2

OUTER APPLY (SELECT l.Name AS Logo3 
             FROM OrderLogos ol 
             JOIN Logos l ON ol.Logo_Id = l.Id 
             WHERE Order_Id = o.id 
             ORDER BY Logo_Id  
             OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY) L3

OUTER APPLY (SELECT l.Name AS Logo4 
             FROM OrderLogos ol 
             JOIN Logos l ON ol.Logo_Id = l.Id 
             WHERE Order_Id = o.id 
             ORDER BY Logo_Id  
             OFFSET 3 ROWS FETCH NEXT 1 ROWS ONLY) L4

不漂亮，但认为它会工作......

推荐阅读