id 数量
1 10
1 11
1 12
1 13
2 12
2 13
2 14
2 15
id数量不只2个,可能3个,可能10个
需要sql出来成(id1 在前面也可以)
2 15
1 13
2 14
1 12
……
也就是
id2按数量从大到小排序,id1按数量从大到小排序,然后交叉排列
不知道这个怎么操作?
1 10
1 11
1 12
1 13
2 12
2 13
2 14
2 15
id数量不只2个,可能3个,可能10个
需要sql出来成(id1 在前面也可以)
2 15
1 13
2 14
1 12
……
也就是
id2按数量从大到小排序,id1按数量从大到小排序,然后交叉排列
不知道这个怎么操作?
+------+------+
| id | qty |
+------+------+
| 1 | 10 |
| 1 | 11 |
| 1 | 12 |
| 1 | 13 |
| 2 | 12 |
| 2 | 13 |
| 2 | 14 |
| 2 | 15 |
+------+------+
8 rows in set (0.00 sec)mysql> select * from t_advancejar a
-> order by (select count(*) from t_advancejar where id=a.id and qty>a.qty),id desc;
+------+------+
| id | qty |
+------+------+
| 2 | 15 |
| 1 | 13 |
| 2 | 14 |
| 1 | 12 |
| 2 | 13 |
| 1 | 11 |
| 2 | 12 |
| 1 | 10 |
+------+------+
8 rows in set (0.02 sec)mysql>
-> from t_advancejar a,t_advancejar b
-> where a.id=b.id and a.qty<=b.qty
-> group by a.id,a.qty
-> order by count(*),a.id desc;
+------+------+
| id | qty |
+------+------+
| 2 | 15 |
| 1 | 13 |
| 2 | 14 |
| 1 | 12 |
| 2 | 13 |
| 1 | 11 |
| 2 | 12 |
| 1 | 10 |
+------+------+
8 rows in set (0.00 sec)mysql>
mysql> select a.id,a.qty
-> from t_advancejar a inner join t_advancejar b on a.id=b.id and a.qty<=b.qty
-> group by a.id,a.qty
-> order by count(*),a.id desc;
+------+------+
| id | qty |
+------+------+
| 2 | 15 |
| 1 | 13 |
| 2 | 14 |
| 1 | 12 |
| 2 | 13 |
| 1 | 11 |
| 2 | 12 |
| 1 | 10 |
+------+------+
8 rows in set (0.00 sec)mysql>