有一个产品表tbl_products
(product_id=产品id,product_name=产品名,c_id=产品所属类别,add_time=产品生成时间,click_num=产品关注次数)
product_id product_name c_id add_time click_num表中共有324条记录我现在想写一条语句按c_id 分组，然后取每一组click_num关注次数最高的前三条记录不知要如何写呢
请赐教。
(product_id=产品id,product_name=产品名,c_id=产品所属类别,add_time=产品生成时间,click_num=产品关注次数)
product_id product_name c_id add_time click_num表中共有324条记录我现在想写一条语句按c_id 分组，然后取每一组click_num关注次数最高的前三条记录不知要如何写呢
请赐教。
SELECT A.c_id,A.click_num
FROM VIEW1 A LEFT JOIN VIWE1 B
ON A.c_id=B.c_id AND A.click_num<=B.click_num
HAVING COUNT(B.c_id)<=3
1 aaaa 1 201006 10
2 bbbb 1 201006 22
3 cccc 2 201005 7
4 dddd 2 201006 9
5 eeee 1 201006 13
6 ffff 1 201005 14
7 gggg 3 201006 9
8 hhhh 2 201006 8
FROM tt A LEFT JOIN tt B
ON A.c_id=B.c_id AND A.click_num<=B.click_num
group by a.product_id,a.product_name,a.c_id,a.add_time,a.click_num
HAVING COUNT(B.c_id)<=3
product_id product_name c_id add_time click_num
2 bbbb 1 201006 22
6 ffff 1 201005 14
5 eeee 1 201006 13
4 dddd 2 201006 9
8 hhhh 2 201006 8
3 cccc 2 201005 7
7 gggg 3 201006 9
153 3 293
155 3 238
156 3 230
152 3 186
154 3 168
60 3 163
65 3 138
61 3 109
64 3 106
1 6 57
3 6 53
2 6 48
59 6 40
5 6 38
130 6 28
129 6 21
247 8 2337
169 8 1549
170 8 883
248 8 627
172 8 401
396 8 392
242 8 306
359 8 272
356 8 225
397 8 221
171 8 186
196 8 170
488 8 169
358 8 155
241 8 154
360 8 140
399 8 139
398 8 108
243 8 96
405 8 78
426 8 68
467 8 62
487 8 59
403 8 56
469 8 54
486 8 53
404 8 53
357 8 48
127 8 41
441 8 40
428 8 40
433 8 39
12 8 37
151 8 34
445 8 32
444 8 30
466 8 27
465 8 25
425 8 23
401 8 22
447 8 20
408 8 15
448 8 15
485 8 14
468 8 14
402 8 13
464 8 13
432 8 12
427 8 12
429 8 11
446 8 11
400 8 11
431 8 10
430 8 7
442 8 6
443 8 5
134 10 3255
494 10 341
133 10 253
493 10 211
490 10 134
546 10 95
547 10 60
548 10 58
489 10 52
136 10 51
491 10 32
549 10 22
492 10 1
347 24 332
422 24 299
173 25 12127
21 26 497
298 26 304
295 26 99
297 26 91
296 26 59
16 27 10300
90 28 9048
81 29 252
80 29 208
82 29 39
421 29 28
420 29 6
83 30 428
84 30 30
79 33 2
88 34 8
176 36 35
177 36 28
281 36 20
205 36 9
202 36 6
201 36 5
206 36 4
208 36 3
198 36 3
197 36 2
204 36 2
207 36 1
203 36 1
199 36 1
200 36 1
283 38 50
282 38 42
23 38 24
434 38 19
284 38 16
102 38 16
436 38 11
449 38 10
435 38 8
108 38 3
437 38 2
438 39 8
440 40 3
439 40 1
374 41 18
369 41 16
376 41 9
361 41 7
370 41 7
375 41 4
371 41 3
363 41 2
407 41 1
373 41 1
362 41 1
366 41 0
364 41 0
309 43 44
308 43 14
545 43 13
345 43 9
372 43 9
344 43 6
310 43 6
542 43 6
544 43 4
343 43 3
543 43 3
307 43 2
140 45 479
326 45 179
327 45 145
329 45 44
328 45 27
316 45 26
336 45 26
334 45 24
323 45 24
335 45 23
322 45 13
333 45 9
317 45 8
324 45 6
321 45 6
313 45 5
319 45 3
149 51 67
148 51 66
150 51 49
349 60 283
300 60 202
193 60 174
348 60 157
132 60 127
195 60 101
131 60 84
353 60 75
350 60 71
194 60 57
351 60 51
299 60 48
481 60 39
22 60 29
112 60 21
484 60 18
410 60 15
352 60 15
483 60 13
409 60 13
109 60 12
482 60 9
244 61 1394
117 61 234
412 61 182
418 61 178
246 61 128
245 61 109
110 61 103
413 61 83
411 61 69
118 61 48
416 61 44
44 61 43
126 61 40
419 61 38
114 61 37
424 61 37
125 61 36
8 61 33
119 61 32
423 61 29
417 61 28
471 61 24
45 61 23
120 61 22
470 61 19
111 61 15
496 61 1
495 61 1
166 64 0
167 64 0
168 64 0
388 68 19
392 68 12
391 68 11
386 68 9
394 68 9
236 68 8
385 68 7
234 68 5
389 68 5
235 68 3
393 68 3
390 68 3
395 68 2
383 68 1
384 68 0
459 69 2
461 69 1
457 69 0
462 69 0
456 69 0
463 69 0
455 69 0
229 70 86
223 70 23
230 70 15
209 70 11
231 70 8
212 70 7
211 70 4
220 70 3
216 70 3
214 70 2
217 70 2
528 70 2
517 70 2
516 70 2
210 70 1
514 70 1
523 70 1
520 70 1
524 70 0
529 70 0
522 70 0
519 70 0
518 70 0
527 70 0
525 70 0
515 70 0
521 70 0
526 70 0
480 71 49
475 71 24
476 71 17
479 71 14
474 71 13
478 71 12
255 71 9
257 71 7
477 71 5
472 71 4
473 71 4
256 71 2
453 71 2
452 71 1
454 71 1
450 71 0
226 72 42
280 72 10
552 72 7
551 72 5
550 72 4
262 74 1296
252 74 567
250 74 336
275 74 280
274 74 249
263 74 192
251 74 180
249 74 163
261 74 71
259 74 61
258 74 43
260 74 38
276 74 7
277 74 4
271 74 3
272 74 2
555 74 1
273 74 1
238 76 54
239 76 47
237 76 25
346 77 0
[征集]分组取最大N条记录方法征集，及散分....
-> from tbl_products a
-> where 3>(select count(*) from tbl_products where c_id=a.c_id and click_num>a.click_num);
+------------+------+-----------+
| product_id | c_id | click_num |
+------------+------+-----------+
| 153 | 3 | 293 |
| 155 | 3 | 238 |
| 156 | 3 | 230 |
| 1 | 6 | 57 |
| 3 | 6 | 53 |
| 2 | 6 | 48 |
| 247 | 8 | 2337 |
| 169 | 8 | 1549 |
| 170 | 8 | 883 |
| 134 | 10 | 3255 |
| 494 | 10 | 341 |
| 133 | 10 | 253 |
| 347 | 24 | 332 |
| 422 | 24 | 299 |
| 173 | 25 | 12127 |
| 21 | 26 | 497 |
| 298 | 26 | 304 |
| 295 | 26 | 99 |
| 16 | 27 | 10300 |
| 90 | 28 | 9048 |
| 81 | 29 | 252 |
| 80 | 29 | 208 |
| 82 | 29 | 39 |
| 83 | 30 | 428 |
| 84 | 30 | 30 |
| 79 | 33 | 2 |
| 88 | 34 | 8 |
| 176 | 36 | 35 |
| 177 | 36 | 28 |
| 281 | 36 | 20 |
| 283 | 38 | 50 |
| 282 | 38 | 42 |
| 23 | 38 | 24 |
| 438 | 39 | 8 |
| 440 | 40 | 3 |
| 439 | 40 | 1 |
| 374 | 41 | 18 |
| 369 | 41 | 16 |
| 376 | 41 | 9 |
| 309 | 43 | 44 |
| 308 | 43 | 14 |
| 545 | 43 | 13 |
| 140 | 45 | 479 |
| 326 | 45 | 179 |
| 327 | 45 | 145 |
| 149 | 51 | 67 |
| 148 | 51 | 66 |
| 150 | 51 | 49 |
| 349 | 60 | 283 |
| 300 | 60 | 202 |
| 193 | 60 | 174 |
| 244 | 61 | 1394 |
| 117 | 61 | 234 |
| 412 | 61 | 182 |
| 166 | 64 | 0 |
| 167 | 64 | 0 |
| 168 | 64 | 0 |
| 388 | 68 | 19 |
| 392 | 68 | 12 |
| 391 | 68 | 11 |
| 459 | 69 | 2 |
| 461 | 69 | 1 |
| 457 | 69 | 0 |
| 462 | 69 | 0 |
| 456 | 69 | 0 |
| 463 | 69 | 0 |
| 455 | 69 | 0 |
| 229 | 70 | 86 |
| 223 | 70 | 23 |
| 230 | 70 | 15 |
| 480 | 71 | 49 |
| 475 | 71 | 24 |
| 476 | 71 | 17 |
| 226 | 72 | 42 |
| 280 | 72 | 10 |
| 552 | 72 | 7 |
| 262 | 74 | 1296 |
| 252 | 74 | 567 |
| 250 | 74 | 336 |
| 238 | 76 | 54 |
| 239 | 76 | 47 |
| 237 | 76 | 25 |
| 346 | 77 | 0 |
+------------+------+-----------+
83 rows in set (1.28 sec)mysql>