查询列出一个商店的客户，有两个不同的订单表

Question

我需要sql帮助查询： 我想创建一个视图来列出一个商店的客户。这是选择了一个或多个预订（表预订）或已经完成一个或多个预订（餐桌shop_orders）的用户表。

------------- -------------- 
| users: | | shops:  | 
------------- -------------- 
| id (PK) | | id (PK) | 
------------- -------------- 

------------------ ---------------- 
| reservations: | | shops_orders:| 
------------------ ---------------- 
| id (PK)  | | id (PK)  | 
| user_id (FK) | | user_id (FK) | 
| shop_id (FK) | | shop_id (FK) | 
------------------ ---------------- 

这是我的出发点工作确定：

SELECT u.*, 
     r.shop_id, 
     (SELECT count(*) 
     FROM reservations r 
     WHERE r.user_id = u.id) AS num_reservations, 
     (SELECT count(*) 
     FROM shop_orders so 
     WHERE so.user_id = u.id) AS num_orders 
FROM users u 
INNER JOIN (SELECT r.user_id, 
        r.shop_id 
      FROM reservations r 
      UNION 
      SELECT so.user_id, 
        so.shop_id 
      FROM shop_orders so) AS r ON u.id = r.user_id 
GROUP BY u.id 

所有查询这一观点将被shop_id进行过滤，只显示一个具体店铺的客户（被店铺管理区域） 。

且只有已经预订或订单客户选择（num_reservations> 0或NUM_ORDERS> 0）

我觉得这个问题是性能：从整个表2个计数（*）。还有这两个整个表格之间的联合。

您是否认为我可以测试更好的替代方法？

Answer 1

你可以这样说：

SELECT 
    u.*, 
    r.shop_id, 
    IFNULL(COUNT(DISTINCT r.id), 0) AS num_reservations, 
    IFNULL(COUNT(DISTINCT o.id), 0) AS num_orders 
FROM users u 
    LEFT JOIN reservarions r ON r.user_id = u.id 
    LEFT JOIN shop_orders o ON o.user_id = u.id 
GROUP BY u.id 
HAVING num_reservations + num_orders > 0 

另一种方式，应该是更快：

SELECT 
    u.*, 
    r.shop_id, 
    IFNULL(COUNT(DISTINCT r.id), 0) AS num_reservations, 
    IFNULL(COUNT(DISTINCT o.id), 0) AS num_orders 
FROM users u 
    LEFT JOIN reservarions r ON r.user_id = u.id 
    LEFT JOIN shop_orders o ON o.user_id = u.id 
WHERE r.id IS NOT NULL OR o.id IS NOT NULL 
GROUP BY u.id 

如果你需要把它拆分通过他们使用的用户和卖场，做GROUP BY u.id, r.shop_id 。