MySQL GROUP_CONCAT具有相同的所有值。

I have the following query:

我有以下查询:

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

I need to convert it into a JOIN QUERY, so I'm trying:

我需要把它转换成一个连接查询，所以我在尝试:

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

The output of GROUP_CONCAT is:

GROUP_CONCAT的输出为:

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

How can I check if all the values if active_statuses in HAVING clause are 1's? This should give me the charges I'm looking for.

我如何检查在HAVING子句中active_statuse是否为1的所有值?这应该是我要找的费用。

2 个解决方案

#1

Try a HAVING clause which uses conditional aggregation to ensure that no non 1 statuses occur for each group.

尝试使用有条件聚合的子句，以确保每个组都不会出现非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

#2

Try:

试一试:

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 active_statuses NOT LIKE '%0%'

which should ignore any results with a 0 in active_statuses

在active_statuse中，哪些结果应该忽略任何结果?

#1