需求不是很明确WITH DATASET AS ( SELECT '2012-01-31' SSRQ, '320623932' JGM, '*******' ID, '张三' NAME FROM DUAL UNION ALL SELECT '2012-12-04' SSRQ, '320623001' JGM, '*******' ID, '张三' NAME FROM DUAL UNION ALL SELECT '2012-12-06' SSRQ, '320623932' JGM, '*******' ID, '张三' NAME FROM DUAL )SELECT T.*, TO_DATE( LEAD(SSRQ) OVER(PARTITION BY ID ORDER BY SSRQ), 'YYYY-MM-DD')-1 LSSRQ FROM DATASET T;
WITH t AS ( SELECT '2012-01-30' SSRQ, '320623932' JGM, '*******' ID, '张三' NAME FROM DUAL UNION ALL SELECT '2012-01-31' SSRQ, '320623932' JGM, '*******' ID, '张三' NAME FROM DUAL UNION ALL SELECT '2012-12-01' SSRQ, '320623001' JGM, '*******' ID, '张三' NAME FROM DUAL UNION ALL SELECT '2012-12-02' SSRQ, '320623001' JGM, '*******' ID, '张三' NAME FROM DUAL UNION ALL SELECT '2012-12-04' SSRQ, '320623001' JGM, '*******' ID, '李四' NAME FROM DUAL UNION ALL SELECT '2012-12-05' SSRQ, '320623001' JGM, '*******' ID, '李四' NAME FROM DUAL ) select jgm, name, min(b.ssrq) minrq, max(b.ssrq) maxrq from (select a.*, (to_date(a.ssrq, 'yyyy/mm/dd') + rownum * (-1)) cc, rownum from (select distinct jgm, name, ssrq from t where id = '*******' order by jgm, name, ssrq) a) b group by jgm, name, cc;
WITH t AS ( SELECT '2012-01-30' SSRQ, '320623932' JGM, '*******' ID, '张三' NAME FROM DUAL UNION ALL SELECT '2012-01-31' SSRQ, '320623932' JGM, '*******' ID, '张三' NAME FROM DUAL UNION ALL SELECT '2012-12-01' SSRQ, '320623001' JGM, '*******' ID, '张三' NAME FROM DUAL UNION ALL SELECT '2012-12-02' SSRQ, '320623001' JGM, '*******' ID, '张三' NAME FROM DUAL UNION ALL SELECT '2012-12-04' SSRQ, '320623001' JGM, '*******' ID, '李四' NAME FROM DUAL UNION ALL SELECT '2012-12-05' SSRQ, '320623001' JGM, '*******' ID, '李四' NAME FROM DUAL ) select jgm, name, min(b.ssrq) minrq, max(b.ssrq) maxrq from (select a.*, (to_date(a.ssrq, 'yyyy/mm/dd') + rownum * (-1)) cc, rownum from (select distinct jgm, name, ssrq from t where id = '*******' order by jgm, name, ssrq) a) b group by jgm, name, cc;[/code]结果集确实是我想要的,但是sql中的rownum不能直接用吧?
不要意思,我这边用的是db2数据库,rownum函数不一样
不要意思,我这边用的是db2数据库,rownum函数不一样2005以上可以使用row_number() over(order by ) as rnn,
db2 应该也有row_number() over()的
不要意思,我这边用的是db2数据库,rownum函数不一样 select jgm, name, min(b.ssrq) minrq, max(b.ssrq) maxrq from (select a.*, (to_date(a.ssrq, 'yyyy/mm/dd') + rnn * (-1)) cc from (select distinct jgm, name, ssrq, row_number() over(order by jgm, name, ssrq) rnn from t where id = '*******') a) b group by jgm, name, cc;可以的吧
我不知道有没有理解对你的意思WITH T AS (SELECT TO_DATE('01/01/2014', 'DD/MM/YYYY') DT, '320623932' JGM FROM DUAL UNION ALL SELECT TO_DATE('02/01/2014', 'DD/MM/YYYY') DT, '320623932' JGM FROM DUAL UNION ALL SELECT TO_DATE('06/01/2014', 'DD/MM/YYYY') DT, '320623932' JGM FROM DUAL UNION ALL SELECT TO_DATE('03/01/2014', 'DD/MM/YYYY') DT, '320623001' JGM FROM DUAL UNION ALL SELECT TO_DATE('04/01/2014', 'DD/MM/YYYY') DT, '320623001' JGM FROM DUAL UNION ALL SELECT TO_DATE('05/01/2014', 'DD/MM/YYYY') DT, '320623932' JGM FROM DUAL UNION ALL SELECT TO_DATE('07/01/2014', 'DD/MM/YYYY') DT, '320623932' JGM FROM DUAL) SELECT T3.DT S_DT, NVL((SELECT MAX(DT) FROM T WHERE T.DT >= T3.DT AND T.DT < NVL(T3.N_DT, T.DT + 1)), T3.DT), T3.JGM FROM (SELECT T2.*, LEAD(T2.DT) OVER(ORDER BY T2.DT) N_DT FROM (SELECT T1.*, LAG(JGM) OVER(ORDER BY T1.DT) P_JGM FROM T T1 ORDER BY DT) T2 WHERE T2.P_JGM IS NULL OR T2.P_JGM <> T2.JGM) T3;
(
SELECT '2012-01-31' SSRQ, '320623932' JGM, '*******' ID, '张三' NAME FROM DUAL
UNION ALL
SELECT '2012-12-04' SSRQ, '320623001' JGM, '*******' ID, '张三' NAME FROM DUAL
UNION ALL
SELECT '2012-12-06' SSRQ, '320623932' JGM, '*******' ID, '张三' NAME FROM DUAL
)SELECT T.*, TO_DATE( LEAD(SSRQ) OVER(PARTITION BY ID ORDER BY SSRQ), 'YYYY-MM-DD')-1 LSSRQ FROM DATASET T;
(
SELECT '2012-01-30' SSRQ, '320623932' JGM, '*******' ID, '张三' NAME
FROM DUAL
UNION ALL
SELECT '2012-01-31' SSRQ, '320623932' JGM, '*******' ID, '张三' NAME
FROM DUAL
UNION ALL
SELECT '2012-12-01' SSRQ, '320623001' JGM, '*******' ID, '张三' NAME
FROM DUAL
UNION ALL
SELECT '2012-12-02' SSRQ, '320623001' JGM, '*******' ID, '张三' NAME
FROM DUAL
UNION ALL
SELECT '2012-12-04' SSRQ, '320623001' JGM, '*******' ID, '李四' NAME
FROM DUAL
UNION ALL
SELECT '2012-12-05' SSRQ, '320623001' JGM, '*******' ID, '李四' NAME
FROM DUAL
)
select jgm, name, min(b.ssrq) minrq, max(b.ssrq) maxrq
from (select a.*,
(to_date(a.ssrq, 'yyyy/mm/dd') + rownum * (-1)) cc,
rownum
from (select distinct jgm, name, ssrq
from t
where id = '*******'
order by jgm, name, ssrq) a) b
group by jgm, name, cc;
--------- ---- ---------- ----------
320623932 张三 2012-01-30 2012-01-31
320623001 李四 2012-12-04 2012-12-05
320623001 张三 2012-12-01 2012-12-02
SQL> 是不是你想要的?
(
SELECT '2012-01-30' SSRQ, '320623932' JGM, '*******' ID, '张三' NAME
FROM DUAL
UNION ALL
SELECT '2012-01-31' SSRQ, '320623932' JGM, '*******' ID, '张三' NAME
FROM DUAL
UNION ALL
SELECT '2012-12-01' SSRQ, '320623001' JGM, '*******' ID, '张三' NAME
FROM DUAL
UNION ALL
SELECT '2012-12-02' SSRQ, '320623001' JGM, '*******' ID, '张三' NAME
FROM DUAL
UNION ALL
SELECT '2012-12-04' SSRQ, '320623001' JGM, '*******' ID, '李四' NAME
FROM DUAL
UNION ALL
SELECT '2012-12-05' SSRQ, '320623001' JGM, '*******' ID, '李四' NAME
FROM DUAL
)
select jgm, name, min(b.ssrq) minrq, max(b.ssrq) maxrq
from (select a.*,
(to_date(a.ssrq, 'yyyy/mm/dd') + rownum * (-1)) cc,
rownum
from (select distinct jgm, name, ssrq
from t
where id = '*******'
order by jgm, name, ssrq) a) b
group by jgm, name, cc;[/code]结果集确实是我想要的,但是sql中的rownum不能直接用吧?
from (select a.*, (to_date(a.ssrq, 'yyyy/mm/dd') + rnn * (-1)) cc
from (select distinct jgm,
name,
ssrq,
row_number() over(order by jgm, name, ssrq) rnn
from t
where id = '*******') a) b
group by jgm, name, cc;可以的吧
(SELECT TO_DATE('01/01/2014', 'DD/MM/YYYY') DT, '320623932' JGM
FROM DUAL
UNION ALL
SELECT TO_DATE('02/01/2014', 'DD/MM/YYYY') DT, '320623932' JGM
FROM DUAL
UNION ALL
SELECT TO_DATE('06/01/2014', 'DD/MM/YYYY') DT, '320623932' JGM
FROM DUAL
UNION ALL
SELECT TO_DATE('03/01/2014', 'DD/MM/YYYY') DT, '320623001' JGM
FROM DUAL
UNION ALL
SELECT TO_DATE('04/01/2014', 'DD/MM/YYYY') DT, '320623001' JGM
FROM DUAL
UNION ALL
SELECT TO_DATE('05/01/2014', 'DD/MM/YYYY') DT, '320623932' JGM
FROM DUAL
UNION ALL
SELECT TO_DATE('07/01/2014', 'DD/MM/YYYY') DT, '320623932' JGM
FROM DUAL)
SELECT T3.DT S_DT,
NVL((SELECT MAX(DT)
FROM T
WHERE T.DT >= T3.DT
AND T.DT < NVL(T3.N_DT, T.DT + 1)),
T3.DT),
T3.JGM
FROM (SELECT T2.*, LEAD(T2.DT) OVER(ORDER BY T2.DT) N_DT
FROM (SELECT T1.*, LAG(JGM) OVER(ORDER BY T1.DT) P_JGM
FROM T T1
ORDER BY DT) T2
WHERE T2.P_JGM IS NULL
OR T2.P_JGM <> T2.JGM) T3;
我不能保证DB2能执行我的SQL,
太感动了。谢谢各位给与的解答。真是感激不尽啊~我的问题解决了。关于rownum的问题,我也找到了替换~楼上对解答问题的 思考真是让我耳目一新。
太感动了~~~~~~~~说不清的感谢!!!!