name create_time flag
1 a 2012-9-1 0
2 b 2012-9-5 1
3 c 2012-9-3 1
4 d 2012-9-6 0
5 e 2012-9-2 1
6 f 2012-8-2 0User表
X:select * from user order by create_time descY:select * from user where flag=0 order by create_time desc
从X中取2条Y 最近时间数据 放在最上面 然后将X其余数据放下面
求sql:
数据置顶还不能乱了其它数据的排序
1 a 2012-9-1 0
2 b 2012-9-5 1
3 c 2012-9-3 1
4 d 2012-9-6 0
5 e 2012-9-2 1
6 f 2012-8-2 0User表
X:select * from user order by create_time descY:select * from user where flag=0 order by create_time desc
从X中取2条Y 最近时间数据 放在最上面 然后将X其余数据放下面
求sql:
数据置顶还不能乱了其它数据的排序
union
select * from user order by create_time desc
WITH t_user AS (
SELECT 'a' tname,TO_DATE('2012-9-1','yyyy-mm-dd') create_time,0 flag FROM DUAL UNION ALL
SELECT 'b' tname,TO_DATE('2012-9-5','yyyy-mm-dd') create_time,1 flag FROM DUAL UNION ALL
SELECT 'c' tname,TO_DATE('2012-9-3','yyyy-mm-dd') create_time,1 flag FROM DUAL UNION ALL
SELECT 'd' tname,TO_DATE('2012-9-6','yyyy-mm-dd') create_time,0 flag FROM DUAL UNION ALL
SELECT 'e' tname,TO_DATE('2012-9-2','yyyy-mm-dd') create_time,1 flag FROM DUAL UNION ALL
SELECT 'f' tname,TO_DATE('2012-8-2','yyyy-mm-dd') create_time,0 flag FROM DUAL
)
SELECT *
FROM t_user t
ORDER BY CASE
WHEN t.flag = 0 AND
ROW_NUMBER() OVER(PARTITION BY t.flag ORDER BY t.create_time DESC) <= 2 THEN
ROW_NUMBER() OVER(PARTITION BY t.flag ORDER BY t.create_time DESC)
ELSE ROW_NUMBER() OVER(ORDER BY t.create_time DESC)
ENDTNAME CREATE_TIME FLAG
----- ----------- ----------
d 2012/09/06 0
b 2012/09/05 1
a 2012/09/01 0
c 2012/09/03 1
e 2012/09/02 1
f 2012/08/02 0
WITH table1 AS
(
SELECT 'a' AS st, '2012-9-1' AS create_time,'0' AS flag FROM dual
union all
SELECT 'b' AS st, '2012-9-5' AS create_time,'1' AS flag FROM dual
union all
SELECT 'c' AS st, '2012-9-3' AS create_time,'1' AS flag FROM dual
union all
SELECT 'd' AS st, '2012-9-6' AS create_time,'0' AS flag FROM dual
union all
SELECT 'e' AS st, '2012-9-2' AS create_time,'1' AS flag FROM dual
union all
SELECT 'f' AS st, '2012-8-2' AS create_time,'0' AS flag FROM dual
)
select st, create_time, flag from (
select * from (
select *
from (select st, create_time, flag, '0' as str
from table1
where flag = 0
AND rownum <= 2
order by create_time desc)
union
select st, create_time, flag, '1' as str
from table1 t1
where not exists (select 1
from (select st, create_time, flag, '0' as str
from table1
where flag = 0
AND rownum <= 2)
where t1.st = st)) order by str,create_time desc)
FROM t_user t
ORDER BY CASE
WHEN t.flag = 0 AND
ROW_NUMBER() OVER(PARTITION BY t.flag ORDER BY t.create_time DESC) <= 2 THEN
ROW_NUMBER() OVER(PARTITION BY t.flag ORDER BY t.create_time DESC)
ELSE ROW_NUMBER() OVER(ORDER BY t.create_time DESC)
END,flag
手机定位得到经纬度 lon ,lat,司机手上也有定位终端 slon,slat.
要求获得5公里之内 获得服务器认证,字段 allow 排第一
有手机的 字段 mobile 排第二,其它排第三 ,然后每个分类都按距离排序;
5公里之外只按距离排序再把上面排序完的司机列表取出最近的4个空闲司机的放在最上面,其余数据顺序不变这不是危言耸听,我只是想找个简单的方法把那4个放上面...............