英文:
SQL how to exclude data
问题
根据您的要求，以下是翻译好的内容：
如何在这种情况下制作一个排除ID的查询：
- 包含失败状态
- 包含失败状态，即使有一个或多个成功状态
- 包含待处理状态
根据图像，应该只选择B001和E001
我准备了查询：
SELECT *
FROM TABLE a
WHERE a.Content = 'Submit'
AND a.Status = 'S'
AND a.Status NOT IN ('F', 'P')
英文:
How to make a query to exclude id for this scenario:
- Contains failed status
- Contains failed status, even it has 1 or more success status
- Contains pending status
Based on the image, it should only choose B001 and E001
I prepare query:
SELECT *
FROM TABLE a
WHERE a.Content = 'Submit'
AND a.Status = 'S'
AND a.Status NOT IN ('F', 'P')
答案1
得分: 3
Here is the translated content:
由于您需要检查其他行的内容以满足您的条件，您需要使用子查询。
这里我使用了 NOT EXISTS 子句。它检查当前行是否存在具有相同 ID 但状态为 F 或 P 的行。
SELECT * FROM TABLE a
WHERE a.Content = 'Submit'
AND NOT EXISTS (
SELECT 1
FROM TABLE a1
WHERE a1.ID = a.ID
AND a.Status IN ('F', 'P')
)
英文:
Since you need to check other rows content for your condition, you need a sub-query.
Here I use NOT EXISTS clause. It checks that fpr crrent row there are now rows with same ID, but status F or P.
SELECT * FROM TABLE a
WHERE a.Content = 'Submit'
AND NOT EXISTS (
SELECT 1
FROM TABLE a1
WHERE a1.ID = a.ID
AND a.Status IN ('F', 'P')
)
答案2
得分: 1
只使用窗口函数！无需聚合、自连接或子查询：
select *
from (
select t.*,
max(case when status in ('F', 'P') then 1 else 0 end) over(partition by id) has_f_or_p
from mytable t
) t
where has_f_or_p = 0
这个查询只扫描表格一次，因此比其他解决方案更高效。
英文:
Just use window functions! No need for aggregation, self-joining or subqueries:
select *
from (
select t.*,
max(case when status in ('F', 'P') then 1 else 0 end) over(partition by id) has_f_or_p
from mytable t
) t
where has_f_or_p = 0
This scans the table only once, and hence is more efficient than other solutions.
答案3
得分: 0
你需要使用汇总的数据来确定是否仅在多行中状态为 'S'。您不能仅使用等式或不等式运算符来执行此操作。
例如：
CREATE TABLE data (
ID varchar(4),
Content varchar(10),
Status varchar(1)
);
INSERT INTO data (ID, Content, Status)
VALUES
('A001', 'Process', 'S'), ('A001', 'Verify', 'F'), ('B001', 'Process', 'S'),
('C001', 'Process', 'P'), ('D001', 'Submit', 'P'), ('D001', 'Process', 'S'),
('E001', 'Process', 'S'), ('E001', 'Verify', 'S'), ('E001', 'Submit', 'S');
SELECT
*
FROM data
WHERE id IN (
SELECT id
FROM data
GROUP BY id
HAVING MIN(status) = MAX(status) AND MAX(status) = 'S'
);
结果如下：
| id | content | status |
|:--- |:--------|:-------|
| B001 | Process | S |
| E001 | Process | S |
| E001 | Verify | S |
| E001 | Submit | S |
或者，您也可以使用内连接进行查询：
SELECT
*
FROM data
INNER JOIN (
SELECT id
FROM data
GROUP BY id
HAVING MIN(status) = MAX(status) AND MAX(status) = 'S'
) AS sq ON data.id = sq.id;
注意：不建议使用数据的图像，最好将其粘贴为简单的文本表格。
英文:
You need to use aggregated data to determine if the status is only 'S' over multiple rows. You cannot do this with just equality or inequality operators.
e.g.
CREATE TABLE data (
ID varchar(4),
Content varchar(10),
Status varchar(1)
);
INSERT INTO data (ID, Content, Status)
VALUES
('A001', 'Process', 'S'), ('A001', 'Verify', 'F'), ('B001', 'Process', 'S'),
('C001', 'Process', 'P'), ('D001', 'Submit', 'P'), ('D001', 'Process', 'S'),
('E001', 'Process', 'S'), ('E001', 'Verify', 'S'), ('E001', 'Submit', 'S');
select
*
from data
where id in (
select id
from data
group by id
having min(status) = max(status) and max(status) = 'S'
)
| id | content | status |
|:--- |:--------|:-------|
| B001 | Process | S |
| E001 | Process | S |
| E001 | Verify | S |
| E001 | Submit | S |
alternate use an inner join:
select
*
from data
inner join (
select id
from data
group by id
having min(status) = max(status) and max(status) = 'S'
) as sq on data.id = sq.id
NB: It is never a good idea to use an image of your data, just paste it as a simple text table.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论