所有值都相同的 MySQL GROUP_CONCAT

Question

我有以下查询:

SELECT * FROM charges WHERE (
  charges.id not in (
    select charge_id from billing_invoice_charges where is_deactivated = 0
  )
)

我需要将它转换成 JOIN QUERY，所以我正在尝试:

SELECT charges.id, group_concat(bic.is_deactivated) AS active_statuses 
FROM charges LEFT JOIN billing_invoice_charges AS bic
ON bic.charge_id = charges.id GROUP BY charges.id

HAVING .......; <--- Check if all values are 1's

GROUP_CONCAT 的输出是:

+------+-----------------+
| id   | active_statuses |
+------+-----------------+
|    2 | 0,1,1           |
|    3 | 1,1             |
|    6 | 1               |
|    7 | 1,1,1           |
|   12 | 0,0,1           |
+------------------------+

如果 HAVING 子句中的 active_statuses 是否为 1，我如何检查所有值？这应该会给我正在寻找的 charges。

Answer 1

尝试使用条件聚合的 HAVING 子句来确保每个组都不会出现非 1 状态。

SELECT
    charges.id,
    GROUP_CONCAT(bic.is_deactivated) AS active_statuses 
FROM charges
LEFT JOIN billing_invoice_charges AS bic
    ON bic.charge_id = charges.id
GROUP BY charges.id
HAVING SUM(CASE WHEN bic.is_deactivated <> 1 THEN 1 ELSE 0 END) = 0