时间戳

首页 > 解决方案 > 交叉应用中的列到行两个表

sql - 交叉应用中的列到行两个表

问题描述

使用 SQL Server 我有两个表，在 DB 中的示例表 #T1 下面有超过一百万行，表 #T2 有 100 行。两个表都是列格式，我需要透视到行并加入两者。

我可以使用 Cross Apply 在一个查询中获取所有内容并删除 cte 吗？

#T1

#T2

输出

这是我的代码，我有正确的输出，但考虑到行数，这是最有效的方法吗？

with cte_sizes
as
(
 select SizeRange,Size,ColumnPosition
 from #T2
 cross apply (
              values(Sz1,1),(Sz2,2),(Sz3,3),(Sz4,4)
             ) X (Size,ColumnPosition)
 )

 select a.ProductID,a.SizeRange,c.Size,isnull(x.Qty,0) as Qty
 from #T1 a
 cross apply (
              values(a.Sale1,1),(a.Sale2,2),(a.Sale3,3),(a.Sale4,4)
              ) X (Qty,ColumnPosition)
 inner join cte_sizes c 
 on c.SizeRange = a.SizeRange 
 and c.ColumnPosition = x.ColumnPosition

我也编写了代码并考虑了这一点，但这是 CROSS APPLY 更好的方法吗？

with cte_sizes
as
(
   select 1 as SizePos
  union all
   select SizePos + 1 as SizePos
   from cte_sizes
   where SizePos < 4
)

select a.ProductID
      ,a.SizeRange
      ,(case when b.SizePos = 1 then c.Sz1
             when b.SizePos = 2 then c.Sz2
             when b.SizePos = 3 then c.Sz3
             when b.SizePos = 4 then c.Sz4 end
       ) as Size
      ,isnull((case when b.SizePos = 1 then a.Sale1
                    when b.SizePos = 2 then a.Sale2
                    when b.SizePos = 3 then a.Sale3
                    when b.SizePos = 4 then a.Sale4 end
              ),0) as Qty

 from #T1 a
 inner join #T2 c on c.SizeRange = a.SizeRange
 cross join cte_sizes b

标签： sqltsqlsql-server-2012

解决方案

这是疯狂的猜测，但我的魔法水晶球告诉我，你可能正在寻找这样的东西：

为此，我们根本不需要您的桌子#TS。

WITH Unpivoted2 AS
(
    SELECT t2.SizeRange,A.* FROM #t2 t2
    CROSS APPLY(VALUES(1,t2.Sz1)
                     ,(2,t2.Sz2)
                     ,(3,t2.Sz3)
                     ,(4,t2.Sz4)) A(SizePos,Size)
) 
SELECT t1.ProductID
      ,Unpivoted2.SizeRange
      ,Unpivoted2.Size
      ,Unpivoted1.Qty
FROM #t1 t1
CROSS APPLY(VALUES(1,t1.Sale1)
                 ,(2,t1.Sale2)
                 ,(3,t1.Sale3)
                 ,(4,t1.Sale4)) Unpivoted1(SizePos,Qty)
LEFT JOIN Unpivoted2 ON Unpivoted1.SizePos=Unpivoted2.SizePos AND t1.SizeRange=Unpivoted2.SizeRange
ORDER BY t1.ProductID,Unpivoted2.SizeRange;

结果：

+-----------+-----------+------+------+
| ProductID | SizeRange | Size | Qty  |
+-----------+-----------+------+------+
| 123       | S-XL      | S    | 1    |
+-----------+-----------+------+------+
| 123       | S-XL      | M    | 12   |
+-----------+-----------+------+------+
| 123       | S-XL      | L    | 13   |
+-----------+-----------+------+------+
| 123       | S-XL      | XL   | 14   |
+-----------+-----------+------+------+
| 456       | 8-14      | 8    | 2    |
+-----------+-----------+------+------+
| 456       | 8-14      | 10   | 22   |
+-----------+-----------+------+------+
| 456       | 8-14      | 12   | NULL |
+-----------+-----------+------+------+
| 456       | 8-14      | 14   | 24   |
+-----------+-----------+------+------+
| 789       | S-L       | S    | 3    |
+-----------+-----------+------+------+
| 789       | S-L       | M    | NULL |
+-----------+-----------+------+------+
| 789       | S-L       | L    | 33   |
+-----------+-----------+------+------+
| 789       | S-L       | XL   | NULL |
+-----------+-----------+------+------+

简而言之：

cte将以非枢轴结构返回您#T2。每个名称编号的列（您应该避免的）作为单行返回，并带有指示位置的索引。

SELECT 将对#T1这个集合执行相同的操作并加入 cte。

更新：经过很多评论......

如果我正确理解了这一点（以及对初始问题的更改），则上述方法效果很好，但您想知道性能最佳的方法。

“什么是最快的方法？”的第一个答案 是Eric Lippert 的 Race your horses。

很高兴知道 1：CTE 只不过是语法糖。它将允许键入一次子查询并像使用表一样使用它，但它对引擎如何工作的方式没有影响。

很高兴知道 2：无论您使用APPLY还是. 都存在巨大差异JOIN。第一个将使用当前行的值每行调用一次子源。第二个必须先创建两个集合，然后通过某种条件加入它们。没有一般的“什么是更好的”......

对于您的问题：由于有一个非常大的集合和一个非常小的集合，这完全取决于您何时使用任何类型的过滤器来减少大集合。越早越好。

最重要的是：在任何情况下，当您发现名称编号（类似phone1, phone2, phoneX）时，它都是不良结构的标志。最昂贵的工作是将您的 4 个名称编号的列转换为一些专用的行。这应该以标准化格式存储...

如果您仍然需要帮助，我会请您提出一个新问题。

推荐阅读