英文:
Delete duplicate rows using certain conditions in MS SQL Server
问题
| sid | cid | arrival | departure | country | state | pid | phone | ||
|---|---|---|---|---|---|---|---|---|---|
| ab2 | a101 | 2023-04-05 | 2023-04-06 | US | ohio | ly1 | 101@gmail.com | 12345 | |
| ab7 | a106 | 2023-01-20 | 2023-04-26 | US | Texas | ly6 | 106@gmail.com | 87468 | |
| ab8 | a107 | 2023-01-20 | 2023-04-26 | US | Texas | ly7 | 107@gmail.com | 55555 | |
| ab1 | a109 | 2023-01-20 | 2023-04-28 | US | Texas | ly9 | 109@gmail.com | 55555 |
英文:
| sid | cid | arrival | departure | country | state | pid | phone | ||
|---|---|---|---|---|---|---|---|---|---|
| ab1 | a101 | 2023-01-01 | 2023-04-05 | US | ohio | ly1 | 101@gmail.com | 12345 | |
| ab2 | a101 | 2023-04-05 | 2023-04-06 | US | ohio | ly1 | 101@gmail.com | 12345 | |
| ab7 | a106 | 2023-01-20 | 2023-04-26 | US | Texas | ly6 | 106@gmail.com | 87468 | |
| ab8 | a107 | 2023-01-20 | 2023-04-26 | US | Texas | ly7 | 107@gmail.com | 55555 | |
| ab1 | a109 | 2023-01-20 | 2023-04-28 | US | Texas | ly9 | 109@gmail.com | 55555 |
If there are more than 1 row with same cid delete it if departure dates between them are 30 days apart. (Here Cid 101 is present more than 1 so we check departure date here, one day difference therefore we keep the latest departure date)
答案1
得分: 1
使用窗口函数 lead() 获取下一个出发日期，然后检查日期差是否小于30天，然后移除记录：
with cte as (
select *,
lead(stayid) over (partition by cid order by departure) as lead_stayid,
DATEDIFF(day, departure, lead(departure) over (partition by cid order by departure)) as date_diff
from mytable
)
delete from mytable
Where stayid in (
select stayid
from cte
where date_diff is not null and date_diff < 30
);
英文:
Using the window lead() to get the next departure date, then we check date diff if less than 30 days then we remove the record :
with cte as (
select *,
lead(stayid) over (partition by cid order by departure) as lead_stayid,
DATEDIFF(day, departure, lead(departure) over (partition by cid order by departure)) as date_diff
from mytable
)
delete from mytable
Where stayid in (
select stayid
from cte
where date_diff is not null and date_diff < 30
);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论