如何以最大值获取不同的行 [英] how to get distinct rows with max value

问题描述

我很抱歉问什么问题一定很容易解决，但我似乎无法将我的想法包括在内。我甚至无法为我的问题想出一个真正合适的标题，请原谅你的赦免也就是说。

我有一项投票，其中每个用户可以发布多个问题答案，其他人则对这些答案进行投票。我需要得到一个结果，其中每个用户的最高票数答案都会被返回。

测试用例：假设一个问题，例如你最喜欢的歌曲报价是什么？

$ pre $ CREATE TABLE`answers`（
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY，
` authorId` INT，
`answer` TEXT NOT NULL，
`votes` INT NOT NULL
）ENGINE = MYISAM字符集utf8 COLLATE utf8_general_ci;

插入`answers` VALUE
（1，30，West is the Best，120），
（2,30，来吧，宝贝，亮我的火，100），
（3,31，一切都会好起来的，350），
（4,31，Sayin'oooh，我爱你，350），
（5,31，唱纯粹而真实的旋律甜蜜的歌曲，290），
（6,32，我是你的杆子，你穿的都是你的鞋子，540），
（7,32，当我和她在一起时，我更疯狂，180），
（8,32，你听到空中的音乐，230），
（9,30，你知道他们是骗子，190）

结果我希望得到的是：

  id | authorId |回答|票
 6 | 32 |我是你的杆子，你所穿的就是你的鞋子| 540 
 3 | 31 |一切都会好起来的350 
 9 | 30 |你知道他们是骗子| 190 
  

基本上我需要为每个作者选择最佳答案，然后通过投票排序结果最好的答案了。可能发生同一作者的两个答案具有相同数量的选票;那么应该只选择第一张（较低的ID）（如回答＃3和＃4所示）。结果中可能不会出现同一作者的两种不同答案 - 每位作者可能只会获胜一次。

我搜索并搜索并尝试并再次尝试并在那一刻我感觉自己被洗脑了......这可能是在单个SQL查询中无法实现的;应该是这样，可能值得指出的是应用程序是用PHP编写的。我知道我可以用 ORDER BY votes desc，id asc 来获取所有的答案，然后迭代结果，记住所有的 authorId s并用任何一行记录 authorId 我已经看到了，但是我需要获取一组记录，这可能会让人感到尴尬。 。如果我查出太多的行等，可能需要再次运行带有偏移量的查询）。但最终，如果单一查询解决方案过于复杂或根本没有任何解决方案，它可能是最好的解决方案。

有什么想法？ ：o）

解决方案

  SELECT id，authorId，answer，votes 
 FROM （SELECT id，authorId，answer，votes 
 FROM answers 
 ORDER BY votes DESC）AS h 
 GROUP BY authorId 
  

这个小巧的技巧是基于 GROUP BY 构建的，以检索每个案例的第一行。通常这是默认的 ORDER BY id ASC ，但通过这个子查询，每个 authorId 中的第一行与最高票。

注意：正如Iain Elder所说， t使用 ONLY_FULL_GROUP_BY 活动并且只能在MySQL中使用。由于缺乏确认此行为的文档，此解决方案在某种程度上不受支持。它适用于我，但对我来说一直很好。

这种方法仍然适用于最新的 MySQL on sqlfiddle 。

my apologies for asking what must be very simple to solve, but I just can't seem to wrap my mind around this.. I couldn't even come up with a really fitting title for my question, beg your pardons for that as well.

I have a poll where each user may post multiple answers to a question and others then vote on these answers. I need to get a result where the highest-voted answer of every user gets returned.

Test case: let's assume a question like "What is your most favourite song quote?"

CREATE TABLE `answers` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`authorId` INT,
`answer` TEXT NOT NULL ,
`votes` INT NOT NULL 
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO `answers` VALUES
(1, 30, "The West is the Best", 120),
(2, 30, "Come on, baby, light my fire", 100),
(3, 31, "Everything's gonna be allright", 350),
(4, 31, "Sayin' oooh, I love you", 350),
(5, 31, "Singing sweet songs of melodies pure and true", 290),
(6, 32, "I'm your pole and all you're wearing is your shoes", 540),
(7, 32, "And I'm crazier when I'm next to her", 180),
(8, 32, "You hear the music in the air", 230),
(9, 30, "You know they are a liar", 190)

The result I expect to get is:

id | authorId | answer                                             | votes
 6 |       32 | I'm your pole and all you're wearing is your shoes | 540
 3 |       31 | Everything's gonna be allright                     | 350
 9 |       30 | You know they are a liar                           | 190

Basically I need to select the best answer for each author and then sort the result by the votes the best answers got. It may happen that two answers by the same author have the same number of votes; then only the first one posted (lower ID) should be selected (as demonstrated with answers #3 and #4). Two different answers by the same author may never appear in the result - each author may only win once.

I've searched and searched and tried and tried again and at the moment I feel pretty brainwashed.. it may be that this is not doable in a single SQL query; should that be the case, it might be worth pointing out that the application is written in PHP. I know I could just grab all the answers with ORDER BY votes desc, id asc and then iterate over the result, remember all the authorIds and chuck out any row with an authorId I've already seen, but I need to obtain a set number of records and that could get awkward (... would possibly need running the query again with an offset if I chuck out too many rows etc).. but in the end it might be the best solution if the single-query solution was overly complex or none at all...

Any ideas? :o)

解决方案

SELECT id, authorId, answer, votes
FROM (  SELECT id, authorId, answer, votes
        FROM answers
        ORDER BY votes DESC) AS h
GROUP BY authorId

This little neat trick is built basing on GROUP BY to retrieve the first row of each case. Usually this is by default ORDER BY id ASC, but through this sub query, the first row in each authorId with the highest votes.

Note: As mentioned by Iain Elder, this solution doesn't work with ONLY_FULL_GROUP_BY active and only works in MySQL. This solution is to a certain extent unsupported due to lack of documentation confirming this behavior. It works well for me and has always worked well for me however.

This method still works on the latest MySQL on sqlfiddle.

这篇关于如何以最大值获取不同的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！