mysql - 双重计数语句MySQL查询

Question

我有下表，其中包含 bool 列 has_object，它指示每一行是否具有关联的数字对象。

+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| pid        | varchar(255) | NO   | PRI |         |       |
| title      | text         | YES  |     | NULL    |       |
| owner_uid  | int(11)      | YES  |     | NULL    |       |
| has_object | int(11)      | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+

我试过这个查询来获取关于每个 owner_uid 的统计信息。但在我的表中它返回了错误的结果:

SELECT 
  a.owner_uid, 
  count(b.pid) as count1, 
  count(c.pid) as count2  
FROM
  islandora_report a
  JOIN islandora_report b ON b.owner_uid = a.owner_uid AND b.has_object = 0
  JOIN islandora_report c ON c.owner_uid = a.owner_uid AND c.has_object = 1
GROUP BY a.owner_uid;

结果:

Answer 1

因为 BOOLEAN 只是一个 0 或 1，你实际上可以做一对 SUM() 没有任何连接来添加列。

SELECT
  owner_uid,
  /* SUM() adds up all the 1 values */
  SUM(has_object) AS count_true,
  /* Invert the boolean with a case statement to get the inverse */
  SUM(CASE WHEN has_object = 1 THEN 0 ELSE 1 END) AS count_false
FROM islandora_report
GROUP BY owner_uid

除上述方法外，还有其他方法可以反转 bool 值。这只是第一个想到的。您还可以从总计数中减去真实的总和，例如:

SUM(has_object) AS count_true,
COUNT(*) - SUM(has_object) AS count_false