根据提供的表结构，我需要以下 SQL 查询：

[ad_1]

USER TABLE
==========
USER_ID     USER_NAME
1150080     XXXXXXXXXXXX  
1150081     YYYYYYYYYYYY 
1150082     ZZZZZZZZZZZZ 
1150083     RRRRRRRRRRRR
1150084     WWWWWWWWWWWW
1150085     TTTTTTTTTTTT
1150086     EEEEEEEEEEEE
1150087     QQQQQQQQQQQQ



ROLE TABLE
============
ROLE_ID   ROLE_NAME
1         AAAAAAAAA
2         CCCCCCCCC 
3         RRRRRRRRR
4         TTTTTTTTT
5         UUUUUUUUU
6         YYYYYYYYY
7         IIIIIIIII


USERROLE TABLE
===============
USER_ID    ROLE_ID
1150080    1
1150080    2
1150081    1
1150083    1
1150083    7


REQURIED RESAULT

USER_ID      USER_NAME                   ROLE_ID

                                1    2    3   4  5  6   7 
1150080     XXXXXXXXXXXX        ✓    ✓    *   *  *  *   *
1150081     YYYYYYYYYYYY        *    ✓    *   *  *  *   *
1150082     ZZZZZZZZZZZZ        *    *    *   *  *  *   * 
1150083     RRRRRRRRRRRR        ✓    *    *   *  *  *   ✓ 
1150084     WWWWWWWWWWWW        *    *    *   *  *  *   *
1150085     TTTTTTTTTTTT        *    *    *   *  *  *   *
1150086     EEEEEEEEEEEE        *    *    *   *  *  *   *
1150087     QQQQQQQQQQQQ        *    *    *   *  *  *   *

我尝试过的：

select SUSER.USER_ID,SUSER.USER_NAME,SUSERROLE.ROLE_ID,SROLEDETAIL.PRODUCT_ID,SPRODUCT.PRODUCT_NAME,SROLEDETAIL.FUNCTION_ID,SFUNCTION.FUNCTION_NAME from SUSER
full join SUSERROLE on SUSER.USER_ID = SUSERROLE.USER_ID
full join SROLEDETAIL on SUSERROLE.ROLE_ID =SROLEDETAIL.ROLE_ID
full join SFUNCTION on SROLEDETAIL.FUNCTION_ID= SFUNCTION.FUNCTION_ID
FULL JOIN SPRODUCT ON SROLEDETAIL.PRODUCT_ID =SPRODUCT.PRODUCT_ID

解决方案1
解决方案2

解决方案1

你好，

无论如何，你可以使用 PIVOT 来实现这一点。如果 ROLE_ID 有限，您可以在查询中使用静态值。但如果不限制，那么您可能需要动态生成 PIVOT 查询并执行它。

顺便说一句，你的结果有点不对劲。例如：用户“1150081”没有 ROLE_ID“2”，而应该是“1”。我猜这是一个错字。

SQL

;WITH SourceQuery AS (
SELECT 
	UT.[USER_ID]
	,[UT].[USER_NAME]
	,[ROLE_ID]
FROM
	USER_TABLE AS [UT]
	LEFT JOIN USERROLE_TABLE AS [UR]
		ON UT.[USER_ID] = [UR].[USER_ID]
)
SELECT
	[USER_ID]
	,[USER_NAME]
	,IIF([1] IS NULL, N'',N'Y') AS [1]
	,IIF([2] IS NULL, N'',N'Y') AS [2]
	,IIF([3] IS NULL, N'',N'Y') AS [3]
	,IIF([4] IS NULL, N'',N'Y') AS [4]
	,IIF([5] IS NULL, N'',N'Y') AS [5]
	,IIF([6] IS NULL, N'',N'Y') AS [6]
	,IIF([7] IS NULL, N'',N'Y') AS [7]
FROM	
	SourceQuery AS Src
PIVOT (
	MAX(ROLE_ID) 
	FOR ROLE_ID
	IN (
		[1],[2],[3],[4],[5],[6],[7]
	)
) AS PVT
ORDER BY
	[USER_ID]

我使用“Y”和“”而不是勾号和星号，因为它不会在解决方案窗格中正确显示。

希望它能帮助你。

解决方案2

要生成具有预定义用户角色的矩阵，您可以通过 SELECT 语句和条件逻辑使用以下 SQL 查询。 MAX 函数用于合并 GROUP BY 子句中每个角色的信息。在这种情况下，“Y”表示复选标记，而“N”表示星号。”

SQL

SELECT
    u.USER_ID,
    u.USER_NAME,
    MAX(CASE WHEN ur.ROLE_ID = 1 THEN 'Y' ELSE 'N' END) AS [1],
    MAX(CASE WHEN ur.ROLE_ID = 2 THEN 'Y' ELSE 'N' END) AS [2],
    MAX(CASE WHEN ur.ROLE_ID = 3 THEN 'Y' ELSE 'N' END) AS [3],
    MAX(CASE WHEN ur.ROLE_ID = 4 THEN 'Y' ELSE 'N' END) AS [4],
    MAX(CASE WHEN ur.ROLE_ID = 5 THEN 'Y' ELSE 'N' END) AS [5],
    MAX(CASE WHEN ur.ROLE_ID = 6 THEN 'Y' ELSE 'N' END) AS [6],
    MAX(CASE WHEN ur.ROLE_ID = 7 THEN 'Y' ELSE 'N' END) AS [7]
FROM [USER] u
	LEFT JOIN [USERROLE] ur ON u.USER_ID = ur.USER_ID
GROUP BY u.USER_ID, u.USER_NAME
ORDER BY u.USER_ID