英文:

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>