选择要在 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屋!