SQL交叉查询

Question

这个查询:

SELECT order_id, count(shipments.order_id) 
    FROM shipments
    GROUP by order_id
    ORDER by count(shipments.order_id) DESC
    LIMIT 25

结果:

ID   Order ID   Count
    1   7   70
    2   1730    65
    3   359 50

它返回出现最频繁的 order_id 列表以及它们出现的次数。但是，当我尝试将其与“用户”表链接以返回相应用户的姓名和电子邮件地址时，我得到了完全不同的结果:

SELECT users.first_name, users.email, count(shipments.order_id) 
    FROM users, shipments
    WHERE shipments.order_id = users.current_order_id
    GROUP by users.first_name, users.email
    ORDER by count(shipments.order_id) DESC
    LIMIT 25

结果

ID     Name    Email    OrderID   Count
1   Jane    xxx@hotmail.com 1264    31
2   Tom yyy@yahoo.co.uk 98  28
3   Foo nnn@gmail.com   3203    28

附言发货中使用的订单 ID 是用于位于用户中的经常性(当前)订单的 ID。

Answer 1

尝试将用户表左联入发货计数

    SELECT users.first_name, users.email, count(shipments.order_id) 
    FROM shipments
    LEFT JOIN USERS ON SHIPMENTS.ORDER_ID=USERS.ORDER_ID
    WHERE shipments.order_id = users.current_order_id
    GROUP by users.first_name, users.email
    ORDER by count(shipments.order_id) DESC

我也成功地做了这样的事情:

    SELECT order_id, count(shipments.order_id) 
    into #SHIP
    FROM shipments
    GROUP by order_id
    ORDER by count(shipments.order_id) DESC
    LIMIT 25

    SELECT users.first_name, users.email, (#ship.order_id) 
    FROM #ship
    LEFT JOIN USERS ON #ship.ORDER_ID=USERS.ORDER_ID
    GROUP by users.first_name, users.email
    ORDER by count(shipments.order_id) DESC