有这张桌子
CREATE TABLE BOOKINGS
([RES_ID] varchar(4), [ATTENDANCE_DATE] datetime, [AUTOID] int);
INSERT INTO BOOKINGS
([RES_ID], [ATTENDANCE_DATE], [AUTOID])
VALUES
('A001', '2018-01-01 00:00:00', 1),
('A002', '2018-01-01 00:00:00', 2),
('A003', '2018-01-01 00:00:00', 3),
('A001', '2018-01-02 00:00:00', 4),
('A002', '2018-01-02 00:00:00', 5),
('A003', '2018-01-02 00:00:00', 6),
('A002', '2018-01-03 00:00:00', 7),
('A003', '2018-01-03 00:00:00', 8);我想用id='A001'删除所有行,因为在最近的日期,没有预订(即被取消)。
我已经尝试过了:
with cte as
(
select *,
row_number() over(partition by [res_id]
order by [ATTENDANCE_DATE] desc) rn
from BOOKINGS
)
DELETE FROM cte where rn > 1;但这保留了'A001' (id=4)的最新行,我不希望这样。
预期输出为:
A002 2018-01-03 00:00:00.000 7
A003 2018-01-03 00:00:00.000 8发布于 2018-12-19 20:11:54
一种方法是将每个resid的最新日期与整体最新日期进行比较。您可以使用窗口函数来完成此操作:
with todelete as (
select b.*,
max(attendance_date) over (partition by res_id) as max_ad_resid,
max(attendance_date) over () as max_ad
from bookings b
)
delete from todelete
where max_ad_resid < max_ad;发布于 2018-12-19 20:13:52
你可以像这样简单地删除
delete
from BOOKINGS
where ATTENDANCE_DATE != (
select max(ATTENDANCE_DATE) from BOOKINGS
)发布于 2018-12-19 20:16:50
这就是你要找的东西吗?
DELETE FROM BOOKINGS WHERE
[ATTENDANCE_DATE] <= (SELECT MAX([ATTENDANCE_DATE]) FROM BOOKINGS where RES_ID = 'A001')这将获取RES_ID A001的最大日期(无保留),并删除等于或早于该日期的所有条目。
https://stackoverflow.com/questions/53850946
复制相似问题