Question

我试图将逻辑复制为显示 < a href=> https://stackoverflow.com/ questions/1442527/how-to-elect-the-newest- four-items-per 类/1442867#1442867#1442867>>。但是,如果有人加入,我没有运气。下面是我查询的最小版本:

SELECT resources.title, catRel.catRef
FROM resources 
LEFT JOIN placesRel ON placesRel.refId = resId 
LEFT JOIN catRel ON refId = resId
WHERE ...

简言之,我需要一份包含Id类的列表,我想将结果限制在不超过n 的结果,例如,每个目录只显示两个结果:

title             catRef
Swizz Gubbinz     1
Runcible Spoons   1
Peter Pan DVD     2
Button Moon       2
Monkey Alan       3
Bilge Pump        3

Answer 1

我不确定哪张桌子属于RefID和ResID 但是...

SELECT resources.title, catRel.catRef
FROM resources 
LEFT JOIN placesRel ON placesRel.refId = resId 
LEFT JOIN catRel as cr1 ON cr1.catRel.primaryKey in (select cr2.primaryKey from catRel as cr2 where cr2.refID = resId Limit 0,2)
WHERE ...

Answer 2

In the lack of Window Functions from MySQL, the answer is not trivial. Here s a trick which selects top N record per group, by utilizing MySQL s GROUP_CONCAT: MySQL: Selecting Top N Records Per Group.

Group_CONCAT 是一个集成函数, 可以被操纵, 以便按预期的顺序提供连接字符串。使用文本操作, 字符串会被解析。可以选择, 将值投入合适的类型。

友情链接