sql-server - 获取数据并在同一张表上使用 subselect

Question

我有一个这样的表:

+--------------+--------------+--------------+
| userid       | username     | proxy        |
+--------------+--------------+--------------+
| 1            | j.doe        |              |
| 2            | lechnerio    | 1,4          |
| 3            | janedoe      | 1            |
| 4            | mustermann   | 2            |
+--------------+--------------+--------------+

代理可以是NULL，一个或多个来自其他用户的ID。

我想构建一个有助于可视化用户的 View 。我想到了这样一个类似的结果:

+--------------+--------------+--------------+-----------------------------+
| userid       | username     | proxy        | proxy_info                  |
+--------------+--------------+--------------+-----------------------------+
| 1            | j.doe        |              |                             |
| 2            | lechnerio    | 1,4          | j.doe (1), mustermann (4)   |
| 3            | janedoe      | 1            | j.doe (1)                   |
| 4            | mustermann   | 2            | lechnerio (2)               |
+--------------+--------------+--------------+-----------------------------+

我无法全神贯注于 proxy_info 所需的子选择。表本身不止这三列，但在本例中这无关紧要。将 proxy_info 与用户名和括号中的 id 结合起来也不是问题。但是我无法选择用户 ID 匹配的值。

对于实现上述结果的任何技巧和提示，我都会很高兴。

我考虑过将表与自身连接或使用联合。但是对于想要的结果来说，这两种选择似乎都过于复杂了。我在这里使用 SQL Server。

作为一个想法:

SELECT a.agentcode
    ,a.username
    ,a.proxy
    ,(
        SELECT b.agentcode
        FROM app_users b
        WHERE a.agentcode = b.proxy
        ) AS proxy_info
FROM app_users a

Answer 1

试试这个:

DROP TABLE IF EXISTS [dbo].[test_Users];
GO

CREATE TABLE [dbo].[test_Users]
(
    [userid] INT
   ,[username] VARCHAR(18)
   ,[proxy] VARCHAR(12)
);

GO

INSERT INTO [dbo].[test_Users] ([userid], [username], [proxy])
VALUES (1, 'j.doe',  NULL)
      ,(2, 'janedoe',  '1,4')
      ,(3, 'janedoe',  '1')
      ,(4, 'mustermann',  '2');

GO

SELECT U.[userid]
      ,U.[username]
      ,U.[proxy]
      ,NULLIF(STRING_AGG(CONCAT(P.[username], '(', p.[userid] ,')'), ', '), '()')
FROM [dbo].[test_Users] U
OUTER APPLY STRING_SPLIT (U.[proxy], ',') S
LEFT JOIN [dbo].[test_Users] P
    ON S.[value] = P.[userid]
GROUP BY U.[userid]
        ,U.[username]
        ,U.[proxy];