如何将SQL查询返回的同一资产多行结果聚合为单条记录？

测试数据库结构与数据 #

-- Computer assets
CREATE TABLE tblAssets 
(
    AssetID int NOT NULL,
    AssetName nvarchar(200) NOT NULL,
    UserName nvarchar(150),
    PRIMARY KEY (AssetID)
);

-- Software titles
CREATE TABLE tblSoftwareUNI 
(
    SoftID int NOT NULL,
    softwareName nvarchar(300),
    PRIMARY KEY (SoftID)
);

-- Software installed on computer assets
CREATE TABLE tblSoftware 
(
    SoftwareID int NOT NULL,
    AssetID int NOT NULL,
    SoftID int NOT NULL,
    SoftwareVersion nvarchar(100),
    PRIMARY KEY (SoftwareID),
    CONSTRAINT FK_tblSoftware_tblAssets 
        FOREIGN KEY (AssetID)
        REFERENCES tblAssets(AssetID),
    CONSTRAINT FK_tblSoftware_tblSoftwareUNI 
        FOREIGN KEY (SoftID)
        REFERENCES tblSoftwareUNI(SoftID)
);

-- File existence on asset
CREATE TABLE tblFileVersions 
(
    VersionID int NOT NULL,
    AssetID int NOT NULL,
    FilePathfull nvarchar(1000),
    Found bit,
    PRIMARY KEY (VersionID),
    CONSTRAINT FK_tblFileVersions_tblAssets 
        FOREIGN KEY (AssetID)
        REFERENCES tblAssets(AssetID)  
);

INSERT INTO tblSoftwareUNI (SoftID, softwareName) VALUES (1, 'Adobe Acrobat');
INSERT INTO tblSoftwareUNI (SoftID, softwareName) VALUES (2, 'Microsoft Word');

INSERT INTO tblAssets (AssetID, AssetName, UserName) VALUES (1, 'PC1', 'Vito Corleone');
INSERT INTO tblAssets (AssetID, AssetName, UserName) VALUES (2, 'PC2', 'Jack Sparrow');
INSERT INTO tblAssets (AssetID, AssetName, UserName) VALUES (3, 'PC3', 'Han Solo');
INSERT INTO tblAssets (AssetID, AssetName, UserName) VALUES (4, 'PC4', 'Anakin Skywalker');
INSERT INTO tblAssets (AssetID, AssetName, UserName) VALUES (5, 'PC5', 'Harry Potter');

INSERT INTO tblSoftware (SoftwareID, AssetID, SoftID, SoftwareVersion) VALUES (1, 1, 1, '10.9');
INSERT INTO tblSoftware (SoftwareID, AssetID, SoftID, SoftwareVersion) VALUES (2, 2, 1, '11.0');
INSERT INTO tblSoftware (SoftwareID, AssetID, SoftID, SoftwareVersion) VALUES (3, 2, 2, '1.42');
INSERT INTO tblSoftware (SoftwareID, AssetID, SoftID, SoftwareVersion) VALUES (4, 2, 2, '2.57');
INSERT INTO tblSoftware (SoftwareID, AssetID, SoftID, SoftwareVersion) VALUES (5, 3, 1, '11.0');
INSERT INTO tblSoftware (SoftwareID, AssetID, SoftID, SoftwareVersion) VALUES (6, 3, 2, '2.57');
INSERT INTO tblSoftware (SoftwareID, AssetID, SoftID, SoftwareVersion) VALUES (7, 4, 1, '10.9');
INSERT INTO tblSoftware (SoftwareID, AssetID, SoftID, SoftwareVersion) VALUES (8, 4, 2, '1.40.9');
INSERT INTO tblSoftware (SoftwareID, AssetID, SoftID, SoftwareVersion) VALUES (9, 5, 1, '11.0');
INSERT INTO tblSoftware (SoftwareID, AssetID, SoftID, SoftwareVersion) VALUES (10, 5, 2, '1.40.9');
INSERT INTO tblSoftware (SoftwareID, AssetID, SoftID, SoftwareVersion) VALUES (11, 5, 2, '2.57');

INSERT INTO tblFileVersions (VersionID, AssetID, FilePathfull, Found) VALUES (1, 1, 'c:\temp\temp.txt', 0);
INSERT INTO tblFileVersions (VersionID, AssetID, FilePathfull, Found) VALUES (2, 1, 'c:\test\testfile.txt', 0);
INSERT INTO tblFileVersions (VersionID, AssetID, FilePathfull, Found) VALUES (3, 2, 'c:\temp\temp.txt', 1);
INSERT INTO tblFileVersions (VersionID, AssetID, FilePathfull, Found) VALUES (4, 2, 'c:\test\testfile.txt', 1);
INSERT INTO tblFileVersions (VersionID, AssetID, FilePathfull, Found) VALUES (5, 3, 'c:\temp\temp.txt', 0);
INSERT INTO tblFileVersions (VersionID, AssetID, FilePathfull, Found) VALUES (6, 3, 'c:\test\testfile.txt', 1);
INSERT INTO tblFileVersions (VersionID, AssetID, FilePathfull, Found) VALUES (7, 4, 'c:\temp\temp.txt', 1);
INSERT INTO tblFileVersions (VersionID, AssetID, FilePathfull, Found) VALUES (8, 4, 'c:\test\testfile.txt', 0);
INSERT INTO tblFileVersions (VersionID, AssetID, FilePathfull, Found) VALUES (9, 5, 'c:\temp\temp.txt', 1);
INSERT INTO tblFileVersions (VersionID, AssetID, FilePathfull, Found) VALUES (10, 5, 'c:\test\testfile.txt', 1);

原查询语句 #

SELECT
    tblAssets.AssetName,
    tblAssets.UserName,
    CASE LEFT(tblSoftware.SoftwareVersion, 1)
        WHEN '1' THEN tblSoftware.SoftwareVersion
        ELSE ''
    END AS v1,
    CASE LEFT(tblSoftware.SoftwareVersion, 1)
        WHEN '2' THEN tblSoftware.SoftwareVersion
        ELSE ''
    END AS v2,
    (SELECT TOP 1 tblFileVersions.Found 
     FROM tblFileVersions 
     WHERE tblFileVersions.FilePathFull = 'c:\test\testfile.txt' 
       AND tblFileVersions.AssetID = tblSoftware.AssetID) FileFound
FROM 
    tblSoftware
INNER JOIN 
    tblAssets ON tblSoftware.AssetID = tblAssets.AssetID
INNER JOIN 
    tblSoftwareUNI ON tblSoftwareUni.SoftID = tblSoftware.softID
WHERE
    tblSoftwareUni.softwareName LIKE 'Microsoft%'

原查询结果 #

期望结果 #

解决方案 #

要实现每个资产仅显示一条记录，需按资产维度分组，使用聚合函数合并同一资产的v1、v2值。由于每个资产的v1和v2各自最多只有一个非空值，使用MAX()函数可提取对应版本号，空字符串会被自动忽略。

修改后的查询语句如下：

SELECT
    tblAssets.AssetName,
    tblAssets.UserName,
    MAX(CASE LEFT(tblSoftware.SoftwareVersion, 1)
        WHEN '1' THEN tblSoftware.SoftwareVersion
        ELSE ''
    END) AS v1,
    MAX(CASE LEFT(tblSoftware.SoftwareVersion, 1)
        WHEN '2' THEN tblSoftware.SoftwareVersion
        ELSE ''
    END) AS v2,
    MAX((SELECT TOP 1 tblFileVersions.Found 
         FROM tblFileVersions 
         WHERE tblFileVersions.FilePathFull = 'c:\test\testfile.txt' 
           AND tblFileVersions.AssetID = tblSoftware.AssetID)) AS FileFound
FROM 
    tblSoftware
INNER JOIN 
    tblAssets ON tblSoftware.AssetID = tblAssets.AssetID
INNER JOIN 
    tblSoftwareUNI ON tblSoftwareUni.SoftID = tblSoftware.softID
WHERE
    tblSoftwareUni.softwareName LIKE 'Microsoft%'
GROUP BY tblAssets.AssetName, tblAssets.UserName

核心逻辑说明 #

1. 分组控制：通过GROUP BY tblAssets.AssetName, tblAssets.UserName确保每个资产仅生成一条记录；
2. 版本聚合：用MAX()包裹原CASE语句，自动筛选出每个资产对应的v1、v2非空版本值；
3. 文件状态处理：用MAX()包裹子查询，因同一资产的FileFound值唯一，聚合后结果不受影响，也可将子查询移至JOIN环节减少重复计算。

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