SQL-选择最低的分组依据和排序依据? [英] SQL - Select lowest values with group by and order by?
问题描述
在我的rankings数据库中，我有一个名为times的表.我还有另一张与作者的桌子.作者具有作者ID(在times表中名为ath_id).
In my rankings database I have a table named times. I also have another table with authors. The authors have author id's (named ath_id inside the times table).
id ath_id brand_id time date
------------- ------------ -------------- -------------- --------------
65125537 5384729 3 44741 May 8 2014
72073658 4298584 1 1104 Jun 28 2015
86139060 4298584 2 2376 Nov 20 2016
92237079 4298584 1 1115 Jun 24 2017
92237082 4298584 1 1104 Jun 24 2017
93436362 5384729 12 376492 Dec 31 2012
我想实现的目标
我想检索属于作者的时间的有序列表(按作者ID).我想按brand_id对其进行排序，而我只希望time值最低的记录.
What I want to achieve
I'd like to retrieve an ordered list of the times that belong to the author (by the author id). I'd like to order them by brand_id, and I only want the records with the lowest time value.
另外，当有多个记录具有相同的brand_id 和相同的time值时，我希望按日期对列表进行排序.因此，具有最新日期的记录将是最后一个.
Also, when there are multiple records with the same brand_id and the same time value, I'd like the list to be ordered by date. So the record with the latest date will be last.
我当前正在使用以下查询:SELECT * FROM times WHERE ath_id = 4298584 GROUP BY brand_id ASC.
I currently use this query: SELECT * FROM times WHERE ath_id = 4298584 GROUP BY brand_id ASC.
虽然效果很好，但它会将具有相同brand_id的记录限制为1，从而即使具有多个time值最低的记录也具有相同的time记录.
It works great, but it limits records with the same brand_id to 1, and thereby it limits records with the same time, even when multiple records have the lowest time value.
因此，在上面的示例中.当我选择所有带有ath_id = 4298584的记录时，我想检索以下有序列表:
So in the case of the example above. When I select all the records with ath_id = 4298584, I'd like to retrieve the following ordered list:
id ath_id brand_id time date
------------- ------------ -------------- -------------- --------------
72073658 4298584 1 1104 Jun 28 2015
92237082 4298584 1 1104 Jun 24 2017
86139060 4298584 2 2376 Nov 20 2016
这是我第一次进行更高级的SQL查询.我正在使用Laravel，因此使用Laravel查询生成器同时提供原始SQL解决方案和Laravel解决方案都不会造成任何危害.
This is my first time doing a bit more advanced SQL queries. I'm working with Laravel, so giving both a raw SQL solution and a Laravel solution using the Laravel Query Builder wouldn't do any harm.
推荐答案
您可以尝试使用派生表来获取ath_id和brand_id的最短时间.然后将其重新连接到原始表以获取其余数据.
You could try using a derived table to get the min time for an ath_id and brand_id. Then join it back to your original table to get the rest of the data.
SELECT t.*
FROM times t
JOIN (SELECT ath_id, brand_id, MIN(time) AS time FROM dbo.times GROUP BY ath_id, brand_id) b
ON t.ath_id = b.ath_id AND t.brand_id = b.brand_id AND t.time = b.time
WHERE t.ath_id = 4298584
ORDER BY t.brand_id ASC, t.date DESC
这篇关于SQL-选择最低的分组依据和排序依据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
