每两月发生的领用材料统计

Question

有一张材料领用表tab_mat，里面包含字段id， name， useddate，现在要求统计某一段时期（比如一年以来）每2月（不是平均每2月，比如1-4月来，1月和2月任一月发生过领用并且3月和4月任一月发生过领用的材料需要统计出来，但若1、2月均未发生领用，但3月和4月同时发生过领用的则不符合条件）有发生领用的材料，请指点。

Answer 1

 
SQL> select * from tab_mat;
 
ID         NAME       USEDDATE
---------- ---------- -----------
2          物料2      2010-1-17 1
3          物料3      2010-1-17 1
4          物料4      2010-2-17 1
4          物料4      2010-2-17 1
4          物料4      2010-2-17 1
5          物料5      2010-6-17 1
1          物料1      2010-1-17 1
1          物料1      2010-2-17 1
 
8 rows selected
 
SQL> 
SQL> select (ceil(to_char(useddate,'mm')/2)*2-1)||'-'||ceil(to_char(useddate,'mm')/2)*2||'月',id
  2  from tab_mat where useddate between trunc(sysdate,'y') and trunc(sysdate,'mm')
  3  group by (ceil(to_char(useddate,'mm')/2)*2-1)||'-'||ceil(to_char(useddate,'mm')/2)*2||'月',id
  4  having abs(sum(case when to_char(useddate,'mm')in('01','03','05','07','09','11') then -1 else 1 end))-count(*)=0
  5  ;
 
(CEIL(TO_CHAR(USEDDATE,'MM')/2                                                   ID
-------------------------------------------------------------------------------- ----------
1-2月                                                                            2
1-2月                                                                            3
1-2月                                                                            4
5-6月                                                                            5
 
SQL> 

Answer 2

create table TAB_MAT
(
  ID       VARCHAR2(10),
  NAME     VARCHAR2(10),
  USEDDATE DATE
);
insert into TAB_MAT (ID, NAME, USEDDATE)
values ('2', '物料2', to_date('17-01-2010 10:10:45', 'dd-mm-yyyy hh24:mi:ss'));
insert into TAB_MAT (ID, NAME, USEDDATE)
values ('3', '物料3', to_date('17-01-2010 10:10:50', 'dd-mm-yyyy hh24:mi:ss'));
insert into TAB_MAT (ID, NAME, USEDDATE)
values ('4', '物料4', to_date('17-02-2010 10:10:54', 'dd-mm-yyyy hh24:mi:ss'));
insert into TAB_MAT (ID, NAME, USEDDATE)
values ('4', '物料4', to_date('17-02-2010 10:10:56', 'dd-mm-yyyy hh24:mi:ss'));
insert into TAB_MAT (ID, NAME, USEDDATE)
values ('4', '物料4', to_date('17-02-2010 10:10:59', 'dd-mm-yyyy hh24:mi:ss'));
insert into TAB_MAT (ID, NAME, USEDDATE)
values ('5', '物料5', to_date('17-06-2010 10:16:19', 'dd-mm-yyyy hh24:mi:ss'));
insert into TAB_MAT (ID, NAME, USEDDATE)
values ('1', '物料1', to_date('17-01-2010 10:10:37', 'dd-mm-yyyy hh24:mi:ss'));
insert into TAB_MAT (ID, NAME, USEDDATE)
values ('1', '物料1', to_date('17-02-2010 10:10:40', 'dd-mm-yyyy hh24:mi:ss'));
commit;

Answer 3

这样也可以
SQL> select (ceil(to_char(useddate,'mm')/2)*2-1)||'-'||ceil(to_char(useddate,'mm')/2)*2||'月',id
  2      from tab_mat where useddate between trunc(sysdate,'y') and trunc(sysdate,'mm')
  3      group by (ceil(to_char(useddate,'mm')/2)*2-1)||'-'||ceil(to_char(useddate,'mm')/2)*2||'月',id
  4      having count(distinct to_char(useddate,'mm'))=1;
 
(CEIL(TO_CHAR(USEDDATE,'MM')/2                                                   ID
-------------------------------------------------------------------------------- ----------
1-2月                                                                            2
1-2月                                                                            3
1-2月                                                                            4
5-6月                                                                            5
 
SQL> 

Answer 4

这种写法是可扩展的.把下面sql里的n换成对应的月份数就可以了,比如取3个月中只有一个月有领用的,就替换成3.
select (ceil(to_char(useddate,'mm')/n)*n-n+1)||'-'||ceil(to_char(useddate,'mm')/n)*n||'月',id
        from tab_mat where useddate between trunc(sysdate,'y') and trunc(sysdate,'mm')
        group by (ceil(to_char(useddate,'mm')/n)*n-n+1)||'-'||ceil(to_char(useddate,'mm')/n)*n||'月',id
        having count(distinct to_char(useddate,'mm'))=1;

Answer 5

a         b
        1 2010-07-17
1 2010-11-17
1 2010-09-17
1 2010-01-17
1 2010-05-17
1 2010-03-17
2 2010-07-17
2 2010-05-17
2 2010-01-17
2 2010-02-17
3 2010-07-17
3 2010-02-17
3 2010-03-17
4 2010-09-17with t as(
  select a,
         b,
         case
           when to_char(b, 'mm') in (01, 02) then
            'a'
           when to_char(b, 'mm') in (03, 04) then
            'b'
           when to_char(b, 'mm') in (05, 06) then
            'c'
           when to_char(b, 'mm') in (07, 08) then
            'd'
           when to_char(b, 'mm') in (09, 10) then
            'e'
           when to_char(b, 'mm') in (11, 12) then
            'f'
         end as flag
    from test)
  select a
    from (select a, flag, count(flag) as cou from t group by a, flag) t1
   group by a
  having count(a) = 6 and sum(cou) = 6

Answer 6

好像把图传到自己相册,然后在这里发url就可以了.

Answer 7

谢谢minitoy，这里“between trunc(sysdate,'y') and trunc(sysdate,'mm')”好像没太明白
同时谢谢kingkingzhu

Answer 8

哦，这里你可以替换成你自己的时间段。我是懒得写to_date，直接用sysdate取了今年的开始时间到当今的月份。

Answer 9

谢谢minitory，但这里还是有些问题要明确一下，数据如下ID         NAME       USEDDATE
---------- ---------- -----------
2          物料2      2010-1-17 1
2          物料2      2010-3-17 1
2          物料2      2010-5-17 1
2          物料2      2010-6-17 1
2          物料2      2010-7-17 1
2          物料2      2010-8-17 1
2          物料2      2010-10-17 1
2          物料2      2010-12-17 1
3          物料3      2010-1-17 1
4          物料4      2010-2-17 1
4          物料4      2010-2-17 1
4          物料4      2010-2-17 1
5          物料5      2010-6-17 1
1          物料1      2010-1-17 1
1          物料1      2010-2-17 1
2          物料2      2010-1-17 1
3          物料3      2010-1-17 1
4          物料4      2010-2-17 1
4          物料4      2010-2-17 1
4          物料4      2010-2-17 1
5          物料5      2010-6-17 1
1          物料1      2010-1-17 1
1          物料1      2010-2-17 1
如果从2010-01-01开始统计，这里只有id为2的为符合条件的记录，也就是说至少每两月要领用一次，再次谢谢minitoy在前面的辛勤付出

Answer 10

with t as (
select a.id, a.useddate, b.xx, b.zz
  from (select distinct * from test) a
  left join (select xx, 'a' || yy as zz--利用起始时间构造时间段
               from (select to_char(add_months(to_date('2010-01-01',
                                                       'yyyy-mm-dd'),
                                               level - 1),
                                    'yyyy-mm') || ',' ||
                            to_char(add_months(to_date('2010-01-01',
                                                       'yyyy-mm-dd'),
                                               level),
                                    'yyyy-mm') as xx,
                            level as yy
                       from dual
                     connect by level <
                                months_between(to_date('2010-12-01',--结束时间
                                                       'yyyy-mm-dd'),
                                               to_date('2010-01-01',--开始时间
                                                       'yyyy-mm-dd')) + 1)
              where mod(yy, 2) = 1) b on (to_char(a.useddate, 'yyyy-mm') =
                                         substr(b.xx, 1, 7) or
                                         to_char(a.useddate, 'yyyy-mm') =
                                         substr(b.xx, 9, 7)))
                           select id from t 
                           group by id
                           having count(distinct zz)=(months_between(to_date('2010-12-01',
                                                       'yyyy-mm-dd'),
                                               to_date('2010-01-01',
                                                       'yyyy-mm-dd')) + 1)/2

Answer 11

表名叫 test
还有 下次贴创建表和数据的 sql

Answer 12

怎么格式这个样子了   坐下实验  呵呵with t as (
select a.id, a.useddate, b.xx, b.zz
  from (select distinct * from test) a
  left join (select xx, 'a' || yy as zz--利用起始时间构造时间段
               from (select to_char(add_months(to_date('2010-01-01',
                                                       'yyyy-mm-dd'),
                                               level - 1),
                                    'yyyy-mm') || ',' ||
                            to_char(add_months(to_date('2010-01-01',
                                                       'yyyy-mm-dd'),
                                               level),
                                    'yyyy-mm') as xx,
                            level as yy
                       from dual
                     connect by level <
                                months_between(to_date('2010-12-01',--结束时间
                                                       'yyyy-mm-dd'),
                                               to_date('2010-01-01',--开始时间
                                                       'yyyy-mm-dd')) + 1)
              where mod(yy, 2) = 1) b on (to_char(a.useddate, 'yyyy-mm') =
                                         substr(b.xx, 1, 7) or
                                         to_char(a.useddate, 'yyyy-mm') =
                                         substr(b.xx, 9, 7)))
                           select id from t 
                           group by id
                           having count(distinct zz)=(months_between(to_date('2010-12-01',
                                                       'yyyy-mm-dd'),
                                               to_date('2010-01-01',
                                                       'yyyy-mm-dd')) + 1)/2

Answer 13

给你个本方法.
with t1 as (select (ceil(to_char(useddate,'mm')/2)*2-1)||'-'||ceil(to_char(useddate,'mm')/2)*2||'月' dateseg,id
        from tab_mat where useddate between to_date('201001','yyyymm') and to_date('201009','yyyymm')-1/24/60/60 --统计时间段2010年1月1日0时到2010年8月31日23时59分59秒
        group by (ceil(to_char(useddate,'mm')/2)*2-1)||'-'||ceil(to_char(useddate,'mm')/2)*2||'月',id
        having count(distinct to_char(useddate,'mm'))=1),
     t2 as(select id from t1 group by t1 having count(dateseg)=4)--因为是2010年1月1日0时到2010年8月31日23时59分59秒,为4个分段,所以取4
     select * from tab_mat a,t2 where a.id=t2.id and a.useddate  between to_date('201001','yyyymm') and to_date('201009','yyyymm')-1/24/60/60 

Answer 14

带with的sql好像在plsql developder里不能格式化  日
看的晕啊t2 as(select id from t1 group by t1 having count(dateseg)=4)
这里是
t2 as(select id from t1 group by id having count(dateseg)=4)吧

Answer 15

是啊,写错了.
with t1 as (select (ceil(to_char(useddate,'mm')/2)*2-1)||'-'||ceil(to_char(useddate,'mm')/2)*2||'月' dateseg,id
        from tab_mat where useddate between to_date('201001','yyyymm') and to_date('201009','yyyymm')-1/24/60/60 --统计时间段2010年1月1日0时到2010年8月31日23时59分59秒
        group by (ceil(to_char(useddate,'mm')/2)*2-1)||'-'||ceil(to_char(useddate,'mm')/2)*2||'月',id
        having count(distinct to_char(useddate,'mm'))=1),
     t2 as(select id from t1 group by id having count(dateseg)=4)--因为是2010年1月1日0时到2010年8月31日23时59分59秒,为4个分段,所以取4
     select * from tab_mat a,t2 where a.id=t2.id and a.useddate  between to_date('201001','yyyymm') and to_date('201009','yyyymm')-1/24/60/60 

Answer 16

再次谢过 minitoy和kingkingzhu