mysql max datetime_返回的數據是Max Datetime和不同的標識不同的行--MYSQL

delimiter $$

CREATE TABLE `tbl_locate` (

`ID` int(11) NOT NULL AUTO_INCREMENT,

`Locating` bit(1) DEFAULT NULL,

`MainPower` bit(1) DEFAULT NULL,

`Acc` bit(1) DEFAULT NULL,

`PowerOff` bit(1) DEFAULT NULL,

`Alarm` int(11) DEFAULT NULL,

`Speed` int(11) DEFAULT NULL,

`Direction` int(11) DEFAULT NULL,

`Latitude` double DEFAULT NULL,

`Longitude` double DEFAULT NULL,

`DateTime` datetime DEFAULT NULL,

`MainID` int(11) DEFAULT NULL,

`IOState` int(11) DEFAULT NULL,

`OilState` int(11) DEFAULT NULL,

PRIMARY KEY (`ID`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1$$

SQL STRING

SELECT * FROM tbl_locate WHERE DateTime=(SELECT MAX(`DateTime`) FROM tbl_locate WHERE MainID=1,2,3)

i want to return the data which is latest row where main id are 1 or 2 or 3,

so,need to return 3 rows latest datetime.

1st row are id 1 latest datetime

second row are id 2 latest datetime

third row are id 3 latest datetime

我想返回主id為1或2或3的最新行數據,因此,需要返回3行最新日期時間。第1行是id 1最新日期時間第2行是id 2最新日期時間第3行是id 3最新日期時間

How to do it?

怎么做?

4 个解决方案

#1

1

SELECT *

FROM tbl_locate AS a

INNER JOIN

(

SELECT MainID, Max(DateTime) AS DateTime

FROM tbl_locate

GROUP BY MainID

) AS b

ON a.MainID = b.MainID

AND a.DateTime = b.DateTime

WHERE b.MainID in(1,2,3)

try this

#2

0

Order it by DateTime, and get first 3 row:

SELECT * FROM tbl_locate ORDER BY DateTime LIMIT 3

按DateTime排序,並獲得前3行:SELECT * FROM tbl_locate ORDER BY DateTime LIMIT 3

#3

0

Try this:

SELECT

T1.*

FROM tbl_locate T1

LEFT JOIN tbl_locate T2

ON T1.MainID = T2.MainID

AND T1.`DateTime` < T2.`DateTime`

WHERE T2.Id IS NULL

AND MainID IN (1,2,3)

#4

0

This should work:

這應該工作:

SELECT *

FROM tbl_locate t

JOIN (SELECT Max(DateTime) MaxDateTime, MainId

FROM tbl_Locate

WHERE MainId IN (1,2,3)

GROUP BY MainId) t2

ON t.MainId = t2.MainId AND t.DateTime = t2.MaxDateTime

This should return you 3 rows, one each for Main ID 1,2,3 with their corresponding Max(DateTime). However, if multiple rows exist with the same DateTime, then it will return more than one record.

這應該返回3行,每個行對應一個主ID 1,2,3及其對應的Max(DateTime)。但是,如果存在多個具有相同DateTime的行,則它將返回多個記錄。

以下是小提琴示例:http://sqlfiddle.com/#!2 / f2c49 / 1

In case multiple records exist with the same date, you need to introduce a RANK to ensure you only return a single record for each MainId:

如果存在多個具有相同日期的記錄,則需要引入RANK以確保只返回每個MainId的單個記錄:

SELECT *

FROM (

SELECT

@rowNumber:= IF(@prevRow=t.MainId, @rowNumber:=@rowNumber+1, 1) rank,

t.* ,

@prevRow:=t.MainId

FROM tbl_locate t

JOIN (SELECT

Max(DateTime) MaxDateTime, MainId

FROM tbl_Locate

WHERE MainId IN (1,2,3)

GROUP BY MainId) t2

ON t.MainId = t2.MainId AND t.DateTime = t2.MaxDateTime

JOIN (SELECT @rowNumber:= 0) t3

) t

WHERE rank = 1;