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屋!