混合的两个表：
项目
| item_id | user_id | data |
----------------------------
| 10 | 100 | A |
| 11 | 100 | C |
| 12 | 101 | E |
| 13 | 101 | G |
item_detail
| id | item_id | ignore | detail1 | detail2 | detail3 | detail4 |
-----------------------------------------------------------------
| 1 | 10 | 0 | h1 | h2 | h3 | h4 |
| 2 | 10 | 0 | g1 | g2 | g3 | g4 |
| 3 | 10 | 1 | f1 | f2 | f3 | f4 |
| 4 | 11 | 0 | e1 | e2 | e3 | e4 |
| 5 | 11 | 0 | d1 | d2 | d3 | d4 |
| 6 | 11 | 1 | c1 | c2 | c3 | c4 |
| 7 | 12 | 0 | b1 | b2 | b3 | b4 |
| 8 | 13 | 0 | a1 | a2 | a3 | a4 |
我需要通过item_id从item_detail的最大ID中找到detail1，detail2，并通过item_id从item_detail的MAX NON IGNORED ID的detail3，detail4中找到，并与项目行一起显示。
预期结果：
| item_id | user_id | data | detail1 | detail2 | detail3 | detail4 |
--------------------------------------------------------------------
| 10 | 100 | A | f1 | f2 | g3 | g4 |
| 11 | 100 | C | c1 | c2 | d3 | d4 |
| 12 | 101 | E | b1 | b2 | b3 | b4 |
| 13 | 101 | G | a1 | a2 | a3 | a4 |
这是带有这些数据集的SQLFiddle：http ://sqlfiddle.com/#!2/52839有
任何参与者吗？非常感谢您的投入。
谢谢！
最简单的方法将是对使用group_concat()具有substring_index()：
select i.item_id, i.user_id, i.data,
substring_index(group_concat(id.detail order by id.id desc), ',', 1
) as last_detail,
substring_index(group_concat(case when id.ignored = 0 then id.detail1 end order by id.id desc), ',', 1
) as last_non_ignored_detail1
substring_index(group_concat(case when id.ignored = 0 then id.detail2 end order by id.id desc), ',', 1
) as last_non_ignored_detail2
substring_index(group_concat(case when id.ignored = 0 then id.detail3 end order by id.id desc), ',', 1
) as last_non_ignored_detail3
substring_index(group_concat(case when id.ignored = 0 then id.detail4 end order by id.id desc), ',', 1
) as last_non_ignored_detail4
from items i join
item_detail id
on i.item_id = id.item_id
group by i.item_id;
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句