在这里，我分享了我的查询。所有的总值都给出了错误的结果，因为非常大的值不是所需的 COUNT 值。我的查询有什么问题？
SELECT bd.business_id,
bd.business_name,
bd.business_address,
bd.created_at,
bd.updated_at AS extent_expiry_date,
bd.manager_id as user_id,
COUNT(m.manager_id) AS total_managers,
COUNT(a.agent_id) AS total_agents,
COUNT(c.customer_id) AS total_customers,
COUNT(t.task_id) AS total_tasks,
COUNT(t.order_id) AS total_orders
FROM business_details bd
LEFT JOIN managers m ON m.business_id = bd.business_id
LEFT JOIN agents a ON a.business_id = bd.business_id
LEFT JOIN customers c ON c.business_id = bd.business_id
LEFT JOIN tasks t ON t.business_id = bd.business_id
GROUP BY bd.business_id
由于您的问题中没有样本数据集，我的猜测是您多次获得相同的 manager_id,agent_id... 由于左连接以获得正确的计数使用不同
SELECT bd.business_id,
bd.business_name,
bd.business_address,
bd.created_at,
bd.updated_at AS extent_expiry_date,
bd.manager_id as user_id,
COUNT(distinct m.manager_id) AS total_managers,
COUNT(distinct a.agent_id) AS total_agents,
COUNT(distinct c.customer_id) AS total_customers,
COUNT(distinct t.task_id) AS total_tasks,
COUNT(distinct t.order_id) AS total_orders
FROM business_details bd
LEFT JOIN managers m ON m.business_id = bd.business_id
LEFT JOIN agents a ON a.business_id = bd.business_id
LEFT JOIN customers c ON c.business_id = bd.business_id
LEFT JOIN tasks t ON t.business_id = bd.business_id
GROUP BY bd.business_id
还包括分组中的所有非聚合列，因为根据新版本，大多数 RDBMS 拒绝这些类型的查询
GROUP BY
bd.business_id,
bd.business_name,
bd.business_address,
bd.created_at,
bd.updated_at,
bd.manager_id
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句