代码之家 › 专栏 › 技术社区 › Przemyslaw Remin

SQL连接条件可以是A或B,但不能同时是A和B

join sql-server sql

Przemyslaw Remin · 技术社区 · 7 年前

我有按年份和季度划分的销售数据,去年我想用最后可用的价值填充缺失的季度。

假设我们有源表:

+------+---------+-------+--------+
| year | quarter | sales | row_no |
+------+---------+-------+--------+
| 2018 |       1 |  4000 |      5 |
| 2018 |       2 |  6000 |      4 |
| 2018 |       3 |  5000 |      3 |
| 2018 |       4 |  3000 |      2 |
| 2019 |       1 |  8000 |      1 |
+------+---------+-------+--------+

+------+---------+-------+------------------------+
| year | quarter | sales |                        |
+------+---------+-------+------------------------+
| 2018 |       1 |  4000 |                        |
| 2018 |       2 |  6000 |                        |
| 2018 |       3 |  5000 |                        |
| 2018 |       4 |  3000 |                        |
| 2019 |       1 |  8000 |                        |
| 2019 |       2 |  8000 | <repeat the last value |
| 2019 |       3 |  8000 | <repeat the last value |
| 2019 |       4 |  8000 | <repeat the last value |
+------+---------+-------+------------------------+

因此,我们的任务是将年和季度的笛卡尔函数与相应的或最后的销售额连接起来。

这段代码让我几乎达到了目的:

select r.year, k.quarter, t.sales
from (select distinct year        from [MyTable]) r cross join
     (select distinct quarter     from [MyTable]) k left join
     [MyTable] t
     on (r.year = t.year and k.quarter=t.quarter) or row_no=1

3 回复 | 直到 7 年前

Gordon Linoff 7 年前

一种方法使用外部应用:

select y.year, q.quarter, t.sales
from (select distinct year from [MyTable]) y cross join
     (select distinct quarter from [MyTable]) q outer apply
     (select top (1) t.*
      from [MyTable] t
      where t.year < y.year or
            (t.year = y.year and t.quarter <= q.quarter)
      order by t.year desc, t.quarter desc
     ) t;

对于您的数据量,这应该是好的。

select y.year, q.quarter,
       coalesce(t.sales, tdefault.sales)
from (select distinct year from [MyTable]) y cross join
     (select distinct quarter from [MyTable]) q left join
     [MyTable] t
     on t.year = y.year and
        t.quarter = q.quarter cross join
     (select top (1) t.*
      from [MyTable] t
      order by t.year desc, t.quarter desc
     ) tdefault

Thom A 7 年前

使用CTE和一些窗口函数的一种非常不同的方法。这不需要对表进行两次扫描,也不需要三角连接。

WITH VTE AS(
    SELECT *
    FROM (VALUES (2018,1,4000,5),
                 (2018,2,6000,4),
                 (2018,3,5000,3),
                 (2018,4,3000,2),
                 (2019,1,8000,1)) V([Year],[Quarter],sales, row_no)),
CTE AS(
    SELECT Y.Year,
           Q.Quarter,
           V.sales,
           V.row_no,
           COUNT(CASE WHEN V.sales IS NOT NULL THEN 1 END) OVER (ORDER BY Y.[Year], Q.[Quarter]
                                                                 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Grp
    FROM (VALUES(2018),(2019)) Y([Year])
         CROSS JOIN (VALUES(1),(2),(3),(4)) Q([Quarter])
         LEFT JOIN VTE V ON Y.[Year] = V.[Year] AND Q.[Quarter] = V.[Quarter])
SELECT C.[Year],
       C.[Quarter],
       MAX(C.sales) OVER (PARTITION BY C.Grp) AS Sales
FROM CTE C;

这仅适用于SQL Server 2012+(如下所示) ROWS BETWEEN

Yogesh Sharma 7 年前

我只会这么做 JOIN :

SELECT TT.YEAR, TT.Quarter, COALESCE(T.SALES, MAX(T.SALES) OVER (PARTITION BY TT.YEAR)) AS sales 
FROM (SELECT DISTINCT T.YEAR, TT.Quarter
      FROM [MyTable] T CROSS JOIN
           ( SELECT DISTINCT TT.Quarter FROM [MyTable] TT ) TT
     ) TT LEFT JOIN 
     [MyTable] T 
     ON TT.YEAR = T.YEAR AND TT.Quarter = T.Quarter;

编辑: quarter 所以,你需要 APPLY 在里面 OUTER

SELECT TT.YEAR, TT.Quarter, COALESCE(T.SALES, T1.SALES) AS Sales 
FROM (SELECT DISTINCT T.YEAR, TT.Quarter
      FROM [MyTable] T CROSS JOIN
           ( SELECT DISTINCT TT.Quarter FROM [MyTable] TT ) TT
     ) TT LEFT JOIN 
     [MyTable] T 
     ON TT.YEAR = T.YEAR AND TT.Quarter = T.Quarter OUTER APPLY 
     ( SELECT TOP (1) T.*
       FROM [MyTable] T
       WHERE T.YEAR = TT.YEAR
       ORDER BY T.Quarter DESC
     ) T1;