有两张表的连接查询,使用sql的union操作,要对结果集按refApp 分组,还要按timestamp降序排列(max(timestamp)大的分组放在前面),
不知道有什么办法,在CSDN上找到了对于一张表的解决办法,但是对于两张表的union,就不知道怎么办了。
sql语句如下:
select * from
(
select r.reservationId as venditionId,
r.reserveApp as refApp,
r.reservationTimestamp as 'timestamp',
from Reservation as r unionselect c.consumptionId ,
c.consumptionApp,
c.consumptionTimestamp,
from Consumption as c
) as U
order by refApp desc
现在只是按refApp进行了分组,但是没法根据timestamp排序
不知道有什么办法,在CSDN上找到了对于一张表的解决办法,但是对于两张表的union,就不知道怎么办了。
sql语句如下:
select * from
(
select r.reservationId as venditionId,
r.reserveApp as refApp,
r.reservationTimestamp as 'timestamp',
from Reservation as r unionselect c.consumptionId ,
c.consumptionApp,
c.consumptionTimestamp,
from Consumption as c
) as U
order by refApp desc
现在只是按refApp进行了分组,但是没法根据timestamp排序
(
select
r.reservationId as venditionId,
r.reserveApp as refApp,
r.reservationTimestamp as 'timestamp',
from Reservation as r
union
select
c.consumptionId ,
c.consumptionApp,
c.consumptionTimestamp,
from Consumption as c
) as U,
(select
refApp,max(timestamp) as 'timestamp'
from (select r.reserveApp as refApp,r.reservationTimestamp as 'timestamp', from Reservation as r
union
select c.consumptionApp,c.consumptionTimestamp, from Consumption as c
) as t
group by refApp
) as V
order by V.timestamp desc,U.timestamp desc
(
select r.reservationId as venditionId, r.reserveApp as refApp, r.reservationTimestamp as 'timestamp', -- SYNTAX ERROR!ACMAIN
from Reservation as r
union
select c.consumptionId , c.consumptionApp, c.consumptionTimestamp, from Consumption as c
) as U
order by refApp desc
select * from
(
select
r.reservationId as venditionId,
r.reserveApp as refApp,
r.reservationTimestamp as 'timestamp',
from Reservation as r
union
select
c.consumptionId ,
c.consumptionApp,
c.consumptionTimestamp,
from Consumption as c
) as U,
(select
refApp,max(timestamp) as 'timestamp'
from (select r.reserveApp as refApp,r.reservationTimestamp as 'timestamp', from Reservation as r
union
select c.consumptionApp,c.consumptionTimestamp, from Consumption as c
) as t
group by refApp
) as V
where U.refApp=V.refApp
order by V.timestamp desc,U.timestamp desc
GO
insert tb
select 1,'2008-1-1' union all
select 1,'2008-2-1' union all
select 1,'2009-1-3' union all
select 2,'2009-2-3' union all
select 2,'2009-3-1'
GO select
a.*
from tb a,(select id,max(time) as time from tb group by id) b where a.id=b.id
order by b.time desc,a.time desc/**
"2" "2009-03-01 00:00:00"
"2" "2009-02-03 00:00:00"
"1" "2009-01-03 00:00:00"
"1" "2008-02-01 00:00:00"
"1" "2008-01-01 00:00:00"
**/