MySQL - 查询和子查询之间的交互，可能吗？

Question

让我们假设这个复杂的查询；

SELECT cl.id, cl.nombre, 
    (SELECT count(*) FROM (SELECT count(ac.id)
    FROM acciones ac
    LEFT JOIN inter_clientes_acciones ia ON ac.id = ia.id_accion
    LEFT JOIN clientes cl ON ia.id_cliente = cl.id
    WHERE fecha_fin < CURDATE() AND ac.id_proyecto = 3 AND id_cliente = 3
    GROUP BY ac.id
    HAVING count(*) > 1) tabla_ac_grupales) as ac_grupales,
    (SELECT count(*) FROM (SELECT count(ac.id)
    FROM acciones ac
    LEFT JOIN inter_clientes_acciones ia ON ac.id = ia.id_accion
    LEFT JOIN clientes cl ON ia.id_cliente = cl.id
    WHERE fecha_fin < CURDATE() AND ac.id_proyecto = 3 AND id_cliente = 3
    GROUP BY ac.id
    HAVING count(*) = 1) tabla_ac_individuales ) as ac_individuales
FROM inter_clientes_acciones ia
JOIN clientes cl ON ia.id_cliente = cl.id
JOIN acciones ac ON ia.id_accion = ac.id
WHERE cl.id = 3 AND asistencia = 1 AND (ia.id_accion IN (
    SELECT ac.id as id_accion
    FROM acciones ac
    LEFT JOIN inter_clientes_acciones ia ON ac.id = ia.id_accion
    LEFT JOIN clientes cl ON ia.id_cliente = cl.id
    WHERE fecha_fin < CURDATE() AND ac.id_proyecto = 3
    GROUP BY ac.id
    HAVING count(*) > 1
 ) OR ia.id_accion IN (
    SELECT ac.id as id_accion
    FROM acciones ac
    LEFT JOIN inter_clientes_acciones ia ON ac.id = ia.id_accion
    LEFT JOIN clientes cl ON ia.id_cliente = cl.id
    WHERE fecha_fin < CURDATE() AND ac.id_proyecto = 3
    GROUP BY ac.id
    HAVING count(*) = 1  
 )
)
GROUP BY cl.id

如您所见，我们有 2 个子查询，它们计算另一个子查询的行数，并返回与 where 子句(其中我们有另一个子查询)匹配的所有客户端以及该客户端参与的操作数。事实上，我在查询和子查询的where子句中标记了cl.id = 3。我的问题是，如何在不指定客户端 ID 的情况下执行查询和子查询？我想要的是获得所有客户及其号码。

这可以通过查询来完成还是我必须执行存储过程？另外，如果您知道更好的方法，请随时说出来。

架构操作:

id
(another non important fields)

架构客户端:

id
(another non important fields)

架构 inter_clients_actions:

id
id_client
id_action
attend (Boolean)

Answer 1

这不行吗

WHERE cl.id = 3 AND asistencia = 1 AND (ia.id_accion IN (
    SELECT ac.id as id_accion
    FROM acciones ac
    LEFT JOIN inter_clientes_acciones ia ON ac.id = ia.id_accion
    LEFT JOIN clientes cl ON ia.id_cliente = cl.id
    WHERE fecha_fin < CURDATE() AND ac.id_proyecto = 3
    GROUP BY ac.id
    HAVING count(*) > 1
 ) OR ia.id_accion IN (
    SELECT ac.id as id_accion
    FROM acciones ac
    LEFT JOIN inter_clientes_acciones ia ON ac.id = ia.id_accion
    LEFT JOIN clientes cl ON ia.id_cliente = cl.id
    WHERE fecha_fin < CURDATE() AND ac.id_proyecto = 3
    GROUP BY ac.id
    HAVING count(*) = 1  
 )
)

简单地说:

WHERE cl.id = 3 AND asistencia = 1 AND (ia.id_accion IN (
    SELECT ac.id as id_accion
    FROM acciones ac
    LEFT JOIN inter_clientes_acciones ia ON ac.id = ia.id_accion
    LEFT JOIN clientes cl ON ia.id_cliente = cl.id
    WHERE fecha_fin < CURDATE() AND ac.id_proyecto = 3
    GROUP BY ac.id
    HAVING count(*) >= 1
 )
)

而且也非常简单:

AND id_cliente = 3

是

AND id_cliente = cl.id

最终查询:

SELECT cl.id, cl.nombre, 
    (SELECT count(*) FROM (SELECT count(ac.id)
    FROM acciones ac
    LEFT JOIN inter_clientes_acciones ia ON ac.id = ia.id_accion
    LEFT JOIN clientes cl ON ia.id_cliente = cl.id
    WHERE fecha_fin < CURDATE() AND ac.id_proyecto = 3 AND id_cliente = cl.id
    GROUP BY ac.id
    HAVING count(*) >= 1) tabla_ac_grupales) as res_all
FROM inter_clientes_acciones ia
JOIN clientes cl ON ia.id_cliente = cl.id
JOIN acciones ac ON ia.id_accion = ac.id
WHERE asistencia = 1 AND (ia.id_accion IN (
    SELECT ac.id as id_accion
    FROM acciones ac
    LEFT JOIN inter_clientes_acciones ia ON ac.id = ia.id_accion
    LEFT JOIN clientes cl ON ia.id_cliente = cl.id
    WHERE fecha_fin < CURDATE() AND ac.id_proyecto = cl.id
    GROUP BY ac.id
    HAVING count(*) >= 1
 )
)
GROUP BY cl.id

当 res_all > 1 时，则分组，当 `res_all = 1' 时，则单独。