mysql - 在具有 6 列的连接中获取两列的不同记录

Question

我有两个 MySQL 表 SPONSORSHIPS 和 EVENTS。我想显示按他们赞助的事件类别排序的赞助列表，但在每个事件下只显示一次赞助。连接表示例:

SPONSORSHIPS
sponsorhipid | sponsorid | eventid | date       | 
-------------|-----------|---------|------------|
1            | 3         |  20     | 06/01/2013 |
2            | 2         |  20     | 06/02/2013 |
3            | 3         |  20     | 06/03/2013 |
4            | 2         |  21     | 06/04/2013 |

EVENTS
eventid | name      | premium    | 
--------|-----------|------------|
20      | Lunch     | 0          | 
21      | Dinner    | 1          |

我希望 JOIN 的结果是:

sponsorhipid | sponsorid | eventid | date       | name    | premium  | 
-------------|-----------|---------|------------|---------| ---------|
1            | 3         |  20     | 06/01/2013 | Lunch   | 0        |
2            | 2         |  20     | 06/02/2013 | Lunch   | 0        |
4            | 2         |  21     | 06/04/2013 | Dinner  | 1        |

我尝试了 DISTINCT 和 GROUP BY 但这些会折叠事件，因此如果赞助商 #2 赞助了两个不同的事件，它们仍然只会显示一次。我怎样才能做到这一点？这是我的最后一个 SQL 查询:

SELECT DISTINCT (sponsorships.sponsorshipid), sponsorships.*, events.*
            FROM events 
            INNER JOIN sponsorships
            ON events.eventid = sponsorships.eventid 

非常感谢任何指点!

Answer 1

您需要像这样使用嵌套子查询:

SELECT s.sponsorhipid, s.sponsorid, s.eventid, s.date
       ,e.name, e.premium
FROM EVENTS e 
JOIN
(
    SELECT s1.* FROM SPONSORSHIPS s1
    JOIN
    (
      SELECT sponsorid, MIN(Date) As minDate
      FROM SPONSORSHIPS
      GROUP BY eventid,sponsorid
    ) s2
    ON s1.sponsorid = s2.sponsorid
    AND s1.date = s2.minDate
) s
ON e.eventid = s.eventid;

输出:

| SPONSORHIPID | SPONSORID | EVENTID |       DATE |   NAME | PREMIUM |
|--------------|-----------|---------|------------|--------|---------|
|            1 |         3 |      20 | 06/01/2013 |  Lunch |       0 |
|            2 |         2 |      20 | 06/02/2013 |  Lunch |       0 |
|            4 |         2 |      21 | 06/04/2013 | Dinner |       1 |

参见this SQLFiddle