表结构为 JH(字符型)DM(字符型) NY1(字符型) NY2(字符型) XS1(数值型)XS2(数值型)
假设有一行记录
SHS1-1 SH0001 201201 201206 1 1
我要得到以下这样的查询结果:
JH DM NY XS1 XS2
SHS1-1 SH0001 201201 1 1
SHS1-1 SH0001 201202 1 1
SHS1-1 SH0001 201203 1 1
SHS1-1 SH0001 201204 1 1
SHS1-1 SH0001 201205 1 1
SHS1-1 SH0001 201206 1 1
请问能否用一句SQL实现?
假设有一行记录
SHS1-1 SH0001 201201 201206 1 1
我要得到以下这样的查询结果:
JH DM NY XS1 XS2
SHS1-1 SH0001 201201 1 1
SHS1-1 SH0001 201202 1 1
SHS1-1 SH0001 201203 1 1
SHS1-1 SH0001 201204 1 1
SHS1-1 SH0001 201205 1 1
SHS1-1 SH0001 201206 1 1
请问能否用一句SQL实现?
(SELECT 'SHS1-1' JH,'SH0001' DM, '201201' NY1,'201206' NY2, 1 XS1, 1 XS2 FROM dual)
SELECT JH,DM,ny1+ROWNUM-1,xs1,xs2 FROM tb t LEFT JOIN User_Objects k ON 1=1
WHERE ROWNUM<=ny2-ny1+1
(a varchar2(4),
b varchar2(4),
c varchar2(4),
d varchar2(4),
e integer,
f integer);
insert into t1 (A, B, C, D, E, F) values ('a', 'b', 'c', 'e', 201201, 1);
insert into t1 (A, B, C, D, E, F) values ('a', 'b', 'c', 'e', 201202, 1);
insert into t1 (A, B, C, D, E, F) values ('a', 'b', 'c', 'e', 201203, 1);
insert into t1 (A, B, C, D, E, F) values ('a', 'b', 'c', 'e', 201204, 1);
insert into t1 (A, B, C, D, E, F) values ('a', 'b', 'c', 'e', 201205, 1);
insert into t1 (A, B, C, D, E, F) values ('a1', 'b1', 'c1', 'e1', 201206, 1);
select t2.a, t2.b, t2.c, t2.d, sum(t2.min_e), sum(t2.max_e), t2.f
from (select a, b, c, d, min(e) min_e, 0 max_e, f
from t1
where a = 'a'
group by a, b, c, d, f
union all
select a, b, c, d, 0 min_e, max(e) max_e, f
from t1
where a = 'a'
group by a, b, c, d, f) t2
group by t2.a, t2.b, t2.c, t2.d, t2.f;如果数据记录不是很大,可以看下。
with temp as
(select 'SHS1-1' JH, 'SH0001' DM, '201201' NY1, '201206' NY2, 1 XS1, 1 XS2
from dual)
select JH, DM, NY1 + level - 1, XS1, XS2
from temp
connect by level < (abs(NY1 - NY2) + 2)
create table t(
JH VARCHAR2(50),
DM VARCHAR2(50),
NY1 VARCHAR2(50),
NY2 VARCHAR2(50),
XS1 INT,
XS2 INT
);
/
INSERT INTO T VALUES ('SHS1-1','SH0001','201201','201206',1,1);
INSERT INTO T VALUES ('SHS1-2','SH0002','201203','201207',2,2);
COMMIT;
SELECT * FROM T;
JH DM NY1 NY2 XS1 XS2
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------- ---------------------------------------
SHS1-1 SH0001 201201 201206 1 1
SHS1-2 SH0002 201203 201207 2 2SELECT T.JH,T.DM,TO_CHAR(ADD_MONTHS(TO_DATE(T.NY1,'YYYYMM'),R),'YYYYMM') NY,T.XS1,T.XS2
FROM T,
(SELECT ROWNUM-1 R
FROM DUAL
CONNECT BY ROWNUM <= (SELECT MAX(MONTHS_BETWEEN(TO_DATE(NY2, 'YYYYMM'),
TO_DATE(NY1, 'YYYYMM')))+1
FROM T)) T1
WHERE MONTHS_BETWEEN(TO_DATE(NY2, 'YYYYMM'),
TO_DATE(NY1, 'YYYYMM'))>=T1.R ORDER BY T.JH,NY;
JH DM NY XS1 XS2
-------------------------------------------------- -------------------------------------------------- ------ --------------------------------------- ---------------------------------------
SHS1-1 SH0001 201201 1 1
SHS1-1 SH0001 201202 1 1
SHS1-1 SH0001 201203 1 1
SHS1-1 SH0001 201204 1 1
SHS1-1 SH0001 201205 1 1
SHS1-1 SH0001 201206 1 1
SHS1-2 SH0002 201203 2 2
SHS1-2 SH0002 201204 2 2
SHS1-2 SH0002 201205 2 2
SHS1-2 SH0002 201206 2 2
SHS1-2 SH0002 201207 2 2
11 rows selected