我有两个表,其中一个表的表名(tbl_brands)包含以下列:
第二个表名称(tbl_loader_attachment)包含以下列:
我使用下面的MySQL代码按DESC排序:
SELECT tbl_loader_attachment.*, tbl_brands.* FROM tbl_loader_attachment
INNER JOIN tbl_brands ON(tbl_brands.b_id=tbl_loader_attachment.b_id)
GROUP BY tbl_loader_attachment.b_id ORDER BY tbl_loader_attachment.la_id DESC
当我执行代码时,选择第一行(test1),我要选择最后一行get(test4)
一种方法使用GROUP BY
查询:
SELECT tla1.*, tb.*
FROM tbl_brands tb
INNER JOIN tbl_loader_attachment tla1
ON tb.b_id = tla1.b_id
INNER JOIN
(
SELECT b_id, MAX(la_id) AS max_la_id
FROM tbl_loader_attachment
GROUP BY b_id
) tla2
ON tla1.b_id = tla2.b_id AND
tla1.la_id = tla2.max_la_id;
如果您使用的是MySQL 8+(或者将来的读者应该使用MySQL 8+),那么这里的另一种选择是使用ROW_NUMBER
:
WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY b_id ORDER BY la_id DESC) rn
FROM tbl_loader_attachment
)
SELECT tla.*, tb.*
FROM tbl_brands tb
INNER JOIN cte tla ON tb.b_id = tla.b_id
WHERE tla.rn = 1;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句