英文:
Select data from 1 column based on multiple conditions in 2nd column
问题
我需要排除所有包含绿色的ID。
预期结果：
| ID | Colors_allowed |
|---|---|
| 666 | Blue |
| 786 | Blue |
| 999 | Red |
| 999 | Orange |
这是我尝试过的查询语句：
SELECT *
FROM Table
WHERE colors_allowed != 'Green';
但这个查询也返回了除了绿色以外的颜色的ID "555" 和 "888"，这不符合我的要求，因为它们曾经有绿色，应该被排除掉。
英文:
I need to exclude all IDs that have the color green in them.
| ID | Colors_Allowed |
|---|---|
| 555 | Blue |
| 555 | Green |
| 666 | Blue |
| 786 | Blue |
| 888 | Blue |
| 888 | Green |
| 888 | Red |
| 999 | Red |
| 999 | Orange |
Expected result:
| ID | Colors_allowed |
|---|---|
| 666 | Blue |
| 786 | Blue |
| 999 | Red |
| 999 | Orange |
Here's the attempt I've made:
SELECT *
FROM Table
WHERE colors_allowed != 'Green'
But this also gives me ids "555" and "888" with colors other than green, which I don't want since they had the color green, and that should have disqualified them.
答案1
得分: 2
只使用窗口函数：
select *
from (
select t.*,
max(case when colors_allowed = 'Green' then 1 else 0 end) over(partition by id) has_green
from mytable t
) t
where has_green = 0
窗口函数中的条件 `max()` 检查与当前行具有相同 `id` 的所有行，并在有绿色的情况下返回 `1`。外部查询使用此信息进行筛选。
窗口函数技术的优点是它只需要进行一次表扫描（而不是自连接、`exists` 和 `in`）。
英文:
Just use window functions:
select *
from (
select t.*,
max(case when colors_allowed = 'Green' then 1 else 0 end) over(partition by id) has_green
from mytable t
) t
where has_green = 0
The conditional max() checks all rows that have the same id as the current row, and yields 1 if any has color green. The outer query uses this information for filtering.
The upside of the window function technique is that it requires a single table scan (as opposed to self-joins, exists and in).
答案2
得分: 0
你可以考虑使用NOT EXISTS运算符：
SELECT t1.*
FROM tab t1
WHERE NOT EXISTS(SELECT 1
FROM tab t2
WHERE t1.ID = t2.ID AND colors_allowed = 'Green')
这会检查是否没有记录具有相同的ID t1.ID = t2.ID 至少有一个 "Green" 值。
输出：
| ID | Colors_Allowed |
|---|---|
| 666 | Blue |
| 786 | Blue |
| 999 | Red |
| 999 | Orange |
在这里查看演示链接。
英文:
You can consider using the NOT EXISTS operator:
SELECT t1.*
FROM tab t1
WHERE NOT EXISTS(SELECT 1
FROM tab t2
WHERE t1.ID = t2.ID AND colors_allowed = 'Green')
This checks when there's no record of the same id t1.ID = t2.ID having at least one "Green" value.
Output:
| ID | Colors_Allowed |
|---|---|
| 666 | Blue |
| 786 | Blue |
| 999 | Red |
| 999 | Orange |
Check the demo here.
答案3
得分: 0
WITH
indata(ID,Colors_Allowed) AS (
SELECT 555,'Blue'
UNION ALL SELECT 555,'Green'
UNION ALL SELECT 666,'Blue'
UNION ALL SELECT 786,'Blue'
UNION ALL SELECT 888,'Blue'
UNION ALL SELECT 888,'Green'
UNION ALL SELECT 888,'Red'
UNION ALL SELECT 999,'Red'
UNION ALL SELECT 999,'Orange'
)
-- 实际查询从这里开始 - 用以下代码替换逗号 "WITH"
,
green AS (
SELECT
id
FROM indata
WHERE colors_allowed='Green'
)
SELECT
*
FROM indata
WHERE id NOT IN (SELECT id FROM green)
;
| ID | Colors_Allowed |
|---|---|
| 666 | Blue |
| 786 | Blue |
| 999 | Red |
| 999 | Orange |
英文:
WITH
indata(ID,Colors_Allowed) AS (
SELECT 555,'Blue'
UNION ALL SELECT 555,'Green'
UNION ALL SELECT 666,'Blue'
UNION ALL SELECT 786,'Blue'
UNION ALL SELECT 888,'Blue'
UNION ALL SELECT 888,'Green'
UNION ALL SELECT 888,'Red'
UNION ALL SELECT 999,'Red'
UNION ALL SELECT 999,'Orange'
)
-- REAL query starts here -replace following comma with "WITH" ..
,
green AS (
SELECT
id
FROM indata
WHERE colors_allowed='Green'
)
SELECT
*
FROM indata
WHERE id NOT IN (SELECT id FROM green)
;
| ID | Colors_Allowed |
|---|---|
| 666 | Blue |
| 786 | Blue |
| 999 | Red |
| 999 | Orange |
答案4
得分: -1
你可以使用INNER子查询来查找颜色为绿色的ID，然后使用外部查询和NOT IN子句来选择不包含绿色的ID。
示例查询 - http://sqlfiddle.com/#!18/2848d/1
SELECT DISTINCT ID, Colors_Allowed
FROM TableName
WHERE ID NOT IN (SELECT ID
FROM TableName
WHERE Colors_Allowed = 'Green');
英文:
You can use INNER subquery to find ID having color Green and using outer query with NOT IN clause to select ID which does not have Green in it.
Demo query - http://sqlfiddle.com/#!18/2848d/1
SELECT DISTINCT ID, Colors_Allowed
FROM TableName
WHERE ID NOT IN ( SELECT ID
FROM TableName
WHERE Colors_Allowed = 'Green');
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论