[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
你好,
无论如何,你可以使用 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
[ad_2]
コメント