select q.dwmc,z.tjsj,z.lj_r,z.lj_y,z.lj_n
from mw_app.zxjc_jcqyb q,mw_app.zxjc_zbsjb z
where q.dwbm = '01' and q.obj_id = z.qy_id现在查询结果是 dwmc tjsj lj_r lj_y lj_n
区域1 10 96 96 96
区域1 9 97 97 97
区域2 10 98 98 98
区域2 9 99 99 99
区域3 9 95 95 95现在要查询出:每个区域只能去出一条记录,根据tjsj的大小决定(例如区域1只能取出
区域1 10 96 96 96)的记录求高手解决
from mw_app.zxjc_jcqyb q,mw_app.zxjc_zbsjb z
where q.dwbm = '01' and q.obj_id = z.qy_id现在查询结果是 dwmc tjsj lj_r lj_y lj_n
区域1 10 96 96 96
区域1 9 97 97 97
区域2 10 98 98 98
区域2 9 99 99 99
区域3 9 95 95 95现在要查询出:每个区域只能去出一条记录,根据tjsj的大小决定(例如区域1只能取出
区域1 10 96 96 96)的记录求高手解决
select tt.*
from (
select t.dwmc,t.tjsj,t.lj_r,t.lj_y,t.lj_n,row_number() over(partition by t.dwmc order by t.tjsj) rn
from t)tt
where tt.rn = 1
with t as (
select '区域1' as dwmc, 10 as tjsj, 96 as lj_r, 96 as lj_y, 96 as lj_n from dual
union all
select '区域1', 9, 97, 97, 97 from dual
union all
select '区域2', 10, 98, 98, 98 from dual
union all
select '区域2', 9, 99, 99, 99 from dual
union all
select '区域3', 9, 95, 95, 95 from dual
)
select dwmc,tjsj,lj_r,lj_y,lj_n from (
select dwmc,tjsj,lj_r,lj_y,lj_n,DENSE_RANK() OVER (partition by dwmc Order By tjsj Desc ) as rank from t
) where rank = 1DWMC TJSJ LJ_R LJ_Y LJ_N
----- ---------------------- ---------------------- ---------------------- ----------------------
区域1 10 96 96 96
区域2 10 98 98 98
区域3 9 95 95 95
SELECT '区域1'dwmc,10 tjsj,96 lj_r,96 lj_y,96 lj_n FROM dual
UNION ALL
SELECT '区域1', 9 ,97 ,97, 97 FROM dual
UNION ALL
SELECT '区域2', 10 ,98 ,98 ,98 FROM dual
UNION ALL
SELECT '区域2', 9 ,99, 99,99 FROM dual
UNION ALL
SELECT '区域3', 9 ,95, 95 ,95 FROM dual
)
SELECT dwmc,tjsj,lj_r,lj_y,lj_n FROM
(
SELECT dwmc,tjsj,lj_r,lj_y,lj_n, Row_Number() over (PARTITION BY dwmc ORDER BY tjsj desc)rn FROM t
)tt
WHERE tt.rn=1;
SELECT * FROM t t1 WHERE EXISTS
(
SELECT 1 FROM
(
SELECT DISTINCT dwmc,Last_Value(tjsj) over (PARTITION BY dwmc ORDER BY tjsj rows between unbounded preceding and unbounded following )tjsj FROM t
)
WHERE dwmc=t1.dwmc AND tjsj=t1.tjsj
)
数据部分借用2楼的数据with t as (
select '区域1' as dwmc, 10 as tjsj, 96 as lj_r, 96 as lj_y, 96 as lj_n from dual
union all
select '区域1', 9, 97, 97, 97 from dual
union all
select '区域2', 10, 98, 98, 98 from dual
union all
select '区域2', 9, 99, 99, 99 from dual
union all
select '区域3', 9, 95, 95, 95 from dual
)分析函数select t1.* from
(select dwmc,tjsj,lj_r,lj_y,lj_n,dense_rank() over(partition by dwmc order by tjsj desc) as rn from t) t1
where t1.rn=1解法2:
对于目前的数据,以下代码也可以解决。select t.* from t , (select dwmc,max(tjsj) tj from t group by dwmc) t1 where
t.dwmc=t1.dwmc and t.tjsj=t1.tj但是这样的情况就是你的dwmc与max(tjsj) 在表中不能有同时有重复的值
如果有重复的值,就需要再做处理了所以啊还是分析函数好用