SQL - 在一张表中联合所有用户 [英] SQL - Union All Users in One Table

问题描述

表:流行

<前>用户名 朋友名————————————约翰·莎拉菲利普·乌苏拉约翰·玛丽约翰杰里米菲利普·布洛克汗莱米

我想要带查询的列表；

<前>约翰·菲利普·汗——————————————————莎拉·乌苏拉·莱米嫁给布洛克 -NULL-杰里米 -NULL- -NULL-

我有 100 多个用户名...帮助我使用 SQL 查询 (MSSQL) 列出

解决方案

如果您有100+ 个用户名"，您会希望这是动态的，这样您就不必为每个用户名键入特定的 CASE 语句.

此外，每次向表中添加新用户名时，您都不想更新脚本.

以下脚本将动态检索所有不同的用户名，并为他们创建一列，其中包含所有朋友的行.

 DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX);SET @cols = STUFF((SELECT distinct ',MAX(CASE WHEN UserName = '''+ p.UserName + ''' THEN FriendName END) AS '+ QUOTENAME(p.UserName) FROM 热门 pFOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')SET @query = 'SELECT' + @cols + ' FROM(选择用户名，朋友名,ROW_NUMBER() OVER (PARTITION BY UserName ORDER BY FriendName) AS RowNum来自按用户名、朋友名的热门群组) XGROUP BY RowNum'执行(@查询)；

我上面的输出如下所示；

╔========╦======╦========╗║ 约翰 ║ 汗 ║ 菲利普 ║╠========╬======╬========╣║ 杰里米 ║ 莱米 ║ 布洛克 ║║ 结婚 ║ NULL ║ 乌苏拉 ║║ 莎拉 ║ NULL ║ NULL ║╚========╩======╩========╝

您应该能够对整个表运行此程序并获得所有可能的用户名的结果，而无需键入单独的 CASE 语句.

对于任何想要测试的人，这里是测试表和数据脚本；

 IF EXISTS ( SELECT *来自 INFORMATION_SCHEMA.TABLESWHERE TABLE_NAME = '流行'AND TABLE_SCHEMA = 'dbo'AND TABLE_TYPE = 'TABLE')删除表 [dbo].[流行];走创建表 [dbo].[流行](用户名 VARCHAR(20),朋友名 VARCHAR(20));走INSERT INTO [dbo].[Popular] (UserName,FriendName) VALUES('约翰','莎拉'),('菲利普','乌苏拉'),('约翰','玛丽'),('约翰','杰里米'),('菲利普','布洛克'),('汗','莱米');

Table : Popular

UserName   FriendName
--------   ---------- 
John       Sarah
Philip     Ursula
John       Marry
John       Jeremy
Philip     Brock
Khan       Lemy

And I want list with query;

John       Philip       Khan
--------   ----------   --------
Sarah      Ursula       Lemy
Marry      Brock        -NULL-
Jeremy     -NULL-       -NULL-

I have 100+ Username... help me for to list with SQL Query (MSSQL)

解决方案

If you have "100+ UserNames" you will want this to be DYNAMIC so that you don't have to type out specific CASE statements for each UserName.

Also you won't want to have to update your script every time a new UserName is added to your table.

The below script will dynamically retrieve all distinct UserNames and create a column for them with rows for all their friends.

    DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX);

    SET @cols = STUFF((SELECT distinct ',MAX(CASE WHEN UserName = ''' 
                        + p.UserName + ''' THEN FriendName END) AS ' 
                        + QUOTENAME(p.UserName) FROM Popular p
                FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')

    SET @query = 'SELECT ' + @cols + ' FROM 
                (SELECT UserName, FriendName
                    ,ROW_NUMBER() OVER (PARTITION BY UserName ORDER BY FriendName) AS RowNum
                    FROM Popular GROUP BY USERNAME, FRIENDNAME
                ) x
                GROUP BY RowNum'

    EXECUTE(@query);

My output from the above shows as the below;

╔════════╦══════╦════════╗
║  John  ║ Khan ║ Philip ║
╠════════╬══════╬════════╣
║ Jeremy ║ Lemy ║ Brock  ║
║ Marry  ║ NULL ║ Ursula ║
║ Sarah  ║ NULL ║ NULL   ║
╚════════╩══════╩════════╝

You should be able to run this against entire table and get results for all possible UserNames without having to type out individual CASE Statements.

For anyone wanting to test this, here is the test table and data script;

    IF EXISTS ( SELECT *
                FROM INFORMATION_SCHEMA.TABLES
                WHERE TABLE_NAME = 'Popular'
                AND TABLE_SCHEMA = 'dbo'
                AND TABLE_TYPE = 'TABLE')
    DROP TABLE [dbo].[Popular];
    GO

    CREATE TABLE [dbo].[Popular]
    (
    UserName VARCHAR(20),
    FriendName VARCHAR(20)
    );
    GO

    INSERT INTO [dbo].[Popular] (UserName,FriendName) VALUES
    ('John','Sarah'),
    ('Philip','Ursula'),
    ('John','Marry'),
    ('John','Jeremy'),
    ('Philip','Brock'),
    ('Khan','Lemy');

这篇关于SQL - 在一张表中联合所有用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！