如何通过UserId关联多表查询返回单条用户数据？

Hey there! Let's tackle this problem together. The issue with your original query is that joining multiple one-to-many tables (like mp_ModuleAccess, mp_PageAccess, and mp_CatalogAccess) directly creates a cartesian product—so if a user has 2 modules and 3 pages, you end up with 2*3=6 duplicate rows for that user. Using SUM or MAX on those fields doesn't work either, since you're trying to preserve all related records, not aggregate numerical values or pick a single max entry.

The Solution: Aggregate One-to-Many Data First #

We need to first aggregate the data from each one-to-many table into a single row per user (using string concatenation or JSON), then join those aggregated results with your one-to-one tables (mp_Users, mp_UserType, mp_SystemAccess). Here are two common approaches depending on your SQL Server version:

Option 1: Using STRING_AGG (SQL Server 2017+)

This function lets you concatenate values from multiple rows into a single comma-separated string, which matches the single-row output you're looking for.

SELECT
    us.userId,
    us.username,
    us.email,
    us.isAdministrator,
    us.status,
    us.FullName,
    ut.userTypeId,
    ut.typeName,
    ut.levelName,
    sys.sysAccessId,
    sys.adminDashboard,
    sys.accessName,
    sys.standardDashboard,
    sys.marginChart,
    sys.expiringChart,
    sys.increasingChart,
    sys.viewCatalogue,
    sys.importList,
    sys.exportList,
    sys.masterDataMain,
    sys.changesNeed,
    -- Aggregated Module Access data
    md.moduleIds,
    md.moduleNames,
    md.moduleUrls,
    -- Aggregated Page Access data
    pg.pageIds,
    pg.pageNames,
    pg.pageUrls,
    pg.pagePermissions,
    pg.pageAccesses,
    -- Aggregated Catalog Access data
    cat.catAccIds,
    cat.hasAccessList
FROM dbo.mp_Users AS us
INNER JOIN dbo.mp_UserType AS ut ON us.userId = ut.userId
INNER JOIN dbo.mp_SystemAccess AS sys ON us.userId = sys.userId
-- Left join to include users with no module access
LEFT JOIN (
    SELECT
        userId,
        STRING_AGG(CAST(moduleId AS VARCHAR(50)), ', ') AS moduleIds,
        STRING_AGG(moduleName, ', ') AS moduleNames,
        STRING_AGG(moduleUrl, ', ') AS moduleUrls
    FROM dbo.mp_ModuleAccess
    GROUP BY userId
) AS md ON us.userId = md.userId
-- Left join to include users with no page access
LEFT JOIN (
    SELECT
        userId,
        STRING_AGG(CAST(pageId AS VARCHAR(50)), ', ') AS pageIds,
        STRING_AGG(pageName, ', ') AS pageNames,
        STRING_AGG(pageUrl, ', ') AS pageUrls,
        STRING_AGG(CAST(pagePermission AS VARCHAR(50)), ', ') AS pagePermissions,
        STRING_AGG(CAST(pageAccess AS VARCHAR(50)), ', ') AS pageAccesses
    FROM dbo.mp_PageAccess
    GROUP BY userId
) AS pg ON us.userId = pg.userId
-- Left join to include users with no catalog access
LEFT JOIN (
    SELECT
        userId,
        STRING_AGG(CAST(catAccId AS VARCHAR(50)), ', ') AS catAccIds,
        STRING_AGG(CAST(HasAccess AS VARCHAR(5)), ', ') AS hasAccessList
    FROM dbo.mp_CatalogAccess
    GROUP BY userId
) AS cat ON us.userId = cat.userId
-- Optional: Filter for a specific user
-- WHERE us.userId = 'your-user-id-here'

Option 2: Using STUFF + FOR XML PATH (SQL Server 2016 or Earlier)

If you're on an older SQL Server version that doesn't support STRING_AGG, use this method to concatenate strings:

SELECT
    us.userId,
    us.username,
    us.email,
    us.isAdministrator,
    us.status,
    us.FullName,
    ut.userTypeId,
    ut.typeName,
    ut.levelName,
    sys.sysAccessId,
    sys.adminDashboard,
    sys.accessName,
    sys.standardDashboard,
    sys.marginChart,
    sys.expiringChart,
    sys.increasingChart,
    sys.viewCatalogue,
    sys.importList,
    sys.exportList,
    sys.masterDataMain,
    sys.changesNeed,
    -- Aggregated Module Access
    md.moduleIds,
    md.moduleNames,
    md.moduleUrls,
    -- Aggregated Page Access
    pg.pageIds,
    pg.pageNames,
    pg.pageUrls,
    pg.pagePermissions,
    pg.pageAccesses,
    -- Aggregated Catalog Access
    cat.catAccIds,
    cat.hasAccessList
FROM dbo.mp_Users AS us
INNER JOIN dbo.mp_UserType AS ut ON us.userId = ut.userId
INNER JOIN dbo.mp_SystemAccess AS sys ON us.userId = sys.userId
LEFT JOIN (
    SELECT
        md1.userId,
        STUFF((
            SELECT ', ' + CAST(moduleId AS VARCHAR(50))
            FROM dbo.mp_ModuleAccess md2
            WHERE md2.userId = md1.userId
            FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS moduleIds,
        STUFF((
            SELECT ', ' + moduleName
            FROM dbo.mp_ModuleAccess md2
            WHERE md2.userId = md1.userId
            FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS moduleNames,
        STUFF((
            SELECT ', ' + moduleUrl
            FROM dbo.mp_ModuleAccess md2
            WHERE md2.userId = md1.userId
            FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS moduleUrls
    FROM dbo.mp_ModuleAccess md1
    GROUP BY md1.userId
) AS md ON us.userId = md.userId
LEFT JOIN (
    SELECT
        pg1.userId,
        STUFF((
            SELECT ', ' + CAST(pageId AS VARCHAR(50))
            FROM dbo.mp_PageAccess pg2
            WHERE pg2.userId = pg1.userId
            FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS pageIds,
        STUFF((
            SELECT ', ' + pageName
            FROM dbo.mp_PageAccess pg2
            WHERE pg2.userId = pg1.userId
            FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS pageNames,
        STUFF((
            SELECT ', ' + pageUrl
            FROM dbo.mp_PageAccess pg2
            WHERE pg2.userId = pg1.userId
            FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS pageUrls,
        STUFF((
            SELECT ', ' + CAST(pagePermission AS VARCHAR(50))
            FROM dbo.mp_PageAccess pg2
            WHERE pg2.userId = pg1.userId
            FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS pagePermissions,
        STUFF((
            SELECT ', ' + CAST(pageAccess AS VARCHAR(50))
            FROM dbo.mp_PageAccess pg2
            WHERE pg2.userId = pg1.userId
            FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS pageAccesses
    FROM dbo.mp_PageAccess pg1
    GROUP BY pg1.userId
) AS pg ON us.userId = pg.userId
LEFT JOIN (
    SELECT
        cat1.userId,
        STUFF((
            SELECT ', ' + CAST(catAccId AS VARCHAR(50))
            FROM dbo.mp_CatalogAccess cat2
            WHERE cat2.userId = cat1.userId
            FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS catAccIds,
        STUFF((
            SELECT ', ' + CAST(HasAccess AS VARCHAR(5))
            FROM dbo.mp_CatalogAccess cat2
            WHERE cat2.userId = cat1.userId
            FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS hasAccessList
    FROM dbo.mp_CatalogAccess cat1
    GROUP BY cat1.userId
) AS cat ON us.userId = cat.userId

Bonus: Structured JSON Output (SQL Server 2022+) #

If you prefer structured data that's easier for front-end apps to parse, use JSON_AGG to return arrays of JSON objects for each one-to-many relationship:

SELECT
    us.userId,
    us.username,
    us.email,
    us.isAdministrator,
    us.status,
    us.FullName,
    ut.userTypeId,
    ut.typeName,
    ut.levelName,
    sys.sysAccessId,
    sys.adminDashboard,
    sys.accessName,
    sys.standardDashboard,
    sys.marginChart,
    sys.expiringChart,
    sys.increasingChart,
    sys.viewCatalogue,
    sys.importList,
    sys.exportList,
    sys.masterDataMain,
    sys.changesNeed,
    md.moduleAccessJson,
    pg.pageAccessJson,
    cat.catalogAccessJson
FROM dbo.mp_Users AS us
INNER JOIN dbo.mp_UserType AS ut ON us.userId = ut.userId
INNER JOIN dbo.mp_SystemAccess AS sys ON us.userId = sys.userId
LEFT JOIN (
    SELECT
        userId,
        JSON_AGG(
            JSON_QUERY(
                '{"moduleId":' + CAST(moduleId AS VARCHAR(50)) + 
                ',"moduleName":"' + REPLACE(moduleName, '"', '\"') + 
                '","moduleUrl":"' + REPLACE(moduleUrl, '"', '\"') + '"}'
            )
        ) AS moduleAccessJson
    FROM dbo.mp_ModuleAccess
    GROUP BY userId
) AS md ON us.userId = md.userId
LEFT JOIN (
    SELECT
        userId,
        JSON_AGG(
            JSON_QUERY(
                '{"pageId":' + CAST(pageId AS VARCHAR(50)) + 
                ',"pageName":"' + REPLACE(pageName, '"', '\"') + 
                '","pageUrl":"' + REPLACE(pageUrl, '"', '\"') + 
                '","pagePermission":' + CAST(pagePermission AS VARCHAR(50)) + 
                ',"pageAccess":' + CAST(pageAccess AS VARCHAR(50)) + '}'
            )
        ) AS pageAccessJson
    FROM dbo.mp_PageAccess
    GROUP BY userId
) AS pg ON us.userId = pg.userId
LEFT JOIN (
    SELECT
        userId,
        JSON_AGG(
            JSON_QUERY(
                '{"catAccId":' + CAST(catAccId AS VARCHAR(50)) + 
                ',"HasAccess":' + CAST(HasAccess AS VARCHAR(5)) + '}'
            )
        ) AS catalogAccessJson
    FROM dbo.mp_CatalogAccess
    GROUP BY userId
) AS cat ON us.userId = cat.userId

Key Benefits of This Approach: #

• No more duplicate rows—each user gets exactly one record
• Preserves all related data from one-to-many tables in a readable format
• Uses LEFT JOIN so users without access to modules/pages/catalogs still appear in results (switch to INNER JOIN if you only want users with all access types)

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