主页 / dba / 问题 / 274621
Accepted
newbie
Asked: 2020-08-30 10:14:00 +0800 CST

Mysql 查询 group by 与 order by desc 结合

  • 772

我有以下查询。

SELECT Day(vehicleTrip.startDateTime) As day, 
       Month(vehicleTrip.startDateTime) As month,
       Sum(TIMESTAMPDIFF(SECOND, startDateTime, endDateTime )) As totalDuration,
       count(vehicleTrip.startDateTime) As totalTrip, 
       sum(endMileage-startMileage) As totalMleage
FROM vehicleTrip                            
WHERE vehicleTrip.vehicleID=:vehicleID
  AND vehicleTrip.vehicleEndCodeID!=1
  AND vehicleTrip.startDateTime BETWEEN :startDateTime And :endDateTime                             
Group By Day(vehicleTrip.startDateTime),Month(vehicleTrip.startDateTime)

目前它工作正常，因为它可以按每日日期分组duration, totalTrip, totalMileage etc.但唯一的问题是我希望日期由 desc 安排。我试过按它给出的 startDateTime desc 下订单me error that its not in GROUP By clause and containts nonaggregated column。我已经尝试了很多方法，比如把它Max(vehicleTrip.startDateTime)还是一样的。我怎样才能最好地解决这个问题？

下面是我的表格设计及其索引。

CREATE TABLE `vehicleTrip` (
  `vehicleEndCodeID` tinyint NOT NULL DEFAULT '1',
  `vehicleID` mediumint NOT NULL,
  `startDateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `endDateTime` timestamp NULL DEFAULT NULL,
  `startMileage` float(11,3) DEFAULT NULL,
  `endMileage` float(11,3) DEFAULT NULL,
  `startLatitude` float(10,8) NOT NULL,
  `startLongitude` float(11,8) NOT NULL,
  `endLatitude` float(10,8) DEFAULT NULL,
  `endLongitude` float(11,8) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


ALTER TABLE `vehicleTrip`
  ADD PRIMARY KEY (`vehicleID`,`startDateTime`) USING BTREE;
COMMIT;
mysql group-by

1 个回答

  1. Best Answer

    也许这就是你要找的？

    SELECT Day(startDateTime) As "day", 
       Month(startDateTime) As "month",
       Sum(TIMESTAMPDIFF(SECOND, startDateTime, endDateTime ))
             As totalDuration,
       count(*) As trips, 
       sum(endMileage-startMileage) As totalMleage
FROM vehicleTrip                            
WHERE vehicleID = :vehicleID
  AND vehicleEndCodeID != 1
  AND startDateTime BETWEEN :startDateTime And :endDateTime                             
Group By 2, 1
ORDER BY 2 DESC, 1 DESC

    （我假设您想按月和日排序，最新的优先？）

    还要考虑

    GROUP BY DATE(startDateTime)
ORDER BY DATE(startDateTime) DESC

    更多的

    !=很难优化。我没有看到任何避免“文件排序”的方法，但这个索引可能有助于提高性能：

    INDEX(vehicleID, startDateTime)   -- in this order

    但是，既然那已经是PRIMARY KEY，请不要添加 this INDEX。

    重新来

    现在CREATE TABLE已经添加了（这就是我经常要求的原因），这就是我将如何回答这个问题：

    如果可能，请与 . 这允许优化器同时进行。在您的问题中，您在每个问题中的内容并不完全相同。ORDER BYGROUP BY

    一种变体是改变方向：

    GROUP BY a     , b
ORDER BY a DESC, b DESC

    也是最优的。

    （此处不相关：）在大多数情况下，列上的 ASC 和 DESC 的任何混合ORDER BY都会阻止ORDER BY.

    我使用DATE(..)而不是两列的建议避免了这个问题并解决了明年一月出现的潜在错误。但是......它可能会遇到“唯一完整的分组依据”问题，因为DAY(..), MONTH(..)它不被识别为 `DATE(..)

    为了避免上述一些混乱，我首先建议

    GROUP BY 2     , 1
ORDER BY 2 DESC, 1 DESC

    如果这失败了，我会从袖子里拿出一个子查询。

    • 1

相关问题