英文:
Ignoring records for certain criteria
问题
需要获取仅在第二列中具有“1”的记录。如果它们具有除“1”之外的任何其他值,需要忽略特定第一列的所有记录。
可以尝试以下查询:
SELECT ACCOUNT, FLAG
FROM your_table_name
WHERE ACCOUNT NOT IN (
SELECT ACCOUNT
FROM your_table_name
WHERE FLAG <> 1
);
请将“your_table_name”替换为实际表的名称。这个查询将返回只包含第二列中值为“1”的记录,并忽略了具有第二列中任何其他值的特定第一列的记录。
英文:
I have data as below,
ACCOUNT | FLAG |
---|---|
asdf | 1 |
asdf | 2 |
asdf | 3 |
kjhj | 1 |
qwer | 1 |
qwer | 1 |
need to get output:
ACCOUNT | FLAG |
---|---|
kjhj | 1 |
qwer | 1 |
situation is that need to get records that have only "1" in 2nd column. If they have any other value other than "1", need to ignore all records for particular 1st column.
can you plz suggest a query
tried group by but didn't find option
答案1
得分: 1
将每个输出行分组到单个帐户,然后使用HAVING和*同时*最小值和最大值来断言组中的所有行必须具有flag=1。
```sql
SELECT
account,
MIN(flag) flag
FROM
your_table
GROUP BY
account
HAVING
MIN(flag) = 1
AND MAX(flag) = 1
有些人更喜欢以下方式,更容易理解,它还可以导致排除组中的NULL行...
HAVING
MIN(CASE WHEN flag=1 THEN 1 ELSE 0 END) = 1
<details>
<summary>英文:</summary>
Group to a single account per output row, then assert that all rows in a group must have flag=1 by using HAVING with *both* min and max.
SELECT
account,
MIN(flag) flag
FROM
your_table
GROUP BY
account
HAVING
MIN(flag) = 1
AND MAX(flag) = 1
Some people prefer the following and being more understandable, and it also causes a NULL row to exclude the group...
HAVING
MIN(CASE WHEN flag=1 THEN 1 ELSE 0 END) = 1
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论