MySQL单查询内连接（非GROUP BY）实现双条件count(id)统计

架构老林

2026-5-25

Solution Query

Got it, let's work through this. Here's the MySQL query that checks all your boxes — uses INNER JOIN, skips the GROUP BY clause, and counts orders by each status for customers who have placed orders:

SELECT DISTINCT
    c.customer_id,
    c.customer_name,
    (SELECT COUNT(order_id) FROM orders o2 WHERE o2.customer_id = c.customer_id AND o2.order_status = 'success') AS success_count,
    (SELECT COUNT(order_id) FROM orders o2 WHERE o2.customer_id = c.customer_id AND o2.order_status = 'rejected') AS rejected_count,
    (SELECT COUNT(order_id) FROM orders o2 WHERE o2.customer_id = c.customer_id AND o2.order_status = 'pending') AS pending_count
FROM customer c
INNER JOIN orders o ON c.customer_id = o.customer_id;

Breakdown of how this works:

INNER JOIN: We join customer with orders to only include customers who have at least one order (so customer04 and customer05 get excluded, which aligns with inner join behavior).
DISTINCT: Since the inner join would return one row per order, we use DISTINCT to collapse duplicate customer rows into a single entry per customer.
Correlated Subqueries: Each subquery calculates the count of orders for a specific status tied to the current customer in the main query. This lets us get per-customer status counts without needing GROUP BY.

When you run this, you'll get a result set that matches the stats you listed: