php - 多对多表加入mysql

Question

我有三个表:

邮件

+-----+----------+----------------+
| id  | chain_id | subject        |
+-----+----------+----------------+
| 108 |      108 | aaa            |
| 109 |      109 | Chain          |
| 110 |      110 | New            |
| 111 |      108 | aaa            |
| 112 |      108 | Re: Re:  aaa   |
| 113 |      109 | Chain          |
| 114 |      114 | Fwd: Subject   |
| 115 |      108 | Fwd: aaa       |
| 116 |      108 | Re: aaa        |
| 117 |      108 | Fwd: aaa       |
+-----+----------+----------------+

链标签

+----+----------+--------+
| id | chain_id | tag_id |
+----+----------+--------+
|  1 |      108 |     15 |
|  2 |      108 |     16 |
+----+----------+--------+

邮件标签

+----+--------+---------+
| id | name   | color   |
+----+--------+---------+
| 15 | First  | #ffdaef |
| 16 | Second | #aed9ff |
+----+--------+---------+

我想从mails表中选择所有记录，mail_tags.color，mail_tags.name，以及相同的记录数量chain_id，按mails.chain_id分组，但分组后我想留下最新记录(最大id)。

我设法写了这个查询

SELECT
        *,
        COUNT(*) AS quantity
    FROM
        (SELECT
            *
        FROM
            mails
        ORDER BY id DESC) AS t
    GROUP BY chain_id
    ORDER BY id DESC

它返回按 chain_id 分组的最后一行以及该链中的邮件总数，但我不知道如何为每条记录加入标签名称和标签颜色。有些记录可能只有很少的标签，有些则没有。

Answer 1

我不太确定我是否理解确切的要求，但按照这些思路应该可以工作:

select x.id, x.chain_id, x.subject, count, mt.name, mt.color from
(
select m.*,count(*) count,max(mt.id) maxid
   from mails m
left outer join chain_tags ct on (m.chain_id=ct.chain_id)
left outer join mail_tags mt on (ct.tag_id=mt.id)
  group by m.chain_id
) x
left join mail_tags mt on x.maxid=mt.id

基本上，想法是通过 chain_id 获取计数和最大邮件标签 ID，这是在内部查询中完成的，一旦我们知道了最大邮件标签 ID，我们就可以返回 mail_tags 并获取名称和颜色，这是由主查询中的左连接完成。希望这有帮助