全部博文(2065)
分类: Mysql/postgreSQL
2010-06-02 11:23:00
Mysql里面的union与order by
一个示例
(select
a.`monitortype`,a.`servername`,a.`monitortime`,a.`rio`,a.`wio`,a.`wuse`,a.`ruse`,a.`use` from skio a,portip b,hardware c where a.servername = b.server_ip and b.h_serverserial = c.h_serverserial and c.h_groupmonitor='test' and a.MonitorTime>'2010-06-01 10:52:14' and
a.MonitorTime <
'2010-06-02 10:52:17' order
by a.monitortime
desc) union select * from (select a.`monitortype`,a.`servername`,a.`monitortime`,a.`rio`,a.`wio`,a.`wuse`,a.`ruse`,a.`use` from skio a,mserver b where (a.servername = b.vwip or a.servername = b.vlip) and b.h_groupmonitor='test' and a.MonitorTime>'2010-06-01 10:52:14'
and a.MonitorTime
< '2010-06-02
10:52:17' order by
a.monitortime desc) limit 0,50
以上的一条SQL语句
经过了一段UNION运算之后发现并没有按照我想要的 order by a.monitortime
desc 进行排序处理。
解决办法:
Select * from
(select
a.`monitortype`,a.`servername`,a.`monitortime`,a.`rio`,a.`wio`,a.`wuse`,a.`ruse`,a.`use` from skio a,portip b,hardware c where a.servername = b.server_ip and b.h_serverserial = c.h_serverserial and c.h_groupmonitor='test' and a.MonitorTime>'2010-06-01 10:52:14' and
a.MonitorTime <
'2010-06-02 10:52:17' order
by a.monitortime
desc)
as A union select * from ( select * from (select a.`monitortype`,a.`servername`,a.`monitortime`,a.`rio`,a.`wio`,a.`wuse`,a.`ruse`,a.`use` from skio a,mserver b
where (a.servername = b.vwip or a.servername = b.vlip) and b.h_groupmonitor='test' and a.MonitorTime>'2010-06-01 10:52:14' and
a.MonitorTime <
'2010-06-02 10:52:17' order
by a.monitortime
desc) as B limit 0,50
这样就可以了。即添加一个select
* from 这样的一个东西。因为子集合里面的数据已作了排序了!