我有两个表（客户和订单）包含：

// Customer Table
customer_id     customer_name     customer_address
     1            customer01          address01
     2            customer02          address02
     3            customer03          address03
     4            customer04          address04
     5            customer05          address05

// Orders Table
order_id    customer_id    order_status
    1             1           rejected
    2             1           success
    3             2           success
    4             1           success
    5             1           pending
    6             2           success
    7             2           pending
    8             3           pending

所以，在我的订单表中：
客户01 2 success , 1 rejected 以及 1 pending 客户02 2 success 以及 1 pending customer03只有 1 pending 期望：我需要的结果

customer_id   Customer_name (success)   order_status
     1           customer01 (2)           pending
     2           customer02 (2)           pending
     3           customer03 (0)           pending

我的查询：在sqlfiddle上使用示例数据

SELECT o.order_id, c.customer_id, CONCAT(c.customer_name, " (", (SELECT COUNT(order_id) FROM orders WHERE order_status = 'success'),")") as "Customer Name (success order)", o.order_status
FROM customer c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_status = 'pending'
ORDER BY c.customer_id ASC;

结果

customer_id   Customer_name (success)   order_status
     1           customer01 (4)           pending
     2           customer02 (4)           pending
     3           customer03 (4)           pending

编辑：删除sql fiddle。