MySQL查询:选择最新结果 - MySQL query: selecting latest results

MySQL query: selecting latest results

本文关键字：最新结果选择查询 MySQL | 更新日期: 2023-09-26

我有一个包含数据的MySQL表，并且想要为进行的特定测试选择最近的条目。

table: research
columns: id (int), projectid (int), test (varchar),
         when (datetime), result (longtext)

示例:

1, 6, "Test1", 01-01-2013 12:15, "AAAAA"
2, 6, "Test1", 01-01-2013 13:15, "BBBBB"
3, 6, "Test2", 01-01-2013 16:00, "CCCCC"
4, 6, "Test2", 01-01-2013 16:15, "DDDDD"
5, 6, "Test2", 01-01-2013 16:30, "EEEEE"

如果我想要"Test 1"的最新结果，我使用…

SELECT *
FROM research
WHERE projectid=6 AND test='Test1'
ORDER BY when DESC limit 1

但是我如何在一个查询中获得单个项目中每个测试的最新结果?我试着:

SELECT research.*
FROM research INNER JOIN (SELECT MAX(id) AS id
                          FROM research
                          WHERE projectid=".$ProjectId."
                          GROUP BY test) ids
     ON research.id = ids.id
WHERE research.projectid=".$ProjectId

但是它给我的不是最新的测试结果，它给我的是最古老的…

子查询背后的思想是，它分别获得每个投影和测试的最新日期WHEN。然后将结果连接回原始表。

SELECT  a.*
FROM    research a
        INNER JOIN
        (
            SELECT projectid, test, MAX(`when`) maxDate
            FROM research
            GROUP BY projectid, test
        ) b ON  a.projectid = b.projectid AND
                a.test = b.test AND
                a.`when` = b.maxDate

SQLFiddle演示/gh>

您可以使用WHERE和IN来过滤max(when)日期:

select *
from research r
where r.projectid = 6
   and r.`when` in (select max(`when`)
                   from research
                   group by projectid, test);

参见SQL Fiddle with Demo

试试这个:

SELECT test,when,result
FROM research r1
WHERE when = (SELECT max(when)
            FROM research r2
            WHERE r2.test=r1.test)

请尝试下面的查询。

SELECT `e`.* FROM (
SELECT `r`.*
FROM research `r`
ORDER By `r`.`when`
DESC
)`e`
WHERE `e`.`projectid`= $ProjectId GROUP BY `e`.`test`

我希望这对你有帮助。

谢谢。

因此，要获得每个项目的每个测试，具有最大when列的行，您可以使用以下命令:

SELECT r.*
FROM research AS r 
  JOIN
    ( SELECT projectid, test, MAX(`when`) AS maxw
      FROM research
      GROUP BY projectid, test
    ) AS m
    ON  m.projectid = r.projectid
    AND m.test = r.test
    AND m.maxw = r.`when` ;

对于单个项目(例如项目6)的每个测试:

SELECT r.*
FROM research AS r 
  JOIN
    ( SELECT test, MAX(`when`) AS maxw
      FROM research
      WHERE projectid = 6
      GROUP BY test
    ) AS m
    ON  m.test = r.test
    AND m.maxw = r.`when` 
WHERE r.projectid = 6 ;

在(projectid, test, when)上建立索引将有助于提高上述两个查询的效率。

按测试划分，按分区内日期排序。使用解析函数。