有这样一个表
id s_cid i_zhuangtaibm d_chaobiaorq
1 1 00 20110204
2 1 01 20110305
3 2 05 20110605
4 2 01 20110609
5 2 00 20110705
6 1 03 20110509
7 1 00 20110704
… … … …
我想用一条sql语句使它变成下面的显示结果。
s_cid i_zhuangtaibm1 最近日期1 i_zhuangtaibm2 最近日期2
1 03 20110509 01 20110305
2 01 20110609 05 20110605
… … … … …
请问这个sql语句该肿么写?是oracle的
id s_cid i_zhuangtaibm d_chaobiaorq
1 1 00 20110204
2 1 01 20110305
3 2 05 20110605
4 2 01 20110609
5 2 00 20110705
6 1 03 20110509
7 1 00 20110704
… … … …
我想用一条sql语句使它变成下面的显示结果。
s_cid i_zhuangtaibm1 最近日期1 i_zhuangtaibm2 最近日期2
1 03 20110509 01 20110305
2 01 20110609 05 20110605
… … … … …
请问这个sql语句该肿么写?是oracle的
1 03 20110509 01 20110305
2 01 20110609 05 20110605你要得结果是怎么来的?
比如说s_cid=1,最近日期1,最近日期2怎么是20110509 和 20110305,按我理解应该是20110509和20110704
先写一个,没有第二,第三近的日期的数据,过滤掉了SQL> WITH t AS (
2 SELECT '1' tid,'1' s_cid,'00' i_zhuangtaibm,'20110204' d_chaobiaorq FROM DUAL UNION ALL
3 SELECT '2' tid,'1' s_cid,'01' i_zhuangtaibm,'20110305' d_chaobiaorq FROM DUAL UNION ALL
4 SELECT '3' tid,'2' s_cid,'05' i_zhuangtaibm,'20110605' d_chaobiaorq FROM DUAL UNION ALL
5 SELECT '4' tid,'2' s_cid,'01' i_zhuangtaibm,'20110609' d_chaobiaorq FROM DUAL UNION ALL
6 SELECT '5' tid,'2' s_cid,'00' i_zhuangtaibm,'20110705' d_chaobiaorq FROM DUAL UNION ALL
7 SELECT '6' tid,'1' s_cid,'03' i_zhuangtaibm,'20110509' d_chaobiaorq FROM DUAL UNION ALL
8 SELECT '7' tid,'1' s_cid,'00' i_zhuangtaibm,'20110704' d_chaobiaorq FROM DUAL
9 )
10 SELECT m.s_cid,
11 MAX(DECODE(m.rn, 2, m.i_zhuangtaibm)) i_zhuangtaibm1,
12 MAX(DECODE(m.rn, 2, m.d_chaobiaorq)) 最近日期1,
13 MAX(DECODE(m.rn, 3, m.i_zhuangtaibm)) i_zhuangtaibm2,
14 MAX(DECODE(m.rn, 3, m.d_chaobiaorq)) 最近日期2
15 FROM (SELECT t.*,
16 ROW_NUMBER() OVER(PARTITION BY t.s_cid ORDER BY t.d_chaobiaorq DESC) rn
17 FROM t) m
18 WHERE rn > 1
19 AND rn <= 3
20 GROUP BY m.s_cid
21 ;S_CID I_ZHUANGTAIBM1 最近日期1 I_ZHUANGTAIBM2 最近日期2
----- -------------- --------- -------------- ---------
1 03 20110509 01 20110305
2 01 20110609 05 20110605