GROUP BY A.USER_NO,
                  (CASE
                    WHEN FEE_CLASS_FIRST_LVL IN ('2110') AND
                         NVL(IN_CODE, '9999') <> '10158' THEN
                     '1110' 
                    WHEN FEE_CLASS_FIRST_LVL IN ('2111') AND
                         NVL(IN_CODE, '9999') <> '10158' THEN
                     '1120' 
                    WHEN FEE_CLASS_FIRST_LVL IN ('2112') AND
                         NVL(IN_CODE, '9999') <> '10158' THEN
                     '1130' 
                    WHEN FEE_CLASS_FIRST_LVL IN ('2113', '2114') AND
                         NVL(IN_CODE, '9999') <> '10158' THEN
                     '1140' 
                    WHEN FEE_CLASS_FIRST_LVL IN ('2120', '2121', '2122') AND
                         NVL(IN_CODE, '9999') <> '10158' THEN
                     '1210' 
                    WHEN FEE_CLASS_FIRST_LVL IN ('2123', '2124', '2125') AND
                         NVL(IN_CODE, '9999') <> '10158' THEN
                     '1221'
                    WHEN FEE_CLASS_FIRST_LVL IN ('2126') AND
                         NVL(IN_CODE, '9999') <> '10158' THEN
                     '1222' 
                    WHEN FEE_CLASS_FIRST_LVL IN ('2129') AND
                         NVL(IN_CODE, '9999') <> '10158' THEN
                     '1223' 
                    WHEN FEE_CLASS_FIRST_LVL IN ('2127') AND
                         NVL(IN_CODE, '9999') <> '10158' THEN
                     '1224' 
                    WHEN FEE_CLASS_FIRST_LVL IN ('2128') AND
                         NVL(IN_CODE, '9999') <> '10158' THEN
                     '1225' 
                    WHEN NVL(IN_CODE, '9999') = '10158' THEN
                     '2300' 
                    ELSE
                     '1300'
                  END),
                  DECODE(SERVICE_KIND, '921', '01', '02'),
                  (CASE
                    WHEN C.FEE_CLASS_FIRST_LVL IN ('2120', '2121', '2122') THEN
                     '01' 
                    WHEN C.FEE_CLASS_FIRST_LVL IN
                         ('2123', '2124', '2125', '2126', '2127', '2128',
                          '2129') THEN
                     '02' 
                    ELSE
                     '03'
                  END);
      COMMIT;
这种 group by 字段1, ('1100');
是什么意思,
我光知道以字段分组,后面'1100'什么作用呢?

解决方案 »

  1.   

    影响到结果的排序select cgicode from askquotelist group by cgicode,1100,12001 90606CP34A
    2 90606CP35A
    3 96301JM71A
    4 80838JN00B
    5 96368CP20A
    6 96369CP20A
    7 82830CJ000
    select cgicode from askquotelist group by cgicode,1200,1100
    1 85074JN00A
    2 85075JN00A
    3 90450EL00A
    4 82385CJ00A
    5 82386CJ000
    6 84908AZ66A
    7 90125JG000分组后的排序不同
      

  2.   


    INSERT /*+ APPEND */
          INTO MID_DW_F_DEV_INCR_DAY1 LOGGING
            SELECT /*+ORDERED*/
             V_ACCT_MONTH,
             V_DAY,
             C1.AREA_NO,
             A.USER_NO,
             '',
             (CASE
               WHEN FEE_CLASS_FIRST_LVL IN ('2110') AND
                    NVL(IN_CODE, '9999') <> '10158' THEN
                '1110' 
               WHEN FEE_CLASS_FIRST_LVL IN ('2111') AND
                    NVL(IN_CODE, '9999') <> '10158' THEN
                '1120' 
               WHEN FEE_CLASS_FIRST_LVL IN ('2112') AND
                    NVL(IN_CODE, '9999') <> '10158' THEN
                '1130' 
               WHEN FEE_CLASS_FIRST_LVL IN ('2113', '2114') AND
                    NVL(IN_CODE, '9999') <> '10158' THEN
                '1140' 
               WHEN FEE_CLASS_FIRST_LVL IN ('2120', '2121', '2122') AND
                    NVL(IN_CODE, '9999') <> '10158' THEN
                '1210' 
               WHEN FEE_CLASS_FIRST_LVL IN ('2123', '2124', '2125') AND
                    NVL(IN_CODE, '9999') <> '10158' THEN
                '1221' 
               WHEN FEE_CLASS_FIRST_LVL IN ('2126') AND
                    NVL(IN_CODE, '9999') <> '10158' THEN
                '1222' 
               WHEN FEE_CLASS_FIRST_LVL IN ('2129') AND
                    NVL(IN_CODE, '9999') <> '10158' THEN
                '1223'
               WHEN FEE_CLASS_FIRST_LVL IN ('2127') AND
                    NVL(IN_CODE, '9999') <> '10158' THEN
                '1224' 
               WHEN FEE_CLASS_FIRST_LVL IN ('2128') AND
                    NVL(IN_CODE, '9999') <> '10158' THEN
                '1225' 
               WHEN NVL(IN_CODE, '9999') = '10158' THEN
                '2300' 
               ELSE
                '1300'
             END),
             DECODE(SERVICE_KIND, '921', '01', '02'),
             (CASE
               WHEN C.FEE_CLASS_FIRST_LVL IN ('2120', '2121', '2122') THEN
                '01' 
               WHEN C.FEE_CLASS_FIRST_LVL IN
                    ('2123', '2124', '2125', '2126', '2127', '2128', '2129') THEN
                '02' 
               ELSE
                '03'
             END),
             '1',
             '',
             '',
             '',
             '',
             COUNT(1),
             0,
             0,
             SUM(BASE_FEE),
             SUM(INFO_FEE),
             SUM(BASE_FEE + INFO_FEE + MONTH_FEE + OTHER_FEE),
             SUM(MESSAGE_NUM * P1),
             0,
             0,
             0,
             SYSDATE
              FROM (SELECT CHARGE_CODE, A.FEE_CLASS_FIRST_LVL, P1, P2, P3, P4
                      FROM ODSCODE.DIM_CHARGE_CODE         A,
                           ODSCODE.DIM_FEE_CLASS_FIRST_LVL B
                     WHERE A.FEE_CLASS_FIRST_LVL = B.FEE_CLASS_FIRST_LVL
                       AND TELE_TYPE = '1'
                       AND B.IF_VALID = '1'
                       AND B.FEE_CLASS_SECOND_LVL = '210'
                       AND A.FEE_CLASS_FIRST_LVL NOT IN ('2410', '2411', '2412')) C,
                   (SELECT *
                      FROM ODS.ODS_B_CDR_GSM_SMS
                     WHERE ACCT_MONTH = V_ACCT_MONTH
                       AND DAY_ID = V_DAY
                       AND AREA_NO = C1.AREA_NO) A
             WHERE A.FEE_KIND = C.CHARGE_CODE(+)
             GROUP BY A.USER_NO,
                      (CASE
                        WHEN FEE_CLASS_FIRST_LVL IN ('2110') AND
                             NVL(IN_CODE, '9999') <> '10158' THEN
                         '1110' 
                        WHEN FEE_CLASS_FIRST_LVL IN ('2111') AND
                             NVL(IN_CODE, '9999') <> '10158' THEN
                         '1120'
                        WHEN FEE_CLASS_FIRST_LVL IN ('2112') AND
                             NVL(IN_CODE, '9999') <> '10158' THEN
                         '1130' 
                        WHEN FEE_CLASS_FIRST_LVL IN ('2113', '2114') AND
                             NVL(IN_CODE, '9999') <> '10158' THEN
                         '1140' 
                        WHEN FEE_CLASS_FIRST_LVL IN ('2120', '2121', '2122') AND
                             NVL(IN_CODE, '9999') <> '10158' THEN
                         '1210'
                        WHEN FEE_CLASS_FIRST_LVL IN ('2123', '2124', '2125') AND
                             NVL(IN_CODE, '9999') <> '10158' THEN
                         '1221' 
                        WHEN FEE_CLASS_FIRST_LVL IN ('2126') AND
                             NVL(IN_CODE, '9999') <> '10158' THEN
                         '1222' 
                        WHEN FEE_CLASS_FIRST_LVL IN ('2129') AND
                             NVL(IN_CODE, '9999') <> '10158' THEN
                         '1223' 
                        WHEN FEE_CLASS_FIRST_LVL IN ('2127') AND
                             NVL(IN_CODE, '9999') <> '10158' THEN
                         '1224' 
                        WHEN FEE_CLASS_FIRST_LVL IN ('2128') AND
                             NVL(IN_CODE, '9999') <> '10158' THEN
                         '1225' 
                        WHEN NVL(IN_CODE, '9999') = '10158' THEN
                         '2300' 
                        ELSE
                         '1300'
                      END),
                      DECODE(SERVICE_KIND, '921', '01', '02'),
                      (CASE
                        WHEN C.FEE_CLASS_FIRST_LVL IN ('2120', '2121', '2122') THEN
                         '01' 
                        WHEN C.FEE_CLASS_FIRST_LVL IN
                             ('2123', '2124', '2125', '2126', '2127', '2128',
                              '2129') THEN
                         '02' 
                        ELSE
                         '03'
                      END);
          COMMIT; 这是全部代码
      

  3.   

    楼主,你的group by '1100' 是因为你的select中有这个值,要按照这个分组,这个没什么问题啊
      

  4.   

    INSERT /*+ APPEND */
          INTO MID_DW_F_DEV_INCR_DAY1 LOGGING
            SELECT /*+ORDERED*/
             V_ACCT_MONTH,
             V_DAY,
             C1.AREA_NO,
             A.USER_NO,
             '',
             (CASE
               WHEN FEE_CLASS_FIRST_LVL IN ('2110') AND
                    NVL(IN_CODE, '9999') <> '10158' THEN
                '1110' 
               WHEN FEE_CLASS_FIRST_LVL IN ('2111') AND
                    NVL(IN_CODE, '9999') <> '10158' THEN
                '1120' 
               WHEN FEE_CLASS_FIRST_LVL IN ('2112') AND
                    NVL(IN_CODE, '9999') <> '10158' THEN
                '1130' 
               WHEN FEE_CLASS_FIRST_LVL IN ('2113', '2114') AND
                    NVL(IN_CODE, '9999') <> '10158' THEN
                '1140' 
               WHEN FEE_CLASS_FIRST_LVL IN ('2120', '2121', '2122') AND
                    NVL(IN_CODE, '9999') <> '10158' THEN
                '1210' 
               WHEN FEE_CLASS_FIRST_LVL IN ('2123', '2124', '2125') AND
                    NVL(IN_CODE, '9999') <> '10158' THEN
                '1221' 
               WHEN FEE_CLASS_FIRST_LVL IN ('2126') AND
                    NVL(IN_CODE, '9999') <> '10158' THEN
                '1222' 
               WHEN FEE_CLASS_FIRST_LVL IN ('2129') AND
                    NVL(IN_CODE, '9999') <> '10158' THEN
                '1223'
               WHEN FEE_CLASS_FIRST_LVL IN ('2127') AND
                    NVL(IN_CODE, '9999') <> '10158' THEN
                '1224' 
               WHEN FEE_CLASS_FIRST_LVL IN ('2128') AND
                    NVL(IN_CODE, '9999') <> '10158' THEN
                '1225' 
               WHEN NVL(IN_CODE, '9999') = '10158' THEN
                '2300' 
               ELSE
                '1300'
             END),
             DECODE(SERVICE_KIND, '921', '01', '02'),
             (CASE
               WHEN C.FEE_CLASS_FIRST_LVL IN ('2120', '2121', '2122') THEN
                '01' 
               WHEN C.FEE_CLASS_FIRST_LVL IN
                    ('2123', '2124', '2125', '2126', '2127', '2128', '2129') THEN
                '02' 
               ELSE
                '03'
             END),
             '1',
             '',
             '',
             '',
             '',
             COUNT(1),
             0,
             0,
             SUM(BASE_FEE),
             SUM(INFO_FEE),
             SUM(BASE_FEE + INFO_FEE + MONTH_FEE + OTHER_FEE),
             SUM(MESSAGE_NUM * P1),
             0,
             0,
             0,
             SYSDATE
              FROM (SELECT CHARGE_CODE, A.FEE_CLASS_FIRST_LVL, P1, P2, P3, P4
                      FROM ODSCODE.DIM_CHARGE_CODE         A,
                           ODSCODE.DIM_FEE_CLASS_FIRST_LVL B
                     WHERE A.FEE_CLASS_FIRST_LVL = B.FEE_CLASS_FIRST_LVL
                       AND TELE_TYPE = '1'
                       AND B.IF_VALID = '1'
                       AND B.FEE_CLASS_SECOND_LVL = '210'
                       AND A.FEE_CLASS_FIRST_LVL NOT IN ('2410', '2411', '2412')) C,
                   (SELECT *
                      FROM ODS.ODS_B_CDR_GSM_SMS
                     WHERE ACCT_MONTH = V_ACCT_MONTH
                       AND DAY_ID = V_DAY
                       AND AREA_NO = C1.AREA_NO) A
             WHERE A.FEE_KIND = C.CHARGE_CODE(+)
             GROUP BY A.USER_NO,
                      (CASE
                        WHEN FEE_CLASS_FIRST_LVL IN ('2110') AND
                             NVL(IN_CODE, '9999') <> '10158' THEN
                         '1110' 
                        WHEN FEE_CLASS_FIRST_LVL IN ('2111') AND
                             NVL(IN_CODE, '9999') <> '10158' THEN
                         '1120'
                        WHEN FEE_CLASS_FIRST_LVL IN ('2112') AND
                             NVL(IN_CODE, '9999') <> '10158' THEN
                         '1130' 
                        WHEN FEE_CLASS_FIRST_LVL IN ('2113', '2114') AND
                             NVL(IN_CODE, '9999') <> '10158' THEN
                         '1140' 
                        WHEN FEE_CLASS_FIRST_LVL IN ('2120', '2121', '2122') AND
                             NVL(IN_CODE, '9999') <> '10158' THEN
                         '1210'
                        WHEN FEE_CLASS_FIRST_LVL IN ('2123', '2124', '2125') AND
                             NVL(IN_CODE, '9999') <> '10158' THEN
                         '1221' 
                        WHEN FEE_CLASS_FIRST_LVL IN ('2126') AND
                             NVL(IN_CODE, '9999') <> '10158' THEN
                         '1222' 
                        WHEN FEE_CLASS_FIRST_LVL IN ('2129') AND
                             NVL(IN_CODE, '9999') <> '10158' THEN
                         '1223' 
                        WHEN FEE_CLASS_FIRST_LVL IN ('2127') AND
                             NVL(IN_CODE, '9999') <> '10158' THEN
                         '1224' 
                        WHEN FEE_CLASS_FIRST_LVL IN ('2128') AND
                             NVL(IN_CODE, '9999') <> '10158' THEN
                         '1225' 
                        WHEN NVL(IN_CODE, '9999') = '10158' THEN
                         '2300' 
                        ELSE
                         '1300'
                      END),
                      DECODE(SERVICE_KIND, '921', '01', '02'),
                      (CASE
                        WHEN C.FEE_CLASS_FIRST_LVL IN ('2120', '2121', '2122') THEN
                         '01' 
                        WHEN C.FEE_CLASS_FIRST_LVL IN
                             ('2123', '2124', '2125', '2126', '2127', '2128',
                              '2129') THEN
                         '02' 
                        ELSE
                         '03'
                      END);
          COMMIT;
      

  5.   


    ---这么长 看了下  改了下 试试
    SELECT /*+ORDERED*/
             V_ACCT_MONTH,
             V_DAY,
             C1.AREA_NO,
             A.USER_NO,
             '',
             (CASE
               WHEN FEE_CLASS_FIRST_LVL IN ('2110') AND
                    NVL(IN_CODE, '9999') <> '10158' THEN
                '1110' 
               WHEN FEE_CLASS_FIRST_LVL IN ('2111') AND
                    NVL(IN_CODE, '9999') <> '10158' THEN
                '1120' 
               WHEN FEE_CLASS_FIRST_LVL IN ('2112') AND
                    NVL(IN_CODE, '9999') <> '10158' THEN
                '1130' 
               WHEN FEE_CLASS_FIRST_LVL IN ('2113', '2114') AND
                    NVL(IN_CODE, '9999') <> '10158' THEN
                '1140' 
               WHEN FEE_CLASS_FIRST_LVL IN ('2120', '2121', '2122') AND
                    NVL(IN_CODE, '9999') <> '10158' THEN
                '1210' 
               WHEN FEE_CLASS_FIRST_LVL IN ('2123', '2124', '2125') AND
                    NVL(IN_CODE, '9999') <> '10158' THEN
                '1221' 
               WHEN FEE_CLASS_FIRST_LVL IN ('2126') AND
                    NVL(IN_CODE, '9999') <> '10158' THEN
                '1222' 
               WHEN FEE_CLASS_FIRST_LVL IN ('2129') AND
                    NVL(IN_CODE, '9999') <> '10158' THEN
                '1223'
               WHEN FEE_CLASS_FIRST_LVL IN ('2127') AND
                    NVL(IN_CODE, '9999') <> '10158' THEN
                '1224' 
               WHEN FEE_CLASS_FIRST_LVL IN ('2128') AND
                    NVL(IN_CODE, '9999') <> '10158' THEN
                '1225' 
               WHEN NVL(IN_CODE, '9999') = '10158' THEN
                '2300' 
               ELSE
                '1300'
             END) str1,
             DECODE(SERVICE_KIND, '921', '01', '02') str1,
             (CASE
               WHEN C.FEE_CLASS_FIRST_LVL IN ('2120', '2121', '2122') THEN
                '01' 
               WHEN C.FEE_CLASS_FIRST_LVL IN
                    ('2123', '2124', '2125', '2126', '2127', '2128', '2129') THEN
                '02' 
               ELSE
                '03'
             END) str3,
             '1',
             '',
             '',
             '',
             '',
             COUNT(1),
             0,
             0,
             SUM(BASE_FEE),
             SUM(INFO_FEE),
             SUM(BASE_FEE + INFO_FEE + MONTH_FEE + OTHER_FEE),
             SUM(MESSAGE_NUM * P1),
             0,
             0,
             0,
             SYSDATE
              FROM (SELECT CHARGE_CODE, A.FEE_CLASS_FIRST_LVL, P1, P2, P3, P4
                      FROM ODSCODE.DIM_CHARGE_CODE         A,
                           ODSCODE.DIM_FEE_CLASS_FIRST_LVL B
                     WHERE A.FEE_CLASS_FIRST_LVL = B.FEE_CLASS_FIRST_LVL
                       AND TELE_TYPE = '1'
                       AND B.IF_VALID = '1'
                       AND B.FEE_CLASS_SECOND_LVL = '210'
                       AND A.FEE_CLASS_FIRST_LVL NOT IN ('2410', '2411', '2412')) C,
                   (SELECT *
                      FROM ODS.ODS_B_CDR_GSM_SMS
                     WHERE ACCT_MONTH = V_ACCT_MONTH
                       AND DAY_ID = V_DAY
                       AND AREA_NO = C1.AREA_NO) A
             WHERE A.FEE_KIND = C.CHARGE_CODE(+)
             GROUP BY          C1.AREA_NO,A.USER_NO,str1,str2,str3
      

  6.   

    可以根据字段分组 也可以根据常量分组 没啥好奇怪的吧
    就像你可以select col from table 也可以 select '1' from table一样
    比如表里的数据是
    A 10
    A 20
    B 10
    B 20
    只根据字段A group by ,结果是两行 根据 A 10 group by 是四行
    还有,有group by 的时候,查询字段必须在group by中 否则不让查
    你想把字段A B C 都查出来 就必须根据这三个字段分组