一条数据变N条，求SQL

显示全部楼层 · 2009-10-9 08:28:00

有一张表两个字段一个是设备id,另外一个是对这个设备的检查日期。
数据如下：
id , 日期
001,20080901
001,20090909
001,20100501
001,20100601
002,20100505
002,20100406
002,20110101
002,20110202
002,20110222
。。。。。。。。。。
现在想对于每一条记录都变成N(N CREATE TABLE t_tab
2AS
3WITH tt AS(
4SELECT '001' t_id,20080901 t_time FROM DUAL UNION ALL
5SELECT '001',20090909 FROM DUAL UNION ALL
6SELECT '001',20100501 FROM DUAL UNION ALL
7SELECT '001',20100601 FROM DUAL UNION ALL
8SELECT '002',20100505 FROM DUAL UNION ALL
9SELECT '002',20100406 FROM DUAL UNION ALL
10SELECT '002',20110101 FROM DUAL UNION ALL
11SELECT '002',20110202 FROM DUAL UNION ALL
12SELECT '002',20110222 FROM DUAL
13)
14SELECT * FROM tt;

Table created

SQL> SELECT * FROM t_tab;

T_ID T_TIME
---- ----------
00120080901
00120090909
00120100501
00120100601
00220100505
00220100406
00220110101
00220110202
00220110222

9 rows selected

SQL> WITH t AS
2(
3SELECT ROWNUM num,t_id,t_time
4FROM t_tab
5)
6SELECT DISTINCT num,ti,tt
7FROM t
8MODEL
9RETURN UPDATED ROWS
10PARTITION BY(num)
11DIMENSION BY(0 AS n)
12MEASURES(
13'xxx' AS ti,t_id,
14'yyyyyyyy' AS tt,
15
t_time,
16
LAG(t_time,1,t_time) OVER(PARTITION BY t_id ORDER BY t_time) t1,
17
LAG(t_time,2,t_time) OVER(PARTITION BY t_id ORDER BY t_time) t2,
18
LAG(t_time,3,t_time) OVER(PARTITION BY t_id ORDER BY t_time) t3
19)
20RULES UPSERT ALL(
21ti[1] = t_id[0],
22ti[2] = t_id[0],
23ti[3] = t_id[0],
24ti[4] = t_id[0],
25tt[1] = t_time[0],
26tt[2] = t1[0],
27tt[3] = t2[0],
28tt[4] = t3[0]
29)
30ORDER BY 1,3
31;

NUM TITT
---------- --- --------
1 001 20080901
2 001 20080901
2 001 20090909
3 001 20080901
3 001 20090909
3 001 20100501
4 001 20080901
4 001 20090909
4 001 20100501
4 001 20100601
5 002 20100406
5 002 20100505
6 002 20100406
7 002 20100406
7 002 20100505
7 002 20110101
8 002 20100406
8 002 20100505
8 002 20110101
8 002 20110202
9 002 20100505
9 002 20110101
9 002 20110202
9 002 20110222

24 rows selected

千问 · 2009-10-9 08:28:00

多谢，楼上。

千问 · 2009-10-9 08:28:00

with t as (
select '001' col_id,20080901 col_dt from dual union all
select '001',20090909 from dual union all
select '001',20100501 from dual union all
select '001',20100601 from dual union all
select '002',20100505 from dual union all
select '002',20100406 from dual union all
select '002',20110101 from dual union all
select '002',20110202 from dual union all
select '002',20110222 from dual)
select col_id,col_dt from (
select col_id,col_dt,row_number() over (partition by col_id order by col_dt) rn from t)
connect by rn=prior rn-1
and col_id=prior col_id
and level= r --第一个出现 total 次，第二个出现 total-2次...
AND total- rn -r <4 --控制显示最近4行
ORDER BYcol_id,r desc,rn

千问 · 2009-10-9 08:28:00

2楼的思路没人写？
SELECT * FROM (
SELECT t2.*
,ROW_NUMBER() OVER(PARTITION BY t1.ROWID ORDER BY t2.t_time DESC) rn
FROM t_tab t1, t_tab t2
WHERE t1.t_id=t2.t_id AND t2.t_time<=t1.t_time
)
WHERE rn<=4