求一条sql语句

Question

表结构为 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实现？

Answer 1

WITH tb AS
(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

Answer 2

借用楼上的数据，并改稍微修改下。SELECT JH, DM, ny1 + ROWNUM - 1, xs1, xs2 FROM tb connect by ROWNUM <= ny2 - ny1 + 1

Answer 3

create table t1
 (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;如果数据记录不是很大，可以看下。

Answer 4

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)

Answer 5

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