假設(shè)現(xiàn)在有一組數(shù)據(jù)，如下圖所示信殊。
| extend_id | out_id | sort_order 排序，越小越靠前 |
|---|---|---|
| 1 | 20 | 255 |
| 2 | 80 | 255 |
| 3 | 20 | 253 |
| 4 | 80 | 253 |
| 5 | 20 | 252 |
| 6 | 80 | 252 |
| 7 | 80 | 254 |
| 8 | 20 | 254 |
| 9 | 20 | 3 |
| 10 | 20 | 3 |
| 11 | 20 | 4 |
| 12 | 20 | 3 |
方法1：用子查詢：
SELECT *
FROM uploaded_extend a
WHERE (
SELECT count(*)
FROM uploaded_extend b
WHERE b.out_id=a.out_id AND b.sort_order < a.sort_order
) < 3
ORDER BY a.out_id DESC,a.sort_order ASC,a.extend_id DESC
解析：
Mysql執(zhí)行順序：
1.FROM uploaded_extend b
2.WHERE ...
3.SELECT *
4.ORDER BY a.out_id DESC,a.sort_order ASC,a.extend_id DESC
子查詢同上
數(shù)據(jù)匹配過程：
a.extend_id = 1時王浴，子查詢返回7姨伟。
a.extend_id = 2時，子查詢返回3哮独。
a.extend_id = 3時，子查詢返回5察藐。
a.extend_id = 4時皮璧，子查詢返回2,2<3符合條件,加入結(jié)果集。
a.extend_id = 5時分飞，子查詢返回5悴务。
a.extend_id = 6時，子查詢返回1譬猫，1<3符合條件,加入結(jié)果集讯檐。
..... 此處省略.....
方法2：用左鏈接：
SELECT a.*
FROM uploaded_extend a
LEFT JOIN uploaded_extend b ON b.out_id = a.out_id AND b.sort_order < a.sort_order
GROUP BY a.extend_id,a.out_id
having count(b.extend_id)<3
ORDER BY a.out_id DESC,a.sort_order ASC,a.extend_id DESC
方法3：用exists半連接：
SELECT * FROM `uploaded_extend` a
WHERE EXISTS
(
SELECT COUNT(*)
FROM `uploaded_extend` b
WHERE b.out_id=a.out_id AND b.sort_order < a.sort_order
HAVING COUNT(*)<3
)
ORDER BY a.out_id DESC,a.sort_order ASC,a.extend_id DESC
結(jié)果返回：
| extend_id | out_id | sort_order |
|---|---|---|
| 6 | 80 | 252 |
| 4 | 80 | 253 |
| 7 | 80 | 254 |
| 12 | 20 | 3 |
| 10 | 20 | 3 |
| 9 | 20 | 3 |
