各位高手,首先根据时间倒序排序后我要实时的记录找到与之对应的记录、 就是一条记录是实时上传到数据库的, 然后还有一条记录是之前的, 它们之间有可能隔着很多很多的记录,(就像一卡通上下车刷卡一样,记录可能是乱的,但是在页面上显示的这个用户要显示一条上车刷卡记录,下一条记录就是他的下车刷卡记录这样配对 ) 时间排序后,我都要一一对应的排序, 各种分组是很难实现的, 我现在的sql是这样的, 实现不了那样的效果, 请高手帮忙!select ROW_NUMBER()
OVER(PARTITION BY fo.card_no ORDER BY cd.tenancy_date desc) as XH,
a.area_name,
fo.card_no,
ei.equipment_name,
we.website_name,
cd.tenancy_date,
ltrim(to_char(cd.tenancy_price / 100, '99999999990.99')),
cd.Ref_Type,
fo.card_type,
cd.ref_is_normal,
ef.bikepile_no,
bi.bike_no,
ac.name,
ac.associator_no,
(case
when cd.Ref_Type = 0 then
ltrim(to_char((card_balance - foregift) / 100,
'99999999990.99'))
else
ltrim(to_char((card_balance) / 100, '99999999990.99'))
end),
(case
when cd.Ref_Type = 0 then
ltrim(to_char((foregift) / 100))
else
'0'
end),
(bi.big_bike_no),
we.website_id
from fz_t_card_info fo
left outer join fz_t_ref_card cd on fo.card_id = cd.card_id
left outer join sm_t_website we on cd.tenancy_website_id = we.website_id
left outer join fz_t_bikepile_manager ef on cd.bikepile_id =
ef.bikepile_id
left outer join fz_t_county_area a on we.area_id = a.area_id
left outer join fz_t_associator ac on fo.card_id = ac.card_id
left outer join fz_t_pos_info pi on cd.tenancy_pos_no = pi.pos_info_id
left outer join fz_t_equipment_info ei on ei.equipment_id =
pi.equipment_id
left outer join fz_t_bike_info bi on bi.bike_rfid = cd.bike_rfid
where (cd.Ref_Type = 1 or cd.Ref_Type = 0)
-- group by fo.card_no
order by cd.tenancy_date desc
OVER(PARTITION BY fo.card_no ORDER BY cd.tenancy_date desc) as XH,
a.area_name,
fo.card_no,
ei.equipment_name,
we.website_name,
cd.tenancy_date,
ltrim(to_char(cd.tenancy_price / 100, '99999999990.99')),
cd.Ref_Type,
fo.card_type,
cd.ref_is_normal,
ef.bikepile_no,
bi.bike_no,
ac.name,
ac.associator_no,
(case
when cd.Ref_Type = 0 then
ltrim(to_char((card_balance - foregift) / 100,
'99999999990.99'))
else
ltrim(to_char((card_balance) / 100, '99999999990.99'))
end),
(case
when cd.Ref_Type = 0 then
ltrim(to_char((foregift) / 100))
else
'0'
end),
(bi.big_bike_no),
we.website_id
from fz_t_card_info fo
left outer join fz_t_ref_card cd on fo.card_id = cd.card_id
left outer join sm_t_website we on cd.tenancy_website_id = we.website_id
left outer join fz_t_bikepile_manager ef on cd.bikepile_id =
ef.bikepile_id
left outer join fz_t_county_area a on we.area_id = a.area_id
left outer join fz_t_associator ac on fo.card_id = ac.card_id
left outer join fz_t_pos_info pi on cd.tenancy_pos_no = pi.pos_info_id
left outer join fz_t_equipment_info ei on ei.equipment_id =
pi.equipment_id
left outer join fz_t_bike_info bi on bi.bike_rfid = cd.bike_rfid
where (cd.Ref_Type = 1 or cd.Ref_Type = 0)
-- group by fo.card_no
order by cd.tenancy_date desc
a.area_name,
fo.card_no,
ei.equipment_name,
we.website_name ,
cd.tenancy_date,
ltrim(to_char(cd.tenancy_price / 100, '99999999990.99')),
cd.ref_type,
fo.card_type,
cd.ref_is_normal,
ef.bikepile_no,
bi.bike_no,
ac.name,
ac.associator_no,
decode(cd.Ref_Type,0,ltrim(to_char((card_balance - foregift) / 100, '99999999990.99')),ltrim(to_char((card_balance) / 100, '99999999990.99'))),
decode(cd.Ref_Type,0,ltrim(to_char((foregift) / 100)),0),
(bi.big_bike_no),
we.website_id
from fz_t_card_info fo
left outer join fz_t_ref_card cd on fo.card_id = cd.card_id
left outer join sm_t_website we on cd.tenancy_website_id = we.website_id
left outer join fz_t_bikepile_manager ef on cd.bikepile_id =
ef.bikepile_id
left outer join fz_t_county_area a on we.area_id = a.area_id
left outer join fz_t_associator ac on fo.card_id = ac.card_id
left outer join fz_t_pos_info pi on cd.tenancy_pos_no = pi.pos_info_id
left outer join fz_t_equipment_info ei on ei.equipment_id =
pi.equipment_id
left outer join fz_t_bike_info bi on bi.bike_rfid = cd.bike_rfid
where (cd.Ref_Type = 1 or cd.Ref_Type = 0) order by cd.tenancy_date desc
),
temp2 as (select t.*,lead(tenancy_date) over(partition by card_no order by tenancy_date) next_time from temp1 t)
--select tt.*,decode(ref_type,1, tenancy_date,next_time) temp_time from temp2 tt order by temp_time desc,card_no,tenancy_date descselect tt.*,decode(ref_type,1, tenancy_date,nvl(next_time,tenancy_date)) temp_time from temp2 tt order by temp_time desc,card_no,tenancy_date desc