求高手帮忙写一条SQL，想了2天都没实现想要的结果。。。

Question

要实现效果和上图主要是查询一张 主表  APP
主表有 BRANCH  PRODUCT  MARKETINGPLAN  STAGE  created等字段
STAGE 字段有大概一下状态： REGISTRATION,APPLICATION PROCESSING,CLOSE,等
主要是 当用户输入一个日期，例如 '2008-12-12'，然后就去 APP表做查询，
根据 BRANCH  PRODUCT  MARKETINGPLAN 分组，再根据STAGE 的不同，分别按 输入日期的 日，月，年分组，计算出 例如：STAGE为
REGISTRATION 的在  2008-12-12日有几条，2008-12 这个月有几条，2008年有几条，以此类推计算出其他状态记录。
分组效果如下：一个BRANCH 有多个 PRODUCT ，一个PRODUCT 有多个MARKETING PLAN ，MARETINGPLAN 里面 按日 月年分组 ，分别计算出 当日 对应STAGE有几条记录，月和年也是。
输入的日期是 跟CREATED 字段对比的
望高手指点下

Answer 1

楼主把图片上传到CSDN的相册，这样大家就能够看到了，而不必登录QQ。

Answer 2

SELECT BRANCH,PRODUCT,MARKETINGPLAN,STAGE,COUNT(create),COUNT(TRUNC(create,'MM')),COUNT(TRUNC(create,'YY'))
FROM APP
WHERE create='2008-12-12';试一试

Answer 3

不好意思，忘了写GROUP BY了。
SELECT BRANCH,PRODUCT,MARKETINGPLAN,STAGE,COUNT(create),COUNT(TRUNC(create,'MM')),COUNT(TRUNC(create,'YY'))
FROM APP
WHERE create='2008-12-12'
GROUP BY BRANCH,PRODUCT,MARKETINGPLAN,STAGE;试一试

Answer 4

分析了一下，感觉很抽象，LZ最好把你的原始数据和你要产生的数据贴出来。
如果你有兴趣自己钻研，那么给你贴个例子：06:34:23 SQL> select * from t;BILL_MONTH      AREA_CODE  NET_TYPE       LOCAL_FARE
--------------- ---------- ---------- --------------
200405          5761       G              7393344.04
200405          5761       J              5667089.85
200405          5762       G              6315075.96
200405          5762       J              6328716.15
200405          5763       G              8861742.59
200405          5763       J              7788036.32
200405          5764       G              6028670.45
200405          5764       J              6459121.49
200405          5765       G             13156065.77
200405          5765       J             11901671.70
200406          5761       G              7614587.96
200406          5761       J              5704343.05
200406          5762       G              6556992.60
200406          5762       J              6238068.05
200406          5763       G              9130055.46
200406          5763       J              7990460.25
200406          5764       G              6387706.01
200406          5764       J              6907481.66
200406          5765       G             13562968.81
200406          5765       J             12495492.50
200407          5761       G              7987050.65
200407          5761       J              5723215.28
200407          5762       G              6833096.68
200407          5762       J              6391201.44
200407          5763       G              9410815.91
200407          5763       J              8076677.41
200407          5764       G              6456433.23
200407          5764       J              6987660.53
200407          5765       G             14000101.20
200407          5765       J             12301780.20
200408          5761       G              8085170.84
200408          5761       J              6050611.37
200408          5762       G              6854584.22
200408          5762       J              6521884.50
200408          5763       G              9468707.65
200408          5763       J              8460049.43
200408          5764       G              6587559.23BILL_MONTH      AREA_CODE  NET_TYPE       LOCAL_FARE
--------------- ---------- ---------- --------------
200408          5764       J              7342135.86
200408          5765       G             14450586.63
200408          5765       J             12680052.3840 rows selected.Elapsed: 00:00:00.00

Answer 5

06:58:02 SQL> select area_code,bill_month,sum(local_fare) local_fare
06:58:30   2  from t
06:58:32   3  group by cube(area_code,bill_month)
06:58:42   4  order by area_code,bill_month nulls last
06:58:57   5  /AREA_CODE  BILL_MONTH          LOCAL_FARE
---------- --------------- --------------
5761       200405                13060.43
5761       200406                13318.93
5761       200407                13710.27
5761       200408                14135.78
5761                             54225.41
5762       200405                12643.79
5762       200406                12795.06
5762       200407                13224.30
5762       200408                13376.47
5762                             52039.62
5763       200405                16649.78
5763       200406                17120.52
5763       200407                17487.49
5763       200408                17928.76
5763                             69186.54
5764       200405                12487.79
5764       200406                13295.19
5764       200407                13444.09
5764       200408                13929.69
5764                             53156.77
5765       200405                25057.74
5765       200406                26058.46
5765       200407                26301.88
5765       200408                27130.64
5765                            104548.72
           200405                79899.53
           200406                82588.15
           200407                84168.03
           200408                86501.34
                                333157.05

Answer 6

06:53:00 SQL> select area_code,bill_month,sum(local_fare) local_fare
06:53:37   2  from t
06:53:38   3  group by rollup(area_code,bill_month)
06:53:49   4  /AREA_CODE  BILL_MONTH          LOCAL_FARE
---------- --------------- --------------
5761       200405             13060433.89
5761       200406             13318931.01
5761       200407             13710265.93
5761       200408             14135782.21
5761                          54225413.04
5762       200405             12643792.11
5762       200406             12795060.65
5762       200407             13224298.12
5762       200408             13376468.72
5762                          52039619.60
5763       200405             16649778.91
5763       200406             17120515.71
5763       200407             17487493.32
5763       200408             17928757.08
5763                          69186545.02
5764       200405             12487791.94
5764       200406             13295187.67
5764       200407             13444093.76
5764       200408             13929695.09
5764                          53156768.46
5765       200405             25057737.47
5765       200406             26058461.31
5765       200407             26301881.40
5765       200408             27130639.01
5765                         104548719.19
                             333157065.3126 rows selected.Elapsed: 00:00:00.00

Answer 7

一段代码，仅供参考 ，可以直接执行，看看效果是不是你要的 select object_type,
       count(*)"年",
       sum(case when trunc(created,'MM') = TRUNC(TO_DATE('2008-12-12','YYYY-MM-DD'),'MM') then 1 else 0 end)"月",
       sum(case when created = TO_DATE('2008-12-12','YYYY-MM-DD') then 1 else 0 end)"日"
  from user_objects 
 where trunc(created,'YY') = TRUNC(TO_DATE('2008-12-12','YYYY-MM-DD'),'YY')
group by object_type
-- 你要是查年度的 ，所以你不能使用使用 = ‘2008-12-12’ 做条件，要变通

Answer 8

补充一下图片
表结构如下
CREATE TABLE APPLICATION (
APPNO VARCHAR2(20) NOT NULL,
DIVISION_CODE VARCHAR2(15) NOT NULL,
PRODUCT_CODE VARCHAR2(20),
MARKETING_PLAN_CODE VARCHAR2(20),
CLASS_TYPE VARCHAR2(25),
SPECIFIC_APPROVAL_FLOW VARCHAR2(3),
DEALER_CODE VARCHAR2(4),
DEALER_BRANCH_CODE VARCHAR2(7),
CREDIT_OFFICER_ID VARCHAR2(32),
FOLLOWUP_BRANCH_CODE VARCHAR2(15),
REC_DATE TIMESTAMP,
CLOSED_DATE TIMESTAMP,
FINAL_APPROVED_USER VARCHAR2(32),
FINAL_APPROVED_DATE TIMESTAMP,
ALERTINDICATOR VARCHAR2(255),
DRAWDOWN_DATE TIMESTAMP,
PRODUCT_NAME VARCHAR2(255),
MARKETING_PLAN_NAME VARCHAR2(255),
MARKETING_PLAN_DESC VARCHAR2(500),
SOURCE_OF_MEDIA VARCHAR2(15),
REFERRAL_BRANCH_COMPANY VARCHAR2(15),
REFERRAL_AGENCY_CODE VARCHAR2(15),
CANCEL_DATE TIMESTAMP(6),
CANCEL_REASON_CODE VARCHAR2(15),
REJECT_REASON_CODE VARCHAR2(15),
REJECT_DATE TIMESTAMP(6),
BASE_CURRENCY_CODE VARCHAR2(15),
STAGE_CODE VARCHAR2(15),
APPLICATION_RESULT VARCHAR2(20),
CREATED_BY VARCHAR2(32),
CREATED TIMESTAMP(6),
LAST_MODIFIED_BY VARCHAR2(32),
LAST_MODIFIED TIMESTAMP(6),
REVISION NUMBER(9 , 0) NOT NULL,
DRAWDOWN_NO VARCHAR2(10),
DRAWDOWN_ACC_NO VARCHAR2(12),
DRAWDOWN_ACC_OPEN_DATE TIMESTAMP(6),
DRAWDOWN_ACC_STATUS CHAR(1),
DRAWDOWN_BORR_CIF VARCHAR2(9)
)