SQL Server 2012 - 将 12 个并集全部合并为一个具有自定义列的查询

Question

我有一个关于 UNION All 的 SQL Server 2012 问题。我已经包含了代码。对于糟糕的编码实践，我深表歉意。还是新来的。

基本上，在源表中，没有日期字段，而是产品编号字段。我需要看到每个日期的产品编号出现一次，从今天开始倒退 14 天。

在这种情况下，"日期"列必须是我自己创建的自定义列。我能够使其工作的唯一方法是做一个 Union All，然后为每个查询增加 1 天，以获得我需要的记录，即每个日期出现一次的产品编号。简而言之，每个产品编号应显示 14 个不同的时间。

很好奇是否有另一种方法可以在不执行存储过程或创建表的情况下编写它。

select Product_Number
            ,cast(GETDATE() -1 as date) DateKey
      from Test.dbo.PRODUCT_MASTER_0001_PM
      Where ACTIVE = 'Y'
      union all
      select Product_Number
            ,cast(GETDATE() -2 as date) 
      from Test.dbo.PRODUCT_MASTER_0001_PM
      Where ACTIVE = 'Y'
      union all
      select Product_Number
            ,cast(GETDATE() -3 as date) 
      from Test.dbo.PRODUCT_MASTER_0001_PM
      Where ACTIVE = 'Y'
      union all
      select Product_Number
            ,cast(GETDATE() -4 as date) 
      from Test.dbo.PRODUCT_MASTER_0001_PM
      Where ACTIVE = 'Y'
      union all
      select Product_Number
            ,cast(GETDATE() -5 as date) 
      from Test.dbo.PRODUCT_MASTER_0001_PM
      Where ACTIVE = 'Y'
      union all
      select Product_Number
            ,cast(GETDATE() -6 as date) 
      from Test.dbo.PRODUCT_MASTER_0001_PM
      Where ACTIVE = 'Y'
      union all
      select Product_Number
            ,cast(GETDATE() -7 as date) 
      from Test.dbo.PRODUCT_MASTER_0001_PM
      Where ACTIVE = 'Y'
      union all
      select Product_Number
            ,cast(GETDATE() -8 as date) 
      from Test.dbo.PRODUCT_MASTER_0001_PM
      Where ACTIVE = 'Y'
      union all
      select Product_Number
            ,cast(GETDATE() -9 as date) 
      from Test.dbo.PRODUCT_MASTER_0001_PM
      Where ACTIVE = 'Y'
      union all
      select Product_Number
            ,cast(GETDATE() -10 as date) 
      from Test.dbo.PRODUCT_MASTER_0001_PM
      Where ACTIVE = 'Y'
      union all
      select Product_Number
            ,cast(GETDATE() -11 as date) 
      from Test.dbo.PRODUCT_MASTER_0001_PM
      Where ACTIVE = 'Y'
      union all
      select Product_Number
            ,cast(GETDATE() -12 as date) 
      from Test.dbo.PRODUCT_MASTER_0001_PM
      Where ACTIVE = 'Y'
      union all
      select Product_Number
            ,cast(GETDATE() -13 as date) 
      from Test.dbo.PRODUCT_MASTER_0001_PM
      Where ACTIVE = 'Y'
      union all
      select Product_Number
            ,cast(GETDATE() -14 as date) 
      from Test.dbo.PRODUCT_MASTER_0001_PM
      Where ACTIVE = 'Y'

任何帮助，不胜感激。

多谢

Answer 1

试试这个：

SELECT t2.Product_Number, t1.dateField
FROM (
   VALUES (CAST(GETDATE() - 1 AS DATE)), (CAST(GETDATE() - 2 AS DATE)),
          (CAST(GETDATE() - 3 AS DATE)), (CAST(GETDATE() - 4 AS DATE)), 
          ...
          (CAST(GETDATE() - 13 AS DATE)), (CAST(GETDATE() - 14 AS DATE))) AS t1(dateField)
CROSS JOIN (
   SELECT Product_Number
   FROM Test.dbo.PRODUCT_MASTER_0001_PM
   WHERE ACTIVE = 'Y') AS t2(Product_Number)

上面的查询使用表值构造函数创建一个内联表，其中包含从今天开始的所有 14 天。然后CROSS JOIN我们可以将这些日期与Product_Number结合起来以获得所需的结果集。

Answer 2

WITH cte_rec AS (
    SELECT 1 AS num
    UNION ALL 
SELECT num + 1 AS num 
FROM cte_rec WHERE num < 15
)
SELECT GETDATE() - num AS date, p.Product_Number 
FROM cte_rec
CROSS APPLY PRODUCT_MASTER_0001_PM p
WHERE p.ACTIVE='Y'

说明：cte_rec - 是递归，用于获取从 1 到 14 的数字。GETDATE（）-number - 像往常一样。交叉应用将输出每个产品编号的日期。

上面的答案更好 - 您可以轻松更改天数。