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'什么作用呢?
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分组后的排序不同
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; 这是全部代码
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;
---这么长 看了下 改了下 试试
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
就像你可以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 都查出来 就必须根据这三个字段分组