我有2张桌子。第一个表是-产品，第二个表是修订版。
加入是通过product.id =版本.revisionable_id进行的。我需要将产品作为1条记录并加入到第一个修订版（created_at字段（2017））和最后一个修订版（created_at（2020））中，并获得如下内容：
Product_name，Revision_2017，Revision_2020
请帮助。
产品展示
+----+-------+
| id | name |
+----+-------+
| 1 | Tesla |
+----+-------+
修订版
+----+-----------------+------------+----------+---------+
| id | revisionable_id | created_at | metadata | user_id |
+----+-----------------+------------+----------+---------+
| 1 | 1 | 2017-01-01 | {} | 5 |
| 2 | 1 | 2018-01-01 | {} | 5 |
| 3 | 1 | 2019-01-01 | {} | 5 |
| 4 | 1 | 2020-01-01 | {} | 5 |
+----+-----------------+------------+----------+---------+
想要的结果：
+-------------+---------------+---------------------+-------------------+--------------------+------------------+
| Products.id | Products.name | revFirst.created_at | revFirst.metadata | revLast.created_at | revLast.metadata |
+-------------+---------------+---------------------+-------------------+--------------------+------------------+
| 1 | Tesla | 2017-01-01 | {} | 2020-01-01 | {} |
+-------------+---------------+---------------------+-------------------+--------------------+------------------+
一个选项使用MySQL 8.0中提供的窗口函数：
select p.*,
max(case when rn_asc = 1 then created_at end) as first_created_at,
max(case when rn_asc = 1 then metadata end) as first_metadata,
max(case when rn_desc = 1 then created_at end) as last_created_at,
max(case when rn_desc = 1 then metadata end) as last_metadata
from product p
left join (
select r.*,
row_number() over(partition by product_id order by created_at) rn_asc,
row_number() over(partition by product_id order by created_at desc) rn_desc
from revisions r
) r on r.product_id = p.id and 1 in (r.rn_asc, r.rn_desc)
group by p.id
我不知道在到底是哪列revisions涉及产品id（id？revisionable_id？），所以我称那列product_id代替。
在最新版本的MySQL（8.0.13）中，两个横向联接可能是一个好方法：
select p.*, r1.*, r2.*
from product p
left join lateral (
select created_at as first_created_at, metadata as first_metadata
from revisions r
where r.product_id = p.id
order by created_at limit 1
) r1 on true
left join lateral (
select created_at as last_created_at, metadata as last_metadata
from revisions r
where r.product_id = p.id
order by created_at desc limit 1
) r2 on true
在早期版本中，我将使用几个子查询：
select p.*,
(
select r.created_at
from revisions r
where r.product_id = p.id
order by r.created_at limit 1
) as first_created_at,
(
select r.metadata
from revisions r
where r.product_id = p.id
order by r.created_at limit 1
) as first_metadata
(
select r.created_at
from revisions r
where r.product_id = p.id
order by r.created_at desc limit 1
) as last_created_at,
(
select r.metadata
from revisions r
where r.product_id = p.id
order by r.created_at desc limit 1
) as last_metadata
from product p
为了提高性能，您需要以下索引：
revisions(product_id, created_at, metadata);
revisions(product_id, created_at desc, metadata);
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句