MySQLtable:
id type p
1 A 12
2 A 23
3 A 34
4 A 65
5 B 23
6 B 54
7 B 31
8 B 68
9 C 23
10 C 27
写个查询
按照type分组,并且取每组前三个记录.结果是
1 A 12
2 A 23
3 A 34
5 B 23
6 B 54
7 B 31
9 C 23
10 C 27
id type p
1 A 12
2 A 23
3 A 34
4 A 65
5 B 23
6 B 54
7 B 31
8 B 68
9 C 23
10 C 27
写个查询
按照type分组,并且取每组前三个记录.结果是
1 A 12
2 A 23
3 A 34
5 B 23
6 B 54
7 B 31
9 C 23
10 C 27
LEFT JOIN TT B ON A.TYPE=B.TYPE AND A.ID>=B.ID
GROUP BY A.id,A.type,A.p
HVAING COUNT(B.ID)<=3
LIMIT 0, 30' at line 1 使用这个查询
SELECT A.id,A.type,A.p FROM ggg A
LEFT JOIN ggg B ON A.type=B.type AND A.id>=B.id
GROUP BY A.id,A.type,A.p
HVAING COUNT(B.id) <=3
还有如果在加个排序该放那里,比如表多个字段 X 按照每个组x的最大值先排列下,在取得前三个.
还有最后个问题,如果加个排序放那里啊,简单点的,按p的大小(int)先排列下.在取得每个组的前3个
就是order by p 这个
+------+------+------+
| id | type | p |
+------+------+------+
| 1 | A | 12 |
| 2 | A | 23 |
| 3 | A | 34 |
| 4 | A | 65 |
| 5 | B | 23 |
| 6 | B | 54 |
| 7 | B | 31 |
| 8 | B | 68 |
| 9 | C | 23 |
| 10 | C | 27 |
+------+------+------+
10 rows in set (0.00 sec)mysql> select *
-> from t_igyhi a
-> where 3>(select count(*) from t_igyhi where `type`=a.type and id<a.id);
+------+------+------+
| id | type | p |
+------+------+------+
| 1 | A | 12 |
| 2 | A | 23 |
| 3 | A | 34 |
| 5 | B | 23 |
| 6 | B | 54 |
| 7 | B | 31 |
| 9 | C | 23 |
| 10 | C | 27 |
+------+------+------+
8 rows in set (0.02 sec)mysql>
LEFT JOIN ggg B ON A.type=B.type AND A.id>=B.id
GROUP BY A.id,A.type,A.p
HAvING COUNT(B.id) <=3
order by a.p
这个没有排序re:wwwwb
这个排序是有了,但是分组给打乱了.
1 A 23
2 A 42
5 B 234
3 A 634
10 C 645
9 C 734
6 B 765
7 B 2348 这样也没有什么大问题,就是用的时候还要在检索下.
如果是按分组排好的,就前三个前三个取就好了.不知道还能不能改.真是太麻烦各位了
mysql> select *
-> from t_igyhi a
-> where 3>(select count(*) from t_igyhi where `type`=a.type and p<a.p);
+------+------+------+
| id | type | p |
+------+------+------+
| 1 | A | 12 |
| 2 | A | 23 |
| 3 | A | 34 |
| 5 | B | 23 |
| 6 | B | 54 |
| 7 | B | 31 |
| 9 | C | 23 |
| 10 | C | 27 |
+------+------+------+
8 rows in set (0.00 sec)mysql>
LEFT JOIN ggg B ON A.type=B.type AND A.id>=B.id
GROUP BY A.id,A.type,A.p
HAvING COUNT(B.id) <=3
order by A.type,A.id,a.p
改成
"简单点的,按每组p的大小(int)先排列下.在取得每个组的前3个,最后把所有组合一块"id type p
1 A 23
2 A 42
3 A 634
4 A 534
5 B 234
6 B 765
7 B 2348
8 B 825
9 C 734
10 C 645 结果,变成这样子的id type p
1 A 23
2 A 42
4 A 534
5 B 234
6 B 765
8 B 825
9 C 734
10 C 645 最后一次了,成不成就是它了.......
+------+------+------+
| id | type | p |
+------+------+------+
| 1 | A | 23 |
| 2 | A | 42 |
| 3 | A | 634 |
| 4 | A | 534 |
| 5 | B | 234 |
| 6 | B | 765 |
| 7 | B | 2348 |
| 8 | B | 825 |
| 9 | C | 734 |
| 10 | C | 645 |
+------+------+------+
10 rows in set (0.00 sec)mysql> select *
-> from t_igyhi a
-> where 3>(select count(*) from t_igyhi where `type`=a.type and p<a.p);
+------+------+------+
| id | type | p |
+------+------+------+
| 1 | A | 23 |
| 2 | A | 42 |
| 4 | A | 534 |
| 5 | B | 234 |
| 6 | B | 765 |
| 8 | B | 825 |
| 9 | C | 734 |
| 10 | C | 645 |
+------+------+------+
8 rows in set (0.00 sec)mysql>
| id | type | p |
+------+------+------+
| 1 | A | 23 |
| 2 | A | 42 |
| 4 | A | 534 |
| 5 | B | 234 |
| 6 | B | 765 |
| 8 | B | 825 |
| 9 | C | 734 |
| 10 | C | 645 |
+------+------+------+这个C组怎么没排列呢?理论上9和10应该掉个顺序,是不是不够3个的原因?
不是因为输出根本就没有控制排序,改成如下mysql> select *
-> from t_igyhi a
-> where 3>(select count(*) from t_igyhi where `type`=a.type and p<a.p)
-> order by `type`,p;
+------+------+------+
| id | type | p |
+------+------+------+
| 1 | A | 23 |
| 2 | A | 42 |
| 4 | A | 534 |
| 5 | B | 234 |
| 6 | B | 765 |
| 8 | B | 825 |
| 10 | C | 645 |
| 9 | C | 734 |
+------+------+------+
8 rows in set (0.00 sec)mysql>