none
如何在创建视图时根据自同一个表的5个不同id合并对应Name为一列 RRS feed

  • 问题

  • 大家好。

    一个Role表,有RoleId(int型)和RoleName两列

    还有一个User表,有5个不同的RoleId(每个User至多有5个不同的Role,均为int型),分别为RoleIdOne,RoleIdTwo,RoleIdThree,RoleIdFour和RoleIdFive。其中RoleIdOne不为空,是主要Role,其它4个可以为空

    我想创建一个视图,包含这5个RoleId,然后有一个RoleName列可以合并这5个RoleId对应的RoleName,RoleId为空时不合并。

    目前我只能默认与RoleIdOne对应的RoleName,代码如下:

    --判断用户自定义视图是否存在
    if exists(select * from sysobjects where id=OBJECT_ID(N'V_USER') and objectproperty(id,N'IsView')=1)
    drop view V_USER  
    go
    
    create view dbo.V_USER
    as
    select u.UserId,u.LoginName,u.RealName,u.RoleIdOne,u.RoleIdTwo,u.RoleIdThree,u.RoleIdFour,u.RoleIdFive,
    r.RoleName,r.RoleId
    from SYS_USER u --User表
    	left join SYS_ROLE r on u.RoleIdOne=r.RoleId	--User表的RoleIdOne是主要的Role
    go
    
    
    select * from V_USER        --使用视图查询

    恳请各位帮帮忙,谢谢。

    2020年3月27日 9:53

答案

  • 你好,

    你可以用FOR XML PATH达到这个效果,用法你可以参考这个文章:SQL Server FOR XML PATH 和 STUFF函数的用法

    查询语法修改如下:

    with cte1 as(
    select UserId,RealName,RoleIdOne as RoleId
    from [User]
    union all
    select UserId,RealName,RoleIdTwo as RoleId
    from [User]
    union all
    select UserId,RealName,RoleIdThree as RoleId
    from [User]
    union all
    select UserId,RealName,RoleIdFour as RoleId
    from [User]
    union all
    select UserId,RealName,RoleIdFive as RoleId
    from [User]
    ),
    cte2 as(
    select c.UserId,c.RealName,c.RoleId,r.RoleName
    from cte1 c join Role r on c.RoleId=r.RoleId),
    cte3 as(
    select Userid,(select RoleName+',' from cte2 where Userid=c.Userid FOR XML PATH('')) AS RoleName
    from cte2 c 
    group by Userid)
    select b.Userid,a.RealName,a.RoleIdOne,a.RoleIdTwo,a.RoleIdThree,a.RoleIdFour,a.RoleIdFive,b.RoleName from [User] a left outer join cte3 b on a.userid=b.Userid

    此外,SQL Server 2008 R2太过老旧,微软早已不再支持,为了防止使用过程中遇见任何问题,建议你下载安装最新的SQL Server 2019

    希望对你有用。 请帮忙标记为答案,以帮助其他社区成员迅速找到有用的答复。谢谢


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    2020年4月2日 2:35

全部回复

  • 你好,

    能否提供一下你的表格的代码,你想要达成什么样的效果?

    例如:

    这样才比较清楚你需要什么, 谢谢


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2020年3月30日 8:45
  • 你好,Young,多谢回复。

    Role表实例如下:

    User表实例如下


    其中的RoleIdOne至RoleIdFive对应至多的5个角色,默认值是-1,表示没有。

    需要得到的视图结果如下:


    即需要新增一列“RoleName",把对应的角色加起来,有几个角色就加几个,用逗号分隔。-1对应的角色表示没有,不能加进去。



    2020年3月31日 2:07
  • 你好,

    你可以尝试以下语句:

    Create view dbo.User_view
    as
    with cte1 as(
    select UserId,RealName,RoleIdOne as RoleId
    from [User]
    union all
    select UserId,RealName,RoleIdTwo as RoleId
    from [User]
    union all
    select UserId,RealName,RoleIdThree as RoleId
    from [User]
    union all
    select UserId,RealName,RoleIdFour as RoleId
    from [User]
    union all
    select UserId,RealName,RoleIdFive as RoleId
    from [User]
    ),
    cte2 as(
    select c.UserId,c.RealName,c.RoleId,r.RoleName
    from cte1 c join Role r on c.RoleId=r.RoleId),
    cte3 as(
    select Userid,STRING_AGG(RoleName,',') as RoleName
    from cte2 
    group by Userid)
    select b.Userid,a.RealName,a.RoleIdOne,a.RoleIdTwo,a.RoleIdThree,a.RoleIdFour,a.RoleIdFive,b.RoleName from [User] a left outer join cte3 b on a.userid=b.Userid

    select * from dbo.User_view

    结果如下:

    希望对你有用。还有任何问题请随时告知,谢谢


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2020年3月31日 8:49
  • 你好,Young。

    多谢你的代码,麻烦问下,你写的那个函数STRING_AGG(RoleName,',')能不能提供一下,目前就这一个错误。再次感谢。

    2020年4月1日 1:08
  • 你好,

    请问什么错误?你可以参考官方的STRING_AGG用法,请注意这个T-SQL只适用于sql server 2017之后。

    另外,请帮忙标记为答案,以帮助其他社区成员迅速找到有用的答复。谢谢


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    2020年4月1日 5:25
  • 你好,Young,非常感谢你的回复。

    我用的时SQL Server 2008 R2,没有这个STRING_AGG函数,就是报这个错误。这个如何自定义一个类似的函数?

    2020年4月2日 1:28
  • 你好,

    你可以用FOR XML PATH达到这个效果,用法你可以参考这个文章:SQL Server FOR XML PATH 和 STUFF函数的用法

    查询语法修改如下:

    with cte1 as(
    select UserId,RealName,RoleIdOne as RoleId
    from [User]
    union all
    select UserId,RealName,RoleIdTwo as RoleId
    from [User]
    union all
    select UserId,RealName,RoleIdThree as RoleId
    from [User]
    union all
    select UserId,RealName,RoleIdFour as RoleId
    from [User]
    union all
    select UserId,RealName,RoleIdFive as RoleId
    from [User]
    ),
    cte2 as(
    select c.UserId,c.RealName,c.RoleId,r.RoleName
    from cte1 c join Role r on c.RoleId=r.RoleId),
    cte3 as(
    select Userid,(select RoleName+',' from cte2 where Userid=c.Userid FOR XML PATH('')) AS RoleName
    from cte2 c 
    group by Userid)
    select b.Userid,a.RealName,a.RoleIdOne,a.RoleIdTwo,a.RoleIdThree,a.RoleIdFour,a.RoleIdFive,b.RoleName from [User] a left outer join cte3 b on a.userid=b.Userid

    此外,SQL Server 2008 R2太过老旧,微软早已不再支持,为了防止使用过程中遇见任何问题,建议你下载安装最新的SQL Server 2019

    希望对你有用。 请帮忙标记为答案,以帮助其他社区成员迅速找到有用的答复。谢谢


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    2020年4月2日 2:35