有个表,记录如下
id,year,no
a,2011,10
b,2011,12
c,2011,6
a,2010,9
c,2010,7
b,2010,13
d,2010,6可否一句SQL实现排序规则如下:
只按照2011年的NO值从上到下,且同一个ID要按照年份从上到下排序id,year,no
b,2011,12
b,2010,13
a,2011,10
a,2010,9
c,2011,6
c,2010,7
d,2010,6
id,year,no
a,2011,10
b,2011,12
c,2011,6
a,2010,9
c,2010,7
b,2010,13
d,2010,6可否一句SQL实现排序规则如下:
只按照2011年的NO值从上到下,且同一个ID要按照年份从上到下排序id,year,no
b,2011,12
b,2010,13
a,2011,10
a,2010,9
c,2011,6
c,2010,7
d,2010,6
from 有个表 a ,有个表 b
where a.Id=b.Id and b.year=2011
order by b.No Desc,a.Year desc
mysql> select * from sort;
+------+------+------+
| id | year | no |
+------+------+------+
| a | 2011 | 10 |
| b | 2011 | 12 |
| c | 2011 | 6 |
| a | 2010 | 9 |
| c | 2010 | 7 |
| b | 2010 | 13 |
| d | 2010 | 6 |
+------+------+------+
7 rows in set (0.00 sec)mysql> set @org_order := 0;
Query OK, 0 rows affected (0.00 sec)mysql>
mysql> select id, year, no
-> from
-> (select *, @org_order := @org_order + 1 as org_order from sort) as t
-> order by id, org_order;
+------+------+------+
| id | year | no |
+------+------+------+
| a | 2011 | 10 |
| a | 2010 | 9 |
| b | 2011 | 12 |
| b | 2010 | 13 |
| c | 2011 | 6 |
| c | 2010 | 7 |
| d | 2010 | 6 |
+------+------+------+
7 rows in set (0.02 sec)
mysql> select a.*
-> from sort a ,sort b
-> where a.Id=b.Id and b.year=2011
-> order by b.No Desc,a.Year desc;
+------+------+------+
| id | year | no |
+------+------+------+
| b | 2011 | 12 |
| b | 2010 | 13 |
| a | 2011 | 10 |
| a | 2010 | 9 |
| c | 2011 | 6 |
| c | 2010 | 7 |
+------+------+------+
6 rows in set (0.00 sec)
用左连接就行了。原来是这样,我没读懂LZ的意思:select a.*
from sort a left join sort b
on a.Id=b.Id and b.year=2011
order by b.No Desc,a.Year desc
(SELECT * FROM (SELECT id FROM t WHERE `year`=2011 ORDER BY `no` DESC) a
UNION DISTINCT
SELECT id FROM t WHERE `year`<>2011) b LEFT OUTER JOIN t c ON b.id=c.id