时间戳

首页 > 解决方案 > 从具有公共 UserId 键的许多表的 SQL 查询中返回单行

sql - 从具有公共 UserId 键的许多表的 SQL 查询中返回单行

问题描述

从具有多个具有公共 UserId 键的表的 SQL 查询中返回单行的更好方法是什么？我也提到了LINK，但结果不是我想要的。

此外，他们是许多用户，分配有许多 ModuleAccess、许多 PageAccess、许多 catalogAccess、只有 1 个 UserType 和只有 1 个 SystemAccess。用户表有很多用户。

我试过这个但是没有用：

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.moduleId, md.moduleName, md.moduleUrl, 
                         pg.pageId, pg.pageName, pg.pageUrl, pg.pagePermission, pg.pageAccess,cat.catAccId, cat.HasAccess
FROM            dbo.mp_Users AS us 
                         INNER JOIN dbo.mp_UserType AS ut ON us.userId = ut.userId 
                         INNER JOIN dbo.mp_PageAccess AS pg ON us.userId = pg.userId 
                         INNER JOIN dbo.mp_ModuleAccess AS md ON us.userId = md.userId 
                         INNER JOIN dbo.mp_SystemAccess AS sys ON us.userId = sys.userId 
                         INNER JOIN dbo.mp_CatalogAccess AS cat ON us.userId = cat.userId

我想要的是链接这个： 在此处输入图像描述

这是我当前的结果查询： 在此处输入图像描述

任何人都有更好的方法来查询以上 SQL 吗？谢谢

_________________经过几次尝试_______________查询错误

SELECT        
    dbo.mp_Users.userId,     dbo.mp_Users.username,     dbo.mp_Users.email,     dbo.mp_Users.isAdministrator,     dbo.mp_Users.status,     dbo.mp_Users.FullName,     dbo.mp_UserType.userTypeId, 
    dbo.mp_UserType.typeName,     dbo.mp_UserType.levelName,     dbo.mp_SystemAccess.sysAccessId,     dbo.mp_SystemAccess.adminDashboard,     dbo.mp_SystemAccess.accessName,     dbo.mp_SystemAccess.standardDashboard, 
    dbo.mp_SystemAccess.marginChart,     dbo.mp_SystemAccess.expiringChart,     dbo.mp_SystemAccess.increasingChart,     dbo.mp_SystemAccess.viewCatalogue,     dbo.mp_SystemAccess.importList, 
    dbo.mp_SystemAccess.exportList,     dbo.mp_SystemAccess.masterDataMain,     dbo.mp_SystemAccess.changesNeed,     
    sum(dbo.mp_ModuleAccess.moduleId), 
    max(dbo.mp_ModuleAccess.moduleName), 
    max(dbo.mp_ModuleAccess.moduleUrl), 
    sum(dbo.mp_PageAccess.pageId), 
    max(dbo.mp_PageAccess.pageName), 
    max(dbo.mp_PageAccess.pageUrl), 
    max(dbo.mp_PageAccess.pagePermission), 
    max(dbo.mp_PageAccess.pageAccess),
    sum(dbo.mp_CatalogAccess.catAccId), 
    max(dbo.mp_CatalogAccess.HasAccess)
FROM dbo.mp_Users 
 INNER JOIN dbo.mp_UserType  ON dbo.mp_Users.userId = dbo.mp_UserType.userId 
 INNER JOIN dbo.mp_PageAccess ON dbo.mp_Users.userId = dbo.mp_PageAccess.userId 
 INNER JOIN dbo.mp_ModuleAccess ON dbo.mp_Users.userId = dbo.mp_ModuleAccess.userId 
 INNER JOIN dbo.mp_SystemAccess ON dbo.mp_Users.userId = dbo.mp_SystemAccess.userId 
 INNER JOIN dbo.mp_CatalogAccess ON dbo.mp_Users.userId = dbo.mp_CatalogAccess.userId
GROUP BY dbo.mp_Users.userId

标签： sqlsql-server

解决方案

你可以加

group by userId

在代码的末尾，然后在选择部分，对于除 userID 之外的所有字段，您必须使用

max(fieldname)

而不是字段名，例如

max(us.username), max(us.email)

不过要小心。我提出 max() 是因为我看到对于每个用户 ID，您的字段的多个值具有相同的值 - 但是，您必须确保是这种情况。如果某个字段有多个值，您将必须确定我们应该以何种方式选择其中一个以显示在 SELECT 部分中。

以下是所有带有 max() 的字段，请谨慎使用：

SELECT        
    us.userId, 
    max(us.username), 
    max(us.email), 
    max(us.isAdministrator), 
    max(us.status), 
    max(us.FullName), 
    max(ut.userTypeId), 
    max(ut.typeName), 
    max(ut.levelName), 
    max(sys.sysAccessId), 
    max(sys.adminDashboard), 
    max(sys.accessName), 
    max(sys.standardDashboard), 
    max(ys.marginChart), 
    max(sys.expiringChart), 
    max(sys.increasingChart), 
    max(sys.viewCatalogue), 
    max(sys.importList), 
    max(sys.exportList), 
    max(sys.masterDataMain), 
    max(sys.changesNeed), 
    max(md.moduleId), 
    max(md.moduleName), 
    max(md.moduleUrl), 
    max(pg.pageId), 
    max(pg.pageName), 
    max(pg.pageUrl), 
    max(pg.pagePermission), 
    max(pg.pageAccess),
    max(cat.catAccId), 
    max(cat.HasAccess)
FROM            dbo.mp_Users AS us 
 INNER JOIN dbo.mp_UserType AS ut ON us.userId = ut.userId 
 INNER JOIN dbo.mp_PageAccess AS pg ON us.userId = pg.userId 
 INNER JOIN dbo.mp_ModuleAccess AS md ON us.userId = md.userId 
 INNER JOIN dbo.mp_SystemAccess AS sys ON us.userId = sys.userId 
 INNER JOIN dbo.mp_CatalogAccess AS cat ON us.userId = cat.userId
GROUP BY us.userId

推荐阅读