MYSQL 内部连接按日期时间升序排序

Question

我正在编写查询以获取所有客户信息并显示他们上次订购的时间。我当前的查询是:

SELECT
`user`.fname,
`user`.lname,
`user`.email,
`order`.orderplaced_ts,
COUNT(*) AS Total
FROM `order`
INNER JOIN `user` ON `order`.user_id = `user`.id
WHERE `order`.store_id = 10
AND `order`.orderplaced_ts > "2015-01-01 00:00:00"
AND `order`.order_status != "Cancelled"
GROUP BY `order`.user_id
ORDER BY `order`.orderplaced_ts

我在 ORDER BY order.orderplaced_ts 之后尝试了 ASC 和 DESC，但仍然没有返回我想要的结果。

编辑:当前结果

First   Last         Email                  Date                  Count
Lisa    Day          email@something.com    2015-01-08 13:16:31    105
Alex    Sullivan     email@something.com    2015-01-05 11:48:33    90
Jill    GORDON       email@something.com    2015-01-02 12:52:57    68
Bob     Jain         email@something.com    2015-03-23 12:39:24    62
Jack    Merkher      email@something.com    2015-01-05 11:19:31    56

日期字段显示的是他们第一次订购的时间，而我想要的是他们最后一次订购的时间。

期望的结果是:

    First   Last         Email                  Date                  Count
    Lisa    Day          email@something.com    2015-07-08 13:16:31    105
    Alex    Sullivan     email@something.com    2015-07-05 11:48:33    90
    Jill    GORDON       email@something.com    2015-07-02 12:52:57    68
    Bob     Jain         email@something.com    2015-07-23 12:39:24    62
    Jack    Merkher      email@something.com    2015-07-05 11:19:31    56

来自 SELECT * 的示例数据:

ID      User_ID Store_ID    orderplaced_ts
1104121 214831  10          2015-01-02 11:04:49
1104261 559281  10          2015-01-02 11:21:34
1104791 420461  10          2015-01-02 12:10:34
1105041 801481  10          2015-01-02 12:33:58
1105051 216501  10          2015-01-02 12:35:28
1105191 814661  10          2015-01-02 12:42:26
1105241 304571  10          2015-01-02 12:44:31
1105321 781611  10          2015-01-02 12:52:41
1105331 528411  10          2015-01-02 12:52:57
1105641 752171  10          2015-01-02 13:36:10
1106061 458791  10          2015-01-02 15:46:24

所以我要做的是获取所有用户、他们订购了多少次以及他们最近下的订单是多少。

Inner Join 只是检索用户电子邮件、名字和姓氏。

我解决问题的最终查询如下所示:

SELECT
    `user`.fname,
    `user`.lname,
    `user`.email,
    COUNT(*) AS Total_Orders,
    `order`.orderplaced_ts AS First_Order,
    MAX(order.orderplaced_ts) AS Last_Order
FROM `order`
INNER JOIN `user` ON `order`.user_id = `user`.id
WHERE `order`.store_id = 10
AND `order`.orderplaced_ts > "2015-01-01 00:00:00"
AND `order`.order_status != "Cancelled"
GROUP BY `order`.user_id ASC
ORDER BY `order`.orderplaced_ts 

Answer 1

所有不在 Group By 子句中的选定值必须具有聚合函数，如 sum 或 max。否则分组选择将无法知道要显示哪个值。

我建议按 user.fname、user.lname 和 user.email 而不是 order 对行进行分组。 user_id

根据@Uueerdo 的评论:按 MAX(order.orderplaced_ts) 排序会成功