请教。用sql表达出连续数据分组,并统计分段中数记录数,起、止等信息。
需求是这样的 一表 t_缴费情况 create table t_缴费情况
(
AAC001 VARCHAR2(10) not null, /*人员編号*/
AAC003 VARCHAR2(10) not null /*缴费人员 */
AAE002 VARCHAR2(6) not null, /*费款期 yyyymm*/
AIC011 NUMBER(8,2), /*已交费金 */
AAE036 DATE /*缴费日期*/
}现在需求:列出每个人在每一段开始,终止年月的缴费月数,如:
小明在2008年1月,2月,3月,4月,7月,8月缴过费
在2009年3月,4月,5月,10月缴过费记录如下:
aac001 aac003 aae002 aic011 aae036
10004 小明 200801 88 2008-01-01
10004 小明 200802 88 2008-02-01
10004 小明 200803 88 2008-03-01
10004 小明 200804 88 2008-04-01
10004 小明 200807 88 2008-07-01
10004 小明 200808 88 2008-08-01
10004 小明 200904 88 2008-04-01
10004 小明 200905 88 2008-05-01
10004 小明 200910 88 2008-10-01
要求结果: 小明 200801-200804 4
小明 200807-200808 2
小明 200904-200905 2
小明 200910-200910 1
请求大虾sql 语句怎么写
需求是这样的 一表 t_缴费情况 create table t_缴费情况
(
AAC001 VARCHAR2(10) not null, /*人员編号*/
AAC003 VARCHAR2(10) not null /*缴费人员 */
AAE002 VARCHAR2(6) not null, /*费款期 yyyymm*/
AIC011 NUMBER(8,2), /*已交费金 */
AAE036 DATE /*缴费日期*/
}现在需求:列出每个人在每一段开始,终止年月的缴费月数,如:
小明在2008年1月,2月,3月,4月,7月,8月缴过费
在2009年3月,4月,5月,10月缴过费记录如下:
aac001 aac003 aae002 aic011 aae036
10004 小明 200801 88 2008-01-01
10004 小明 200802 88 2008-02-01
10004 小明 200803 88 2008-03-01
10004 小明 200804 88 2008-04-01
10004 小明 200807 88 2008-07-01
10004 小明 200808 88 2008-08-01
10004 小明 200904 88 2008-04-01
10004 小明 200905 88 2008-05-01
10004 小明 200910 88 2008-10-01
要求结果: 小明 200801-200804 4
小明 200807-200808 2
小明 200904-200905 2
小明 200910-200910 1
请求大虾sql 语句怎么写
解决方案 »
- 组合函数语法求解!
- 请教各位大锅--oracle 回收站问题
- 有关select查询速度的问题
- sqlldr主键自增的问题
- delete与truncate的区别?
- WIN98下安装了ORACLE8,现有个很简单问题???急急,兄弟们求!!!100分
- 大家好,我现在需要建立一个用户名JOJO,然后让他可以SELECT、UPDATE、DELETE另外一个用户名ANAN下的TABLE_A、TABLE_B、TABLE_C,请问怎么
- 为什么9i的SQLPLUS总是附带着DOS的黑色框?
- 高分求好的Oracle网站
- oracle 如何通过一个表 更新另外一个表 update set……
- 急!向oracle中clob字段插入数据,insert的时候如何循环?
- 高手请进!存储过程授权问题!
from (
select aa.*,to_number(aa.aae002-rownum) kk from
(select a.* from t_缴费情况 a order by a.aac003, a.aae002) aa ) t
group by t.aac003,t.kk
order by count(*) desc
select 10004 aac001,'小明'aac003,'200801' aae002, 88 aic011, date'2008-01-01'aae036 from dual union all
select 10004 aac001,'小明'aac003,'200802' aae002, 88 aic011, date'2008-02-01'aae036 from dual union all
select 10004 aac001,'小明'aac003,'200803' aae002, 88 aic011, date'2008-03-01'aae036 from dual union all
select 10004 aac001,'小明'aac003,'200804' aae002, 88 aic011, date'2008-04-01'aae036 from dual union all
select 10004 aac001,'小明'aac003,'200807' aae002, 88 aic011, date'2008-07-01'aae036 from dual union all
select 10004 aac001,'小明'aac003,'200808' aae002, 88 aic011, date'2008-08-01'aae036 from dual union all
select 10004 aac001,'小明'aac003,'200904' aae002, 88 aic011, date'2008-04-01'aae036 from dual union all
select 10004 aac001,'小明'aac003,'200905' aae002, 88 aic011, date'2008-05-01'aae036 from dual union all
select 10004 aac001,'小明'aac003,'200910' aae002, 88 aic011, date'2008-10-01'aae036 from dual )select aac001,aac003,min(aae002)||'--'||max(aae002),count(1)
from(
select tt.*,add_months(to_date(AAE002,'YYYYMM'),
-1*row_number()over(partition by aac001,aac003 order by aae002))flag
from tt)
group by aac001,aac003,flag
order by 1,2,3AAC001 AAC003 MIN(AAE002)||'--'||MAX(AAE002) COUNT(1)
10004 小明 200801--200804 4
10004 小明 200807--200808 2
10004 小明 200904--200905 2
10004 小明 200910--200910 1
from (
select aa.*,to_number(aa.aae002-rownum) kk from
(select a.* from t_缴费情况 a order by a.aac003, a.aae002) aa ) t
group by t.aac003,t.kk
order by count(*) descAAC003 MIN(T. MAX(T. COUNT(*)
---------- ------ ------ ----------
小明 200801 200804 4
小明 200807 200808 2
小明 200904 200905 2
小明 200910 200910 1
'12006733',
'小明',
'199907',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'199908',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'199909',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'199910',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'199911',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'199912',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200001',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200002',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200003',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200004',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200005',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200006',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200007',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200008',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200009',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200010',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200011',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200012',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200101',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200102',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200103',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200104',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200105',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200106',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200108',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200109',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200110',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200111',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200112',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200201',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200202',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200203',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200204',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200205',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200206',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200207',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200208',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200209',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200210',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200211',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200212',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200301',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200302',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200303',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200304',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200305',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200306',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200307',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200307',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200308',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200309',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200310',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200311',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200312',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200401',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200402',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200403',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200404',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200405',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200406',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200407',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200408',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200409',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200410',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200411',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200412',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200501',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200502',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200503',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200504',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200505',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200506',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200507',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200508',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200509',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200510',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200511',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200512',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200601',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200602',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200603',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200604',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200605',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200606',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200607',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200607',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200608',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200608',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200609',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200610',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200611',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200612',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200701',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200702',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200703',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200704',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200705',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200706',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200707',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200707',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200708',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200708',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200709',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200709',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200710',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200710',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200711',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200712',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200801',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200802',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200803',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200804',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200805',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200806',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200807',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200808',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200809',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200810',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200811',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200812',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200901',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200902',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200903',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200904',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200905',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200906',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200907',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200908',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200909',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200910',
88);
INSERT INTO ttVALUES (
'12006733',
'小明',
'200911',
88);
12006733 小明 200108--200307 24
12006733 小明 200607--200608 2
12006733 小明 200608--200707 12
12006733 小明 200707--200708 2
12006733 小明 200708--200709 2 这三段不知为什么没有连在一起
12006733 小明 200709--200710 2
12006733 小明 200710--200911 26
将row_number改成dense_rank就好了select aac001,aac003,min(aae002)||'--'||max(aae002),count(1)
from(
select tt.*,add_months(to_date(AAE002,'YYYYMM'),
-1*dense_rank()over(partition by aac001,aac003 order by aae002))flag
from tt)
group by aac001,aac003,flag
order by 1,2,3
小明 200801-200806 6
小明 200807-200808 2
小明 200904-200906 2
小明 200907-200907 2
小明 200910-200910 1
substr(aae002,1,4)-1,'04',substr(aae002,1,4)-1,'05',substr(aae002,1,4)-1,
'06',substr(aae002,1,4)-1,substr(aae002,1,4)) nd,
min(aae002) saae002,max(aae002) eaae002,count(1) ylcunt,nvl(aic011,0) aic011
from (
select aac001,aae002,
sum(nvl(aic011,0)) aic011,( select max(min(aae002))
from (
select aac001,aae002,
sum(nvl(aic011,0)) aic011
from tt
where aac001=c_aac001
and aae002 between c_saae001||'07' and c_eaae001||'06'
group by aac001,aae002
)
where aae002<=k.aae002
group by aac001,aab001,aic020) group_aae002,
( select count(sum(decode(sign(Months_between(to_date(aae002,'yyyymm'),to_date(k.aae002,'yyyymm'))),0,1,0)))
from (
select aac001,aae002
sum(nvl(aic011,0)) aic011
from tt
where aac001=c_aac001
and aae002 between c_saae001||'07' and c_eaae001||'06'
group by aac001,aae002
)
group by aac001,aab001,aic020
having k.aae002 between min(aae002) and max(aae002) ) group_count
from tt k
where aac001=c_aac001
and aae002 between c_saae001||'07' and c_eaae001||'06'
group by aac001,aae002,aab001
)
group by aac001,decode(substr(aae002,5,2),'01',substr(aae002,1,4)-1,'02',substr(aae002,1,4)-1,
'03',substr(aae002,1,4)-1,'04',substr(aae002,1,4)-1,
'05',substr(aae002,1,4)-1,'06',substr(aae002,1,4)-1,substr(aae002,1,4)),aic011,group_aae002,group_count
order by max(aae002)
;
-------------------------------以上是一同事写的sql语句 ,实现了按某年某月分区间的需求,但要传入参数,还有没有大虾的清简,现求高手简化。
from(
select tt.*,add_months(to_date(AAE002,'YYYYMM'),
-1*dense_rank()over(partition by aac001,aac003,trunc(add_months(to_date(AAE002,'YYYYMM'),-6),'yy') order by aae002))flag
from tt)
group by aac001,aac003,flag
order by 1,2,3
若8月到7月,则-6改成-(8-1)=-7