sql - 合并存储为 View 的 SQL 查询/组合表

Question

我有几个存储为 View 的摘要查询...

...并希望将它们组合成一个组合输出，如下所示:

..所以我可以将它用作 Excel 中的数据透视表。

日期 是该案例中唯一的共同点。

我可以使用 SUMIFS 在 Excel 中执行此操作，但更愿意在它到达 Excel 之前在 SQL 中对其进行管理。

有人能帮忙吗？

Answer 1

在没有匹配 ID 的情况下，我能想到的最好办法是通过 ROW_NUMBER() 进行排序，这会给出一个稍微冗长的查询；

WITH cte1 AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY DATE 
                               ORDER BY CASE WHEN Dogs IS NULL THEN 1 END) r1 
  FROM View1
), cte2 AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY DATE 
                               ORDER BY CASE WHEN Region IS NULL THEN 1 END) r2
  FROM View2
), cte3 AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY DATE 
                               ORDER BY CASE WHEN Bed IS NULL THEN 1 END) r3
  FROM View3
)
SELECT COALESCE(cte1.Date, cte2.Date, cte3.Date) Date, 
                Dogs, D_Qty, Region, R_Qty, Bed, B_Qty
FROM cte1
FULL OUTER JOIN cte2
  ON cte1.Date = cte2.Date AND r1=r2
FULL OUTER JOIN cte3
  ON cte1.Date = cte3.Date AND r1=r3
  OR cte2.Date = cte3.Date AND r2=r3
ORDER BY Date, COALESCE(r1,r2,r3)

An SQLfiddle to test with .

您可以考虑使用 ROW_NUMBER() OVER (PARTITION BY DATE ORDER BY (whatever order is in them) 在您的 View 中添加一个顺序列，这将消除所有的 cte，并为您提供一个事物的稳定排序。