在联接表中选择不同的值 [英] Selecting distinct values in a joined table

问题描述

简化我要解决的问题，但我认为以下是要点:

Simplifying the problem I'm tackling somewhat, but I think the following is the gist of it:

我有两个表(TableA和TableB)，并由LEFT JOIN连接.

I have two tables (TableA and TableB), joined by a LEFT JOIN.

我需要以相反的时间顺序从TableA中获取十条记录；但是这十个结果中的每一个在TableB.foo中都具有唯一的值.

I need to get ten records from TableA in reverse chronological order; but with each of those ten results having a unique value in TableB.foo.

即

A.id是1，A.date是两天前，B.foo是'cat'.
A.id是2，A.date是昨天，B.foo是'cat'.
A.id是3，A.date是今天，B.foo是'dog'.
A.id是4，A.date是三天前，B.foo是猪".
A.id是5，A.date是四天前，B.foo是狗".

A.id is 1, A.date is two days ago, B.foo is 'cat'.
A.id is 2, A.date is yesterday, B.foo is 'cat'.
A.id is 3, A.date is today, B.foo is 'dog'.
A.id is 4, A.date is three days ago, B.foo is 'pig'.
A.id is 5, A.date is four days ago, B.foo is 'dog'.

我希望查询的前3个结果为3、2、4(并且根本不返回1和5).

I'd want the top 3 results of my query to be 3,2,4 (and 1 and 5 not to be returned at all).

这可以在单个MySQL查询中实现吗?

Can this be achieved in a single MySQL query?

如果失败了，那么分多个步骤获得所需结果集的最有效策略是什么?

Failing that, what would be the most efficient strategy for getting the desired resultset in multiple steps?

推荐答案

SELECT T1.id, T1.date
FROM (SELECT A.id id, B.foo foo, A.date date
      FROM A JOIN B
      ON A.b_id = B.id) T1
JOIN (
    SELECT B.foo foo, MAX(A.date) maxdate
    FROM A JOIN B
    ON A.b_id = B.id
    GROUP BY B.foo) T2
ON T1.foo = T2.foo and T1.date = T2.maxdate
ORDER BY T1.date DESC
LIMIT 10

这篇关于在联接表中选择不同的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！