SQL:根据另一个表中的不同条件设置条件值 [英] SQL: Set conditional value based on varying criteria in another table

问题描述

SQL的一些新知识-数据库是Snowflake，我相信它是ANSI

A bit new to SQL - the db is Snowflake, which I believe is ANSI

主表如下所示.相同发行/UPC/仓库/日期的组合是可能的，因为每当报告新发行时都会添加新记录.存在其他列，但不应影响此问题

Main table shown below. Combinations of same Issue/UPC/Warehouse/Date can be possible, since a new record is added whenever a new issue is reported. Other columns exist, but should not affect this question

排除栏是我要弄清楚的-如果排除"表中的发行/UPC/仓库和日期"的所需组合位于"Y"，则如下所示.

The exclude column is what I'm trying to figure out - it should be 'Y' if the desired combination of Issue/UPC/Warehouse and Date is in the Exclusion table, shown below.

棘手的部分是LEVEL列，它定义UPC/问题/仓库组合是否必须匹配，还是仅UPC/问题，还是仅UPC.另外，主表中的记录必须在日期"范围内才能被排除.

The tricky part is the LEVEL column, defining if a UPC/Issue/Warehouse combination must match, or just UPC/Issue, or just UPC. Also, the records in the main table must fall within the Date range to be excluded.

视觉上，预期的结果是这个

Visually, expected result is this

此解决方案仅适用于一个级别(问题/UPC/仓库)，但是我无法弄清楚如何在不重叠的情况下执行另外两个级别，以及排除意外记录的可能性.

This solution works for just one level (Issue/UPC/Warehouse), but I can't figure out how to do the other two without overlap and the possibility of excluding records on accident.

update t
set exclude = 'Y'
where exists (select 1
              from exclusions e
              where e.issue_code = t.issue_code and
              e.upc = t.upc and
              e.warehouse = t.warehouse and
              t.date between e.date_from and e.date_to);

推荐答案

David的答案涵盖了采取正确方法，使用 CASE条件表达式，但请确保您的查询还明确将级别检查合并到每个条件中.这是一个详细的示例:

David's answer has covered the right approach to take, using the CASE conditional expression, but ensure that your query also incorporates the level check into each condition explicitly. Here's a verbose example:

update t
set exclude = case
    when exists(
        select 1
        from exclusions e
        where 
                e.warehouse = t.warehouse
            and e.upc = t.upc
            and e.issue_code = t.issue_code
            and t.date between e.date_from and e.date_to
            and e.level = 'UPC/ISSUE/WAREHOUSE'
    ) then 'Y'
    when exists(
        select 1
        from exclusions e
        where 
                e.issue_code = t.issue_code
            and e.upc = t.upc
            and t.date between e.date_from and e.date_to
            and e.level = 'UPC/ISSUE'
    ) then 'Y'
    when exists(
        select 1
        from exclusions e
        where 
                e.upc = t.upc
            and t.date between e.date_from and e.date_to
            and e.level = 'UPC'
    ) then 'Y'
    else ''
end;

这篇关于SQL:根据另一个表中的不同条件设置条件值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！