MySQL - 选择列值仅为 0 的行，按另一列分组？

Question

示例表格：

ID, foreign_id, status

ID是唯一的，而foreign_id则不是。以下是示例行：

1, 1111, 0  
2, 2222, 0  
3, 3333, 1
4, 1111, 1
5, 4444, 0

我想选择的是只有状态为0的唯一外键ID。如果它的状态为0，但是另一行与相同的外键ID具有状态为1，则不要选择该外键ID。期望输出：

1, 2222, 0
5, 4444, 0

我可以选择 status = 0 并按 foreign_id 进行分组，但这只会选择状态为 0 的行，即使可能还有另一行具有相同的 foreign_id 和状态为 1。

SELECT ID, foreign_id 
FROM table 
WHERE status = 0 
GROUP BY foreign_id

希望得到任何帮助。

- skyz

Answer 1

（我编辑了一下以演示它的工作原理并澄清一些事情）：

你可能感兴趣的是 HAVING 语句。

你可以在 GROUP BY 后使用 HAVING 语句。这里有一个相当不错的解释： https://www.dofactory.com/sql/having

简而言之，HAVING 语句的作用是让你过滤你的数据，以便返回你的 GROUP BY 表达式的结果。

试试这个：

SELECT 
     foreign_id, MAX(status) AS status
FROM
     myTable
GROUP BY 
     foreign_id
HAVING 
     MAX(status) = 0

这是一个关于编程的例子，以下是测试基础的 MS Access 文件的效果： 我输入了以下代码： 然后得到了以下结果： 如果您有任何问题，请告诉我！

- artemis

Answer 2

使用 GROUP_CONCAT 和 DISTINCT 子句，我们对所有唯一的状态进行分组，针对foreign_id。现在在HAVING子句中，我们只需要过滤掉那些唯一状态为'0'的foreign_id即可。

尝试以下操作：

SELECT ID, foreign_id, status, GROUP_CONCAT(DISTINCT status) as statuses 
FROM table 
GROUP BY foreign_id 
HAVING statuses = '0'

- Madhur Bhaiya

---

使用GROUP_CONCAT的想法很好 - 但是如果没有GROUP BY foreign_id，查询将无法返回相关数据，然后您会失去id - 因此，您的查询不会过滤正确的foreign_id记录并且不会输出所需的属性。 - symcbean

@symcbean 我的查询语句中有一个 GROUP BY 子句，请进一步澄清。 - Madhur Bhaiya

试着运行它 - 看看ID会发生什么变化 - 如果您的数据库管理系统配置为使用严格语法，则会出现错误，否则它将报告它看到的“id”的最后一个值 - OP想要所有的ID。 - symcbean

Answer 3

您可以使用 group by 和 having：

select foreign_id, max(status) as status
from t
group by foreign_id
having max(status) = 0;

基本上，你需要在group by之后进行测试。
如果你想要完整的行，使用not exists：

select t.*
from t
where not exists (select 1
                  from t t2
                  where t2.foreign_id = t.foreign_id and t2.status <> 0
                 );

- Gordon Linoff

Answer 4

正如其他人所说，你应该聚合数据，但这里存在许多潜在问题。但在此之前，你已经说明了你想要输出原始记录而不是汇总数据，最简单的解决方案是使用两个查询——一个用于识别仅具有status=0的foreign_ids，另一个用于获取相应的行。

SELECT r.*
FROM examples r
JOIN ( SELECT s.foreign_id, MAX(s.status)
  FROM examples s
  GROUP BY s.foreign_id
  HAVING MAX(s.status)=0
) t
ON r.foreign_id=t.foreign_id

或者，您可以获取 status！=0 的 foreign_ids 并在第二个查询中排除这些foreign_ids——这取决于数据分布，可能更有效。

当“status”＜0时，情况就会变得混乱。输出将包括这些行（SUM（）也可能发生相同的情况）。有几种方法可以解决这个问题，例如，您可以使用 SUM(ABS(status)) 或 SUM(IF(status=0,0,1))。正如Madhur建议的那样，您还可以使用 GROUP_CONCAT(status) 并检查列的输出是否仅为“0”。

但我怀疑您即将告诉我，由于它是名义数字，因此状态不能为负数（或null？）。如果是这种情况，则您正在使用错误的数据类型——应该使用 ENUM。如果是这种情况，则无法对 ENUM 应用 SUM() 或 MAX()，但仍然可以使用 SUM(IF(status=?,0,1)) 或 GROUP_CONCAT() 方法。

因此...

SELECT r.*
FROM examples r
JOIN ( SELECT s.foreign_id, SUM(IF(status=0,0,1))
  FROM examples s
  GROUP BY s.foreign_id
  HAVING SUM(IF(status=0,0,1))=0
) t
ON r.foreign_id=t.foreign_id

- symcbean

Answer 5

使用聚合函数

select * from t where foreign_id in (
     select foreign_id
        from t
        group by foreign_id
        having min(status) = 0
       ) and status!=1

- Zaynul Abadin Tuhin