SQL Server不重复关联表如何实现按用户生成对应电话明细JSON

优化方案 #

你原有代码的问题有两个：

1. 生成JSON的子查询没有过滤对应用户ID，导致返回了全量用户的电话数据
2. JSON子查询重复关联了#USERMASTER和#USERPHONE两张表，产生冗余查询

修改后的代码仅需要关联一次两张原始表即可实现预期效果：

BEGIN
DROP TABLE IF EXISTS #USERMASTER;
DROP TABLE IF EXISTS #USERPHONE;
CREATE TABLE #USERMASTER (ID INT, NAME VARCHAR(100));
CREATE TABLE #USERPHONE (ID INT, PHONENUMBER NUMERIC,PHONETYPE CHAR(1));
INSERT INTO #USERMASTER VALUES(1,'JOHN');
INSERT INTO #USERMASTER VALUES(2,'VICTOR');
INSERT INTO #USERPHONE VALUES(1,1356487965,'W');
INSERT INTO #USERPHONE VALUES(1,9841007493,'M');
INSERT INTO #USERPHONE VALUES(1,7255952105,'O');
INSERT INTO #USERPHONE VALUES(2,9874563212,'M');

WITH AllUserPhone AS (
    -- 仅在此处关联一次两张原始表，拿到所有明细数据
    SELECT 
        A.ID,A.NAME,B.PHONENUMBER,B.PHONETYPE,
        ROW_NUMBER() OVER(PARTITION BY A.ID ORDER BY A.ID) RN
    FROM #USERMASTER A 
    JOIN #USERPHONE B ON A.ID=B.ID
),
UserPhoneJson AS (
    -- 基于已关联的结果集聚合生成每个用户对应的专属JSON，无需重复查询原始表
    SELECT 
        ID,
        (SELECT ID,PHONETYPE,PHONENUMBER,NAME 
         FROM AllUserPhone t_inner 
         WHERE t_inner.ID = t_outer.ID 
         FOR JSON PATH) AS UserPhoneDetail_JSON
    FROM AllUserPhone t_outer
    GROUP BY ID
)
SELECT 
    a.ID,a.NAME,a.PHONENUMBER,a.PHONETYPE,
    j.UserPhoneDetail_JSON
FROM AllUserPhone a
INNER JOIN UserPhoneJson j ON a.ID = j.ID
WHERE a.RN = 1;
END

运行上述代码即可得到你需要的预期输出，且全程没有重复关联原始表。

内容的提问来源于stack exchange，提问作者SARAVANAN CHINNU