mysql 10万级join,mysql：如何INNER JOIN表，但限制联接到具有最高投票或计数的1结果？...

I have 2 tables. One is items and another is votes for those items.

Items table has: |item_id|name|post_date

Votes table has: |votes_id|item_id|answer|total_yes|total_no

What I want to do is show all items based on post_date and show the answer in the votes table with the HIGHEST total_yes. So I want to show only a SINGLE answer from the votes table with the highest total_yes vote.

I was trying:

SELECT a.*, b.* FROM Items a

INNER JOIN Votes b ON a.item_id = b.item_id

GROUP by a.item_id

ORDER by a.post_date DESC, b.total_yes DESC

But that doesnt work.

The result I would like to see is:

Buick | Fastest | 2 yes votes

Mercedes | Shiny | 32 yes votes

Honda | Quick | 39 yes votes

Any help is appreciated!

解决方案SELECT a.*, b.*

FROM Items a

LEFT JOIN Votes b on a.item_id = b.item_id

and b.total_yes = (select max(total_yes)

from Votes v

where v.item_id = a.item_id)

ORDER BY a.post_date DESC, b.total_yes DESC

N.B.: if you have for an item 2 answers with the same total_yes = max, you will have 2 rows for that item.