問題描述

表:流行

我想要帶查詢的列表；

我有 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 - 在一張表中聯合所有用戶的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！