有个表,记录如下
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
解决方案 »
- 明明可以将数据放入一个数据库,却分了多个库,为嘛
- 我们写了一个sqlserver的分页存储过程,但是怎么样按照这种的根式,写一个mysql的分页存储过程!以前没有用过mysql,有谁知道怎么写啊?
- 求助,这条sql语句应该怎么写!
- MYSQL+JAVA问题
- msyql数据库:id字段为自动增长。但我希望能在特定的时候归零,从1开始,怎么实现?
- win2000下的mysql服务器启动问题
- 100分求EMS Mysql Manager的注册码!!!
- mysql存储过程文件是存在哪里的?
- Mysql latin1插入中文数据乱码
- 有没有人帮下呀
- red hat linux5 下的 my.cnf 在哪?
- 公司经常停电,数据库老是被挂,怎么办?
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