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

我需要在一个大表中组合大约15个表的列.以下内容有效.但是,当CPU使用率达到100％时,运行需要很长时间,这会引起担忧.任何建议将受到高度赞赏.

declare @t1 table (empid int)
declare @t2 table (empid int,phone varchar(50))
declare @t3 table (empid int,license varchar(50))
declare @t4 table (empid int,email varchar(100))

insert into @t1 values (1)
insert into @t1 values (2)
insert into @t1 values (3)
insert into @t2 values (1,'5551234')
insert into @t2 values (2,'5553333')
insert into @t2 values (2,'ttt2222')
insert into @t3 values (2,'L4455')
insert into @t3 values (3,'L7890')
insert into @t4 values (2,'xxx@abc')

SELECT t1.empid,t2.phone,t3.license,t4.email
FROM
    @t1 t1
    LEFT OUTER JOIN
    (SELECT empid,phone,row_number() over (partition by empid order by phone) as rn 
    FROM @t2) t2 
    ON t2.empid = t1.empid
    FULL OUTER JOIN
    (SELECT empid,license,row_number() over (partition by empid order by license) as rn 
    FROM @t3) t3 
    ON t3.empid=t1.empid and (t2.rn is null or t3.rn = t2.rn)
    FULL OUTER JOIN
    (SELECT empid,email,row_number() over (partition by empid order by email) as rn 
    FROM @t4) t4
    ON t4.empid=t1.empid and t4.rn=coalesce(t2.rn,t3.rn) --image how long this coalesce clause is going to be for the 15th table?
order by t1.empid,t2.rn

解决方法

我将为您的示例中的表提供更有意义的名称,并添加更多行以突出显示该问题.在现实生活中,这些表将是真正的表,当然,不是变量,但我会坚持使用变量来使这个示例脚本易于运行和尝试.我在这个例子中使用SQL Server 2008.

declare @TMain table (empid int);
declare @TPhones table (empid int,phone varchar(50));
declare @TLicenses table (empid int,license varchar(50));
declare @TEmails table (empid int,email varchar(100));

insert into @TMain values (1);
insert into @TMain values (2);
insert into @TMain values (3);
insert into @TMain values (4);

insert into @TPhones values (1,'5551234');
insert into @TPhones values (2,'5551111');
insert into @TPhones values (2,'5552222');
insert into @TPhones values (2,'5553333');
insert into @TPhones values (2,'5554444');

insert into @TLicenses values (2,'L4455');
insert into @TLicenses values (3,'L7890');

insert into @TEmails values (2,'xxx@abc');
insert into @TEmails values (2,'yyy@abc');
insert into @TEmails values (2,'zzz@abc');

简单的变种

有一种快速,有效和错误的天真方法：

SELECT
    Main.empid,Phones.phone,Licenses.license,Emails.email
FROM
    @TMain AS Main
    LEFT JOIN @TPhones AS Phones ON Phones.empid = Main.empid
    LEFT JOIN @TLicenses AS Licenses ON Licenses.empid = Main.empid
    LEFT JOIN @TEmails AS Emails ON Emails.empid = Main.empid
ORDER BY Main.empid,email;

它生成所有行的笛卡尔积和重复行.这是上面查询的结果集.您可以看到empid = 2返回12行,即4个电话乘以3个电子邮件和1个许可证.我的猜测是你只想看到empid = 2的4行.换句话说,对于每个empid,结果应该有最小可能的行数(我将在最后显示正确的结果集).

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

长变种

我不确定下面提出的方法是否比你的更有效.您必须同时尝试并比较数据的性能.

我们需要一张数字表.
SQL,Auxiliary table of numbers
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html
http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/you-require-a-numbers-table.aspx

同样,在现实生活中,你将拥有一个合适的数字表,但是对于这个例子,我将使用以下内容：

declare @TNumbers table (Number int);
insert into @TNumbers values (1);
insert into @TNumbers values (2);
insert into @TNumbers values (3);
insert into @TNumbers values (4);
insert into @TNumbers values (5);

我的方法背后的主要思想是首先创建一个包含每个empid的正确行数的辅助表,然后使用此表有效地获得结果.

我们将从计算每个empid的电话,许可证和电子邮件的数量开始：

WITH
CTE_Rows
AS
(
    SELECT Phones.empid,COUNT(*) AS EmpRows
    FROM @TPhones AS Phones
    GROUP BY Phones.empid

    UNION ALL

    SELECT Licenses.empid,COUNT(*) AS EmpRows
    FROM @TLicenses AS Licenses
    GROUP BY Licenses.empid

    UNION ALL

    SELECT Emails.empid,COUNT(*) AS EmpRows
    FROM @TEmails AS Emails
    GROUP BY Emails.empid
)

然后我们计算每个empid的最大行数：

,CTE_MaxRows
AS
(
    SELECT
        CTE_Rows.empid,MAX(CTE_Rows.EmpRows) AS MaxEmpRows
    FROM CTE_Rows
    GROUP BY CTE_Rows.empid
)

上面的CTE对每个empid都有一行：empid本身和最大数量的电话,许可证和电子邮件.现在我们需要扩展此表并为每个empid生成给定的行数.我在这里使用Numbers表：

,CTE_RowNumbers
AS
(
SELECT
    CTE_MaxRows.empid,Numbers.Number AS rn
FROM
    CTE_MaxRows
    CROSS JOIN @TNumbers AS Numbers
WHERE
    Numbers.Number <= CTE_MaxRows.MaxEmpRows
)

（编辑：核心网）

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