SELECT * FROM ( SELECT t.*, Row_Number()over(ORDER BY Abs(20071030-nvl2(t.pmrq,t.pmrq,0))) rn FROM TAB t ) WHERE rn=1
select * from tab t where t.pmrq= least(20071030-nvl2(t.pmrq,t.pmrq,0))
还可以用子查询. select * from tab t where t.pmrq=(select max(pmrq) from tab a where a.pmrq<='20071030')
select * from (select t.*, to_date(t.pmrq,'yyyymmdd')-to_date('20071030','yyyymmdd') mindt from tab t where t.pmrq order by to_date(t.pmrq,'yyyymmdd')-to_date('20071030','yyyymmdd')) where rownum<=1
select * from tab t where t.pmrq= min(20071030-nvl2(t.pmrq,t.pmrq,0)) and nvl2(t.pmrq,t.pmrq,0)<='20071030'。这样可以不?
SELECT * FROM (SELECT * FROM tab t WHERE t.pmrq < '20071030' ORDER BY pmrq DESC) WHERE rownum < 2;
我是想求比‘20071030’小,但距‘20071030’最近的一条记录。(pmrq为char类型数据) [/Quote] 那还不容易,你对你的数据按照时间降序排列下,然后取第一条 select * from ( select * from tab t where nvl2(t.pmrq,t.pmrq,0)<='20071030' order by t.pmrq desc) where rownum =1
select * from (select t...,max(t.pmrq) from tab t where t.pmrq < '20071030' group by t....)
具体如下: hw pmh pmrq A 100 20070125 A 103 20070230 A 106 20070521 B 101 20070130 B 102 20070214 B 104 20070306 B 105 20070418 C 101 20070130 C 103 20070230 C 105 20070418 C 106 20070521 以上表中hw 除“A、B、C”外,其他未写出,pmh和pmrq均为截取部分,未完全列出,我想求的是各个货物hw 的pmrq距'20071030'最近的一次pmh,该怎么写sql谢谢!!!
with tb as ( select 'A' hw,100 pmh,'20070125' pmrq from dual union all select 'A',103,'20070228' from dual union all select 'A' ,106,'20070521' from dual union all select 'B' ,101 ,'20070130' from dual union all select 'B' ,102 ,'20070214' from dual union all select 'B' ,104 ,'20070306' from dual union all select 'B' ,105 ,'20070418' from dual union all select 'C' ,101 ,'20070130' from dual union all select 'C' ,103 ,'20070228' from dual union all select 'C' ,105 ,'20070418' from dual union all select 'C' ,106 ,'20070521' from dual) select hw,pmh,pmrq from (select hw,pmh,pmrq,to_date(pmrq,'yyyymmdd') pmrq2, row_number() over(partition by hw order by to_date('20071030','yyyymmdd')-to_date(pmrq,'yyyymmdd')) rn from tb) where rn=1 SQL> with tb as 2 ( select 'A' hw,100 pmh,'20070125' pmrq from dual union all 3 select 'A',103,'20070228' from dual union all 4 select 'A' ,106,'20070521' from dual union all 5 select 'B' ,101 ,'20070130' from dual union all 6 select 'B' ,102 ,'20070214' from dual union all 7 select 'B' ,104 ,'20070306' from dual union all 8 select 'B' ,105 ,'20070418' from dual union all 9 select 'C' ,101 ,'20070130' from dual union all 10 select 'C' ,103 ,'20070228' from dual union all 11 select 'C' ,105 ,'20070418' from dual union all 12 select 'C' ,106 ,'20070521' from dual) 13 select hw,pmh,pmrq 14 from (select hw,pmh,pmrq,to_date(pmrq,'yyyymmdd') pmrq2, 15 row_number() over(partition by hw order by to_date('20071030','yyyymmdd')-to_date(pmrq,'yyyymmdd')) rn 16 from tb) 17 where rn=1 18 /
HW PMH PMRQ -- ---------- -------- A 106 20070521 B 105 20070418 C 106 20070521
哦,打错了,是20070220,谢谢,pmrq是char类型数据
谢谢18楼的,我这用的是oracle8i的,好像不支持 with as
SQL> with tb as 2 ( select 'A' hw,100 pmh,'20070125' pmrq from dual union all 3 select 'A',103,'20070228' from dual union all 4 select 'A' ,106,'20070521' from dual union all 5 select 'B' ,101 ,'20070130' from dual union all 6 select 'B' ,102 ,'20070214' from dual union all 7 select 'B' ,104 ,'20070306' from dual union all 8 select 'B' ,105 ,'20070418' from dual union all 9 select 'C' ,101 ,'20070130' from dual union all 10 select 'C' ,103 ,'20070228' from dual union all 11 select 'C' ,105 ,'20070418' from dual union all 12 select 'C' ,106 ,'20070521' from dual) --这是我模拟的数据而已 select hw,pmh,pmrq from (select hw,pmh,pmrq,to_date(pmrq,'yyyymmdd') pmrq2, row_number() over(partition by hw order by to_date('20071030','yyyymmdd')-to_date(pmrq,'yyyymmdd')) rn from tb) where rn=1 --你把tb换成你的表名就是
如果分析函数不支持 select a.hw,a.pmh,a.pmrq from tb a, (select hw,max(to_date(pmrq,'yyyymmdd')) pmrq2 from tb where to_date(pmrq,'yyyymmdd')<=to_date('20071030','yyyymmdd') group by hw ) b where a.hw=b.hw and to_date(pmrq,'yyyymmdd')=b.pmrq2
SELECT * FROM (
SELECT t.*, Row_Number()over(ORDER BY Abs(20071030-nvl2(t.pmrq,t.pmrq,0))) rn FROM TAB t
)
WHERE rn=1
select * from tab t where t.pmrq=(select max(pmrq) from tab a where a.pmrq<='20071030')
from (select t.*,
to_date(t.pmrq,'yyyymmdd')-to_date('20071030','yyyymmdd') mindt
from tab t where t.pmrq
order by to_date(t.pmrq,'yyyymmdd')-to_date('20071030','yyyymmdd'))
where rownum<=1
nvl2(t.pmrq,t.pmrq,0)<='20071030'。这样可以不?
不行吧。。你是求’20071030‘和nvl2(t.pmrq,t.pmrq,0)小的那个吗?
不行吧。。你是求’20071030‘和nvl2(t.pmrq,t.pmrq,0)小的那个吗?
[/Quote]
我是想求比‘20071030’小,但距‘20071030’最近的一条记录。(pmrq为char类型数据)
[/Quote]
那还不容易,你对你的数据按照时间降序排列下,然后取第一条
select * from (
select * from tab t where nvl2(t.pmrq,t.pmrq,0)<='20071030' order by t.pmrq desc)
where rownum =1
(select t...,max(t.pmrq)
from tab t where t.pmrq < '20071030' group by t....)
hw pmh pmrq
A 100 20070125
A 103 20070230
A 106 20070521
B 101 20070130
B 102 20070214
B 104 20070306
B 105 20070418
C 101 20070130
C 103 20070230
C 105 20070418
C 106 20070521
以上表中hw 除“A、B、C”外,其他未写出,pmh和pmrq均为截取部分,未完全列出,我想求的是各个货物hw
的pmrq距'20071030'最近的一次pmh,该怎么写sql谢谢!!!
( select 'A' hw,100 pmh,'20070125' pmrq from dual union all
select 'A',103,'20070228' from dual union all
select 'A' ,106,'20070521' from dual union all
select 'B' ,101 ,'20070130' from dual union all
select 'B' ,102 ,'20070214' from dual union all
select 'B' ,104 ,'20070306' from dual union all
select 'B' ,105 ,'20070418' from dual union all
select 'C' ,101 ,'20070130' from dual union all
select 'C' ,103 ,'20070228' from dual union all
select 'C' ,105 ,'20070418' from dual union all
select 'C' ,106 ,'20070521' from dual)
select hw,pmh,pmrq
from (select hw,pmh,pmrq,to_date(pmrq,'yyyymmdd') pmrq2,
row_number() over(partition by hw order by to_date('20071030','yyyymmdd')-to_date(pmrq,'yyyymmdd')) rn
from tb)
where rn=1
SQL> with tb as
2 ( select 'A' hw,100 pmh,'20070125' pmrq from dual union all
3 select 'A',103,'20070228' from dual union all
4 select 'A' ,106,'20070521' from dual union all
5 select 'B' ,101 ,'20070130' from dual union all
6 select 'B' ,102 ,'20070214' from dual union all
7 select 'B' ,104 ,'20070306' from dual union all
8 select 'B' ,105 ,'20070418' from dual union all
9 select 'C' ,101 ,'20070130' from dual union all
10 select 'C' ,103 ,'20070228' from dual union all
11 select 'C' ,105 ,'20070418' from dual union all
12 select 'C' ,106 ,'20070521' from dual)
13 select hw,pmh,pmrq
14 from (select hw,pmh,pmrq,to_date(pmrq,'yyyymmdd') pmrq2,
15 row_number() over(partition by hw order by to_date('20071030','yyyymmdd')-to_date(pmrq,'yyyymmdd')) rn
16 from tb)
17 where rn=1
18 /
HW PMH PMRQ
-- ---------- --------
A 106 20070521
B 105 20070418
C 106 20070521
2 ( select 'A' hw,100 pmh,'20070125' pmrq from dual union all
3 select 'A',103,'20070228' from dual union all
4 select 'A' ,106,'20070521' from dual union all
5 select 'B' ,101 ,'20070130' from dual union all
6 select 'B' ,102 ,'20070214' from dual union all
7 select 'B' ,104 ,'20070306' from dual union all
8 select 'B' ,105 ,'20070418' from dual union all
9 select 'C' ,101 ,'20070130' from dual union all
10 select 'C' ,103 ,'20070228' from dual union all
11 select 'C' ,105 ,'20070418' from dual union all
12 select 'C' ,106 ,'20070521' from dual)
--这是我模拟的数据而已
select hw,pmh,pmrq
from (select hw,pmh,pmrq,to_date(pmrq,'yyyymmdd') pmrq2,
row_number() over(partition by hw order by to_date('20071030','yyyymmdd')-to_date(pmrq,'yyyymmdd')) rn
from tb)
where rn=1
--你把tb换成你的表名就是
如果分析函数不支持 select a.hw,a.pmh,a.pmrq
from tb a,
(select hw,max(to_date(pmrq,'yyyymmdd')) pmrq2 from tb where to_date(pmrq,'yyyymmdd')<=to_date('20071030','yyyymmdd') group by hw ) b
where a.hw=b.hw and to_date(pmrq,'yyyymmdd')=b.pmrq2