时间戳

首页 > 解决方案 > 在 WHERE 子句中使用 CASE

sql - 在 WHERE 子句中使用 CASE

问题描述

我有 2 个表需要根据几个参数连接。其中一个参数是年份。一个表包含当前年份，另一个年份不包含当前年份，因此必须使用最近的年份并与其他参数匹配。

例子

产品

-------------------------------------------------------------------------------
| product_id | category_id | sub_category_id | product_year | amount |
-------------------------------------------------------------------------------
| 504        | I           | U               | 2020         | 400    |
| 510        | I           | U               | 2019         | 100    |
| 528        | I           | U               | 2019         | 150    |
| 540        | I           | U               | 2018         | 1000   |

折扣

-----------------------------------------------------------------------------
| discount_year | category_id | sub_category_id | discount |
-----------------------------------------------------------------------------
| 2018          | I           | U               | 0.15     |
| 2017          | I           | U               | 0.35     |
| 2016          | I           | U               | 0.50     |

输出

-----------------------------------------------------------------------------
| product_id | category_id | sub_category_id | product_year | discount_year |
-----------------------------------------------------------------------------
| 504        | I           | U               | 2020         | 2018          |
| 510        | I           | U               | 2019         | 2018          |
| 528        | I           | U               | 2019         | 2018          |
| 540        | I           | U               | 2018         | 2017          |

折扣总是从一年后获得，但如果这些价格不可用，那么它会一直追溯到一年，直到可用。

我尝试了以下方法：

SELECT 
    product_year, a.product_id, a.category_id, a.sub_category_id, 
    discount_year, amount, discount
FROM
    Product a
INNER JOIN 
    Discount b ON a.category_id = b.category_id 
               AND a.sub_category_id  = b.sub_category_id
               AND product_ year = CASE
                                      WHEN discount_year + 1 = product_year 
                                         THEN discount_year + 1
                                      WHEN discount_year + 2 = product_year 
                                         THEN discount_year + 2
                                      WHEN discount_year + 3 = product_year 
                                         THEN discount_year + 3
                                   END
 WHERE 
     product = 540

这将返回以下输出：

--------------------------------------------------------------------------------------------------
| product_year | product_id | category_id | sub_category_id | discount_year | amount | discount |
--------------------------------------------------------------------------------------------------
| 2016         | 540        | I           | U               | 2017          | 1000   | 0.50     |
| 2017         | 540        | I           | U               | 2017          | 1000   | 0.35     |

任何帮助将不胜感激。

标签： sqlsql-servertsql

解决方案

您可以使用OUTER APPLY和子查询。在子查询中选择最大的行discount_year，即小于product_year使用TOPand的行ORDER BY。

SELECT p.product_year,
       p.product_id,
       p.category_id,
       p.sub_category_id,
       d.discount_year,
       p.amount,
       d.discount
       FROM product p
            OUTER APPLY (SELECT TOP 1
                                *
                                FROM discount d
                                WHERE d.category_id = p.category_id
                                      AND d.sub_category_id = p.sub_category_id
                                      AND d.discount_year < p.product_year
                                ORDER BY d.discount_year DESC) d;

推荐阅读