数据如下:
20100201 陕A00039
20100202 陕A00039
20100203 陕A00039
20100204 陕A00039
20100205 陕A00039
20100206 陕A00039
20100207 陕A00039
20100208 陕A00039
20100210 陕A00039
20100211 陕A00039
20100212 陕A00039
20100213 陕A00039
20100214 陕A00039
20100215 陕A00039
20100216 陕A00039
20100217 陕A00039
20100220 陕A00039
20100221 陕A00039
20100222 陕A00039
20100223 陕A00039希望得到的结果:
陕A00039 8 20100201,20100202,20100203,20100204,20100205,20100206,20100207,20100208
陕A00039 8 20100210,20100211,20100212,20100213,20100214,20100215,20100216,20100217
陕A00039 4 20100220,20100221,20100222,20100223
20100201 陕A00039
20100202 陕A00039
20100203 陕A00039
20100204 陕A00039
20100205 陕A00039
20100206 陕A00039
20100207 陕A00039
20100208 陕A00039
20100210 陕A00039
20100211 陕A00039
20100212 陕A00039
20100213 陕A00039
20100214 陕A00039
20100215 陕A00039
20100216 陕A00039
20100217 陕A00039
20100220 陕A00039
20100221 陕A00039
20100222 陕A00039
20100223 陕A00039希望得到的结果:
陕A00039 8 20100201,20100202,20100203,20100204,20100205,20100206,20100207,20100208
陕A00039 8 20100210,20100211,20100212,20100213,20100214,20100215,20100216,20100217
陕A00039 4 20100220,20100221,20100222,20100223
time name
20100201 陕A00039
则
select name,count(name),wmsys.wm_concat(time)
from tab
group by name
where 20100210 > time and time > 20100201
union all
select name,count(name),wmsys.wm_concat(time)
from tab
group by name
where 20100220 > time and time > 20100211
union all
select name,count(name),wmsys.wm_concat(time)
from tab
group by name
where 20100228 > time and time > 20100221
select name,count(name),wmsys.wm_concat(time)
from tab
group by name
where '20100210' > time and time > '20100201'
union all
select name,count(name),wmsys.wm_concat(time)
from tab
group by name
where '20100220' > time and time > '20100211'
union all
select name,count(name),wmsys.wm_concat(time)
from tab
group by name
where '20100228' > time and time > '20100221'
select car_id, count(*) cnt, wm_concat(to_char(r_date, 'yyyymmdd')) datestr
from (select r_date, car_id, connect_by_root r_date rootdate
from (select r_date,
r_date - lag(r_date) over(partition by car_id order by r_date) diff,
car_id
from (select to_date(r_date, 'yyyymmdd') r_date, t.car_id
from car t))
connect by prior r_date = r_date - 1
start with diff <> 1
or diff is null)
group by car_id, rootdate;
动态计算的方法:
SQL> DESC t1;
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
A VARCHAR2(10) Y
B VARCHAR2(10) Y
SQL> SELECT a,b from t1 t;
A B
---------- ----------
20100201 陕A00039
20100202 陕A00039
20100203 陕A00039
20100204 陕A00039
20100205 陕A00039
20100206 陕A00039
20100207 陕A00039
20100208 陕A00039
20100210 陕A00039
20100211 陕A00039
20100212 陕A00039
20100213 陕A00039
20100214 陕A00039
20100215 陕A00039
20100216 陕A00039
20100217 陕A00039
20100220 陕A00039
20100221 陕A00039
20100222 陕A00039
20100223 陕A00039
A B
---------- ----------
20100226 陕A00039
20100225 陕A00039
22 rows selected
SQL> SELECT B, COUNT(1), WMSYS.WM_CONCAT(B)
2 FROM (SELECT A, A - RN XX, B
3 FROM (SELECT A, B, ROW_NUMBER() OVER(ORDER BY A, B) RN FROM T1)
4 GROUP BY A, A - RN, B
5 ORDER BY 1)
6 GROUP BY B, XX;
B COUNT(1) WMSYS.WM_CONCAT(B)
---------- ---------- --------------------------------------------------------------------------------
陕A00039 8 陕A00039,陕A00039,陕A00039,陕A00039,陕A00039,陕A00039,陕A00039,陕A00039
陕A00039 8 陕A00039,陕A00039,陕A00039,陕A00039,陕A00039,陕A00039,陕A00039,陕A00039
陕A00039 4 陕A00039,陕A00039,陕A00039,陕A00039
陕A00039 2 陕A00039,陕A00039