选择要在 GROUP BY 行上返回的结果 [英] choose which result to return on GROUP BY row

问题描述

考虑以下查询:

SELECT domain, done FROM tasks WHERE 1 GROUP BY domain

显然我可以用 MAX(done) 让它返回done"的最大值，但我如何让它返回整行?

Obviously I can get it to return the max value of "done" with MAX(done), but how do i get it to return that whole row?

在我所处的情况下，我有一个包含以下行的表格:

In the situation I'm in, I have a table with the following rows:

domain = somedomain.com, done = 12:27:39, somecolumn = pink
domain = somedomain.com, done = 13:27:39, somecolumn = blue
domain = somedomain.com, done = 14:27:39, somecolumn = orange
domain = anotherdomain.com, done = 07:27:39, somecolumn = orange
domain = anotherdomain.com, done = 09:27:39, somecolumn = pie

使用上面的查询，它返回以下 2 行:

Using the query above, it returns the following 2 rows:

domain = somedomain.com, done = 13:27:39, somecolumn = blue
domain = anotherdomain.com, done = 09:27:39, somecolumn = pie

我需要返回这两行:

domain = somedomain.com, done = 14:27:39, somecolumn = blue
domain = anotherdomain.com, done = 09:27:39, somecolumn = pie

感谢帮助:)

推荐答案

最简单的一个:

SELECT done,somecolumn FROM tasks ORDER BY done DESC LIMIT 1;

对于更复杂的查询，可能需要左连接或子查询，但这个非常简单.

For more elaborate queries, left joins or subqueries may be needed, but this one is pretty straight forward.

顺便说一句，如果您确实需要其他数据:

For the heck of it, if you indeed need other data:

(1) 不存在:

 SELECT a.*
 FROM tasks a
 WHERE NOT EXISTS(
    SELECT * FROM tasks b
    WHERE b.domain = a.domain AND b.done > a.done);

(2) 左连接

 SELECT a.*
 FROM tasks a
 LEFT JOIN tasks b
 ON b.domain = a.domain AND b.done > a.done
 WHERE b.id IS NULL;

(3) ROWNUMBER() 喜欢:

(3) ROWNUMBER() like:

SELECT numbered.*
FROM (
    SELECT 
        @row := IF(@old_domain = domain,@row + 1,1) as rownumber,
        @old_domain := domain as domain,
        done,
        somecolumn
    FROM tasks
    JOIN (SELECT @old_domain:=0,@row:=0) void
    ORDER BY domain ASC, done DESC
) numbered
WHERE numbered.rownumber = 1;

哪一个会给你更好的性能很大程度上取决于数据集，所以我只是测试一下.

Which one will give you better performance hugely depends on the dataset, so I'd just test those.

这篇关于选择要在 GROUP BY 行上返回的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！