sql - 如果满足条件,SQL 返回特定值
问题描述
我有两张桌子:
CREATE TABLE dates
(
dates DATETIME
);
INSERT INTO dates(dates)
VALUES
('2021-04-22 00:00:00.000'),
('2021-04-23 00:00:00.000'),
('2021-04-24 00:00:00.000')
CREATE TABLE deliveries
(
delivery_id VARCHAR(20),
product VARCHAR(10),
start_date DATETIME,
end_date DATETIME,
);
INSERT INTO deliveries(delivery_id, product, start_date, end_date)
VALUES
('A01', 'CUSTOM', '2021-04-22', '2021-04-23'),
('A02', 'CUSTOM', '2021-04-21', '2021-04-22'),
('A03', 'NORMAL', '2021-04-01', '2021-04-30'),
('A04', 'NORMAL', '2021-04-22', '2021-04-24'),
('A05', 'NORMAL', '2021-04-19', '2021-04-22'),
('A06', 'NORMAL', '2021-04-20', '2021-04-20')
这是我的查询:
declare @TodaysDate datetime = CONVERT(DATETIME, CONVERT(DATE, CURRENT_TIMESTAMP)) + '00:00:00.000'
declare @EndDate datetime = dateadd(day,5,@TodaysDate)
declare @TomorrowsDate datetime = dateadd(day,1,@TodaysDate)
select dt.dates, de.delivery_id, de.product
from dates dt left join deliveries de
on dt.dates between de.start_date and de.end_date
and not(de.end_date = @TodaysDate AND de.product LIKE '%CUSTOM%')
order by delivery_id
结果如下:
dates delivery_id product
2021-04-22T00:00:00Z A01 CUSTOM
2021-04-23T00:00:00Z A01 CUSTOM
2021-04-23T00:00:00Z A03 NORMAL
2021-04-24T00:00:00Z A03 NORMAL
2021-04-22T00:00:00Z A03 NORMAL
2021-04-22T00:00:00Z A04 NORMAL
2021-04-24T00:00:00Z A04 NORMAL
2021-04-23T00:00:00Z A04 NORMAL
2021-04-22T00:00:00Z A05 NORMAL
我需要修改此查询以再添加一个条件,以仅在产品现在和现在(2021-04-22)和明天(2021-04-23)时返回列中包含end_date
(但不是start_date
)的行。因此在上述情况下,结果不应包含行,而是从第 2 行开始:dates
CUSTOM
start_date
end_date
2021-04-22 | A01
2021-04-23 | A01
解决方案
尝试将您的查询修改为此,看看它是否为您提供了正确的数据:
select
dt.dates,
de.delivery_id, de.product
from dates dt
left join deliveries de on dt.dates between de.start_date and de.end_date
and not(de.end_date = @TodaysDate AND de.product LIKE '%CUSTOM%')
and not(de.product LIKE '%CUSTOM%' AND dt.dates = @TodaysDate AND de.end_date = @TomorrowsDate)
order by de.delivery_id
推荐阅读
- javascript - javascript 对 html 进行更改后如何获取 html 源代码(没有浏览器)
- docusignapi - Docusign API,文档内容未显示
- c++ - 使用 CMake 的 CTest 在 CLion 中运行测试
- swift - 在swift中覆盖系统UI样式时如何更改默认状态栏颜色
- sas - 带有 libref 的 Libname:如何访问数据源(库)
- php - 使用插入值选择的正确方法
- angular - ModalView Nativescript 结果
- jquery - 根据窗口内部宽度执行函数
- javascript - 锁文件解释,每一行到底是什么意思?纱线锁
- javascript - 如何在 readOnly 中直观地区分 TextField?