mysql - 如何连接依赖于外部组的计数

Question

我有一个看起来像这样的表格:

id | mid | oid
 1 | 400 | 1
 2 | 400 | NULL
 3 | 401 | 1
 4 | 403 | NULL

我正在尝试返回 MID、具有 null OID 的记录数以及具有 NOT null oid 的记录数

目前我正在使用这个:

SELECT 
(SELECT COUNT(id) FROM drp_ledger WHERE oid IS NULL) AS que,
(SELECT COUNT(id) FROM drp_ledger WHERE oid IS NOT NULL) AS finished, 
`mid` FROM drp_ledger GROUP BY `mid`

但是，这会返回 NULL 和 NOT NULL oid 的全部计数。

然后我尝试使用 JOIN 我觉得我已经很接近了，但我就是无法让它工作。

SELECT l.`mid`, que, finished FROM drp_ledger AS l
JOIN (SELECT COUNT(id) AS que FROM drp_ledger WHERE oid IS NULL GROUP BY `mid`) AS q ON q.`mid` =  l.`mid`
JOIN (SELECT COUNT(id) AS finished FROM drp_ledger WHERE oid IS NOT NULL GROUP BY `mid`) AS f ON f.`mid` = l.`mid`

我想返回:

mid | que | finished
400 |  1  |   1
401 |  0  |   1
403 |  1  |   0

Answer 1

无需加入!

SELECT
  mid,
  SUM(IF(oid IS NULL,1,0)) AS oid_null,
  SUM(IF(oid IS NOT NULL,1,0)) AS oid_not_null
FROM tablename
GROUP BY mid