SELECT p.id, g.typeid, g.id, g.title FROM products p INNER JOIN (SELECT * FROM gallery a WHERE (SELECT COUNT(*) FROM gallery b WHERE b.title = a.title AND b.id >= a.id) <= 2 ) g ON p.id = g.typeid WHERE p.id in (3,4,5)
试试这个SQL小提琴演示
SELECT p.id, g.typeid, g.id, g.name
FROM products p
INNER JOIN (SELECT * FROM gallery a
WHERE (SELECT COUNT(*) FROM gallery b WHERE b.typeid = a.typeid AND b.id >= a.id) <= 2
) g ON p.id = g.typeid
WHERE p.id in (3,4,5) order by g.id asc
所以基本上这部分
WHERE (SELECT COUNT(*) FROM gallery b WHERE b.title = a.title AND b.id >= a.id) <= 2
用于替换 group by typeid