SQL Server查询实现空列显示：供应商关联数据完整查询

我有三个主表：Proveedor（供应商）、Equipo（设备）和Servicio（服务），还有两个关联表Rel_ProvEquipo（供应商-设备关联）、Rel_ProvServicio（供应商-服务关联），关联关系均为一对多。

当前查询只能返回同时拥有设备和服务的供应商，无法返回仅拥有设备或仅拥有服务的供应商。我需要修改查询，让所有供应商都能被查询到：仅拥有设备的供应商，其Servicios（服务）列显示为空；仅拥有服务的供应商，其Equipos（设备）列显示为空。

原查询语句 #

SELECT 
    MAX(P.RazonSocial) AS 'Razon Social Proveedor', 
    STUFF((SELECT '; ' + S.TipoServicio 
           FROM Rel_ProvEquipo pE 
           INNER JOIN Servicio S ON pE.idEquipo = S.idServicio 
           WHERE pE.idProveedor = P.idProveedor
           FOR XML PATH ('')), 1, 1, '') [Servicios], 
    STUFF((SELECT '; ' + E.TipoEquipo 
           FROM Rel_ProvEquipo pE
           INNER JOIN Equipo E ON pE.idEquipo = E.idEquipo 
           WHERE pE.idProveedor = P.idProveedor
           FOR XML PATH ('')), 1, 1, '') [Equipos] 
FROM 
    Proveedor P
INNER JOIN 
    Rel_ProvServicio pServ ON P.idProveedor = pServ.idProveedor 
INNER JOIN 
    Rel_ProvEquipo pEquip ON P.idProveedor = pEquip.idProveedor 
GROUP BY 
    P.idProveedor 
ORDER BY 
    P.idProveedor

表结构 #

CREATE TABLE Proveedor 
(
    idProveedor int IDENTITY(1,1) NOT NULL,
    RazonSocial nvarchar (max) NOT NULL,

    CONSTRAINT PK_Proveedor PRIMARY KEY (idProveedor),
)

CREATE TABLE Servicio
(
    idServicio int IDENTITY(1,1) NOT NULL,
    TipoServicio nvarchar(35) NOT NULL,
    CONSTRAINT PK_Servicio PRIMARY KEY (idServicio)
)

CREATE TABLE Equipo
(
    idEquipo int IDENTITY(1,1) NOT NULL, 
    TipoEquipo nvarchar(35) NOT NULL
    CONSTRAINT PK_Equipo PRIMARY KEY (idEquipo)
)

CREATE TABLE Rel_ProvServicio
(
    idProveedor int NOT NULL,
    idServicio int NOT NULL, 
    CONSTRAINT FK_ProvServicio 
        FOREIGN KEY (idProveedor) REFERENCES Proveedor(idProveedor),
    CONSTRAINT FK_ServicioProv 
        FOREIGN KEY (idServicio) REFERENCES Servicio(idServicio) 
)

CREATE TABLE Rel_ProvEquipo
(
    idProveedor int NOT NULL,
    idEquipo int NOT NULL,
    CONSTRAINT FK_ProvEquipo 
        FOREIGN KEY (idProveedor) REFERENCES Proveedor(idProveedor),
    CONSTRAINT FK_EquipoProv 
        FOREIGN KEY (idEquipo) REFERENCES Equipo(idEquipo) 
)

修正后的示例数据 #

-- 供应商数据
INSERT INTO Proveedor (RazonSocial) VALUES ('Provider 1')
INSERT INTO Proveedor (RazonSocial) VALUES ('Provider 2')
INSERT INTO Proveedor (RazonSocial) VALUES ('Provider 3')

-- 服务数据
INSERT INTO Servicio (TipoServicio) VALUES ('Service 1')
INSERT INTO Servicio (TipoServicio) VALUES ('Service 2')
INSERT INTO Servicio (TipoServicio) VALUES ('Service 3')

-- 设备数据
INSERT INTO Equipo (TipoEquipo) VALUES ('Equipment 1')
INSERT INTO Equipo (TipoEquipo) VALUES ('Equipment 2')
INSERT INTO Equipo (TipoEquipo) VALUES ('Equipment 3')

-- 供应商1：同时关联服务和设备
INSERT INTO Rel_ProvEquipo (idProveedor, idEquipo )VALUES (1,1)
INSERT INTO Rel_ProvEquipo (idProveedor, idEquipo )VALUES (1,2)
INSERT INTO Rel_ProvEquipo (idProveedor, idEquipo )VALUES (1,3)

INSERT INTO Rel_ProvServicio (idProveedor, idServicio)VALUES (1,1)
INSERT INTO Rel_ProvServicio(idProveedor, idServicio )VALUES (1,2)
INSERT INTO Rel_ProvServicio(idProveedor, idServicio)VALUES (1,3)

-- 供应商2：仅关联设备
INSERT INTO Rel_ProvEquipo (idProveedor, idEquipo )VALUES (2,1)
INSERT INTO Rel_ProvEquipo (idProveedor, idEquipo )VALUES (2,2)
INSERT INTO Rel_ProvEquipo (idProveedor, idEquipo )VALUES (2,3)

-- 供应商3：仅关联服务
INSERT INTO Rel_ProvServicio (idProveedor, idServicio)VALUES (3,1)
INSERT INTO Rel_ProvServicio (idProveedor, idServicio )VALUES (3,2)
INSERT INTO Rel_ProvServicio (idProveedor, idServicio)VALUES (3,3)

修改后的查询语句 #

SELECT 
    P.RazonSocial AS 'Razon Social Proveedor',
    -- 获取关联的服务，无服务则返回NULL
    STUFF((
        SELECT '; ' + S.TipoServicio 
        FROM Rel_ProvServicio pServ
        INNER JOIN Servicio S ON pServ.idServicio = S.idServicio 
        WHERE pServ.idProveedor = P.idProveedor
        FOR XML PATH ('')
    ), 1, 2, '') AS [Servicios],
    -- 获取关联的设备，无设备则返回NULL
    STUFF((
        SELECT '; ' + E.TipoEquipo 
        FROM Rel_ProvEquipo pE
        INNER JOIN Equipo E ON pE.idEquipo = E.idEquipo 
        WHERE pE.idProveedor = P.idProveedor
        FOR XML PATH ('')
    ), 1, 2, '') AS [Equipos]
FROM 
    Proveedor P
ORDER BY 
    P.idProveedor

修改说明 #

1. 移除不必要的INNER JOIN：原查询用INNER JOIN关联两个关联表，导致只保留同时有设备和服务的供应商。直接从主表Proveedor查询，通过子查询获取关联数据，自然包含所有供应商。
2. 修正服务子查询的关联表：原服务子查询错误关联了Rel_ProvEquipo，改为Rel_ProvServicio，解决服务数据关联错误的问题。
3. 调整STUFF截断长度：分隔符是; （两个字符），所以将1,1,''改为1,2,''，确保去掉开头多余的分隔符；无关联数据时，STUFF返回NULL，符合需求。
4. 去掉MAX聚合函数：RazonSocial是每个供应商唯一字段，无需用MAX，直接取值即可，同时无需GROUP BY（子查询已按供应商聚合）。

期望输出 #

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