sql-server – 将列“粘合”在一起的最佳方法

然后我们需要向所有带有数据的表添加行号,我们稍后会用它们加入：

,CTE_Phones
AS
(
    SELECT
        Phones.empid,ROW_NUMBER() OVER (PARTITION BY Phones.empid ORDER BY phone) AS rn,Phones.phone
    FROM @TPhones AS Phones
),CTE_Licenses
AS
(
    SELECT
        Licenses.empid,ROW_NUMBER() OVER (PARTITION BY Licenses.empid ORDER BY license) AS rn,Licenses.license
    FROM @TLicenses AS Licenses
),CTE_Emails
AS
(
    SELECT
        Emails.empid,ROW_NUMBER() OVER (PARTITION BY Emails.empid ORDER BY email) AS rn,Emails.email
    FROM @TEmails AS Emails
)

现在我们准备将所有这些加入到一起. CTE_RowNumbers具有我们需要的确切行数,因此这里不需要复杂的FULL JOIN,简单的LEFT JOIN就足够了：

,CTE_Data
AS
(
    SELECT
        CTE_RowNumbers.empid,CTE_Phones.phone,CTE_Licenses.license,CTE_Emails.email
    FROM
        CTE_RowNumbers
        LEFT JOIN CTE_Phones ON CTE_Phones.empid = CTE_RowNumbers.empid AND CTE_Phones.rn = CTE_RowNumbers.rn
        LEFT JOIN CTE_Licenses ON CTE_Licenses.empid = CTE_RowNumbers.empid AND CTE_Licenses.rn = CTE_RowNumbers.rn
        LEFT JOIN CTE_Emails ON CTE_Emails.empid = CTE_RowNumbers.empid AND CTE_Emails.rn = CTE_RowNumbers.rn
)

我们差不多完成了.我猜,主表有可能没有任何相关数据(没有电话,没有虱子,没有电子邮件),例如我的样本数据中的empid = 4.为了在结果集中获得这些empid,我将把CTE_Data连接到主表：

SELECT
    Main.empid,CTE_Data.phone,CTE_Data.license,CTE_Data.email
FROM
    @TMain AS Main
    LEFT JOIN CTE_Data ON CTE_Data.empid = Main.empid
ORDER BY Main.empid,email;

要获取完整脚本,只需将此帖子中的所有代码块按照此处显示的顺序放在一起.

这是结果集：

empid   phone   license email
1   5551234 NULL    NULL
2   5551111 L4455   xxx@abc
2   5552222 NULL    yyy@abc
2   5553333 NULL    zzz@abc
2   5554444 NULL    NULL
3   NULL    L7890   NULL
4   NULL    NULL    NULL

（编辑：晋中站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!