php - MySQL降序问题

Question

我的查询:

$strSQL = "Select customers.*, count(ordersbase.OrderID) As Orders, ordersbase.OrderTime
        From customers
        Inner Join ordersbase On customers.ID = ordersbase.CustomerID
        Group By customers.ID
        Order By customers.ID, ordersbase.OrderTime Desc;";

订单表:

我希望得到 OrderTime 值作为 20181008000000(降序排列的最大值)但它返回另一个值:

    "0": {
        "Orders": 2,
        "OrderTime": "20181006000000"
    },

Answer 1

• 数据以无序方式存储。 Group By 中的任何非聚合列(在功能上不依赖于 Select 中的列)子句，也没有在 GROUP BY 中明确指定子句，是不确定的。您需要使用 Max() 获取最新订单时间的功能。请阅读:SELECT list is not in GROUP BY clause and contains nonaggregated column .... incompatible with sql_mode=only_full_group_by

• Order by在分组完成后进行处理。所以，你对 Order by 的使用不起作用。

请尝试以下操作:

$strSQL = "Select customers.*, 
                  count(ordersbase.OrderID) As Orders, 
                  MAX(ordersbase.OrderTime) 
        From customers
        Inner Join ordersbase On customers.ID = ordersbase.CustomerID
        Group By customers.ID
        Order By customers.ID";