英文:
remove multiple duplicate data records if only one of them meets a certain criteria
问题
这个问题的核心是要知道如何过滤掉特定的数据。为了更好地解释，我将创建一个示例。
假设我有以下表格：
| Id | Date | Status |
|---|---|---|
| 1 | 2023-01-01 | 2 |
| 1 | 2023-01-02 | 3 |
| 1 | 2023-01-03 | 4 |
| 2 | 2023-01-01 | 2 |
| 2 | 2023-01-02 | 3 |
| 3 | 2023-01-01 | 2 |
在这种情况下，我只对那些从未标记为status = 4的id感兴趣。我可以通过'WHERE Status <> 4'来过滤掉第三行，但仍然会计算第一行和第二行，这不是我想要的。
基本上，我正在寻找一种删除具有特定状态（在这种情况下，status = 4）的ID的所有记录的方法。
预期输出：
| Id | Date | Status |
|---|---|---|
| 2 | 2023-01-01 | 2 |
| 2 | 2023-01-02 | 3 |
| 3 | 2023-01-01 | 2 |
英文:
the core of this question is to know how I can filter out a specific data. To better explain I will create an example.
Let's say I have the following table:
| Id | Date | Status |
|---|---|---|
| 1 | 2023-01-01 | 2 |
| 1 | 2023-01-02 | 3 |
| 1 | 2023-01-03 | 4 |
| 2 | 2023-01-01 | 2 |
| 2 | 2023-01-02 | 3 |
| 3 | 2023-01-01 | 2 |
At this case, I am only interest in the ids which have never been market as status = 4. I could filter out the third row by 'WHERE Status <> 4' but I would still be counting the first and second row. Which is something I am not looking for.
Basically, I am looking for a way to delete all the records of an ID that has been in a specific status (at this case, status = 4).
EXPECTED OUTPUT:
| Id | Date | Status |
|---|---|---|
| 2 | 2023-01-01 | 2 |
| 2 | 2023-01-02 | 3 |
| 3 | 2023-01-01 | 2 |
答案1
得分: 2
你可以尝试类似这样的查询：
select t.id, t.date, t.status
from your_table as t
where not exists
(
select 1 from your_table as x
where t.id = x.id and x.status = 4
)
英文:
You can try something like this
select t.id,t.date,t.status
from your_table as t
where not exists
(
select 1 from your_table as x
where t.id=x.id and x.status=4
)
答案2
得分: 1
WITH CTE AS
(SELECT ID FROM sometable WHERE Status = 4)
DELETE FROM sometable WHERE ID IN
(SELECT ID FROM CTE)
英文:
WITH CTE AS
(SELECT ID FROM sometable WHERE Status = 4)
DELETE FROM sometable WHERE ID IN
(SELECT ID FROM CTE)
答案3
得分: 0
DELETE FROM DataTable WHERE ID NOT IN (SELECT ID FROM DataTable WHERE status = 4)
英文:
Another way you can try:
DELETE FROM DataTable WHERE ID NOT IN (SELECT ID FROM DataTable WHERE status = 4)
答案4
得分: 0
以下是翻译好的内容：
这是使用窗口函数 SUM() 结合 CASE WHEN 的另一种方法，用于检查是否存在 Status = 4 的情况 Per Id：
with cte as (
select *, sum(case when Status = 4 then 1 else 0 end) over(partition by Id) as is_exist
from mytable
)
select Id, Date, Status
from cte
where is_exist = 0;
英文:
This is an other way to do it using the window function SUM() combined with CASE WHEN to check if Status = 4 exist Per Id :
with cte as (
select *, sum(case when Status = 4 then 1 else 0 end) over(partition by Id) as is_exist
from mytable
)
select Id, Date, Status
from cte
where is_exist = 0;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论