|
c_data中的每个项目都在一个类别/部分中。我想限制每个类别显示多少个项目,而不是限制检索到的项目总数。显然,如果在查询中添加“ limit
* ^' r# d8 r/ i& O& N20”之类的内容,则总共只能获取20个结果,而不是每个类别20个结果。3 R" r. ^2 ] a/ ~
SELECT cm.id,# y( o: P4 a2 |. B4 g' @" G% F
cm.title AS cmtitle,+ G, y' Y' q# Z6 s; O- N6 A
cm.sectionid,1 s( R! h( t% A6 j$ P
cm.type AS cmtype,9 U* h7 j- t# S) q7 R" G {
cd.id,# s X+ e7 V! G/ r
cd.time,& ]7 s ] q' j/ z2 @; ]) c: t
cd.link,
* Y5 k* q! w. N6 q/ j. m: Z cd.title,
: i- u! c& b+ R: P1 d cd.description,( [3 q$ c2 R- n0 h" l" O: \( [4 {! U
cd.sectionid AS sectionid; g2 D. K8 W+ @+ Y V
FROM c_main AS cm
. m9 W, W, A2 C+ [ JOIN c_data AS cd ON cd.sectionid=cm.sectionid+ f/ q6 |& k. n
WHERE cd.sectionid=cm.sectionid
" b. R \( Q# @1 h ORDER by id ASC, L, R" h3 g* h& B* z
具有类别的字段是“ sectionid”。( A# m) a. p/ R- i0 d6 f6 S) M
% ~+ q5 J* s7 I4 [* B5 C; M; P
解决方案:
4 W) r" P- }5 k5 K% v
$ E+ U5 J4 V8 t3 y+ F" O% [0 U) x2 X! ^/ I6 w' d s6 b# f
% n1 Z+ S. U" r4 x' }# V
MySQL没有任何排名功能,但是您可以使用变量来创建伪行号。7 u% S& s# Z" t0 W. l6 {) F
使用:% }5 H: Y m6 c4 V1 y* Q
SELECT x.*4 A" j# k. g ?- s- C% ~
FROM (SELECT cm.id,1 v: f" J2 \5 \; T- p
cm.title AS cmtitle,0 m o& e; e+ F! w
cm.sectionid,
% t" g N( k. k cm.type AS cmtype,
7 x$ K# t# C# r- C% \% C" F cd.id AS cd_id, u/ Y2 F& x8 L. m4 f
cd.time,1 y9 Q) L8 Y1 t/ r8 e/ [' z8 Q
cd.link,
( p+ z( _# W. }8 X' W cd.title,
6 i5 c5 m/ \# E, a: H/ n cd.description,+ k |% Q' e6 C9 w
cd.sectionid AS cd_sectionid,$ s! m1 Z$ y+ T/ T, X* e6 K. E
CASE
& q U. l) I( F# R WHEN @sectionid != cm.sectionid THEN @rownum := 1
* ]4 P) A. C) @0 |5 o# c ELSE @rownum := @rownum + 1
! ?2 @9 u5 C' N# d1 Z0 P' x: v END AS rank,
$ h% |/ w) V$ Z3 f3 n% I/ t) } @sectionid := cm.sectionid' ^0 i9 C' n9 b& @, H
FROM C_MAIN cm," \7 y% u [/ g: |# V
C_DATA cd, t7 i: O: c4 W( v
(SELECT @rownum := 0, @sectionid := NULL) r
9 O) W- _) E! _+ g) B WHERE cm.sectionid = cd.sectionid9 {2 @& M$ P9 H/ \' D
ORDER BY cm.sectionid) x
: ]6 h. | e; J& l8 I WHERE x.rank <= 20# u2 n4 q, \0 x- f K
ORDER BY id |
|