SELECT '201509',
'01',
'010',
A.USER_ID,
B.DEVICE_NUMBER,
B.IMEI,
SUM(B.MON_VOICE_INTROAM_NUM),
SUM(B.MON_FLUX_INTROAM_NUM),
SUM(B.MON_VOICE_CDR_NUMS),
SUM(B.MON_FLUX_CDR_NUMS),
'1'
FROM
(SELECT USER_ID FROM DWA_S_D_PRD_MB_USE_GROUP_010
WHERE PART_ID = '1'
AND MONTH_ID = '201509'
AND DAY_ID = '01') A
JOIN
(SELECT USER_ID,
DEVICE_NUMBER,
IMEI,
SUM(MON_VOICE_INTROAM_NUM),
SUM(MON_FLUX_INTROAM_NUM),
SUM(MON_VOICE_CDR_NUMS),
SUM(MON_FLUX_CDR_NUMS)
FROM
(SELECT USER_ID,
DEVICE_NUMBER,
IMEI,
MON_INTROAM_NUM MON_VOICE_INTROAM_NUM,
NULL MON_FLUX_INTROAM_NUM,
MON_CDR_NUMS MON_VOICE_CDR_NUMS,
NULL MON_FLUX_CDR_NUMS
FROM DWA_V_D_CUS_2G_IMEI_VOICE_010
WHERE PART_ID = '1'
AND MONTH_ID = '201509'
AND DAY_ID = '01'
UNION ALL
SELECT USER_ID,
DEVICE_NUMBER,
IMEI,
NULL MON_VOICE_INTROAM_NUM,
MON_FLUX_INTROAM_NUM MON_FLUX_INTROAM_NUM,
NULL MON_VOICE_CDR_NUMS,
MON_CDR_NUMS MON_FLUX_CDR_NUMS
FROM DWA_V_D_CUS_2G_IMEI_FLUX_010
WHERE PART_ID = '1'
AND MONTH_ID = '201509'
AND DAY_ID = '01'
UNION ALL
SELECT USER_ID,
DEVICE_NUMBER,
IMEI,
MON_INTROAM_NUM MON_VOICE_INTROAM_NUM,
NULL MON_FLUX_INTROAM_NUM,
MON_CDR_NUMS MON_VOICE_CDR_NUMS,
NULL MON_FLUX_CDR_NUMS
FROM DWA_V_D_CUS_3G_IMEI_VOICE_010
WHERE PART_ID = '1'
AND MONTH_ID = '201509'
AND DAY_ID = '01'
UNION ALL
SELECT USER_ID,
DEVICE_NUMBER,
IMEI,
NULL MON_VOICE_INTROAM_NUM,
MON_FLUX_INTROAM_NUM MON_FLUX_INTROAM_NUM,
NULL MON_VOICE_CDR_NUMS,
MON_CDR_NUMS MON_FLUX_CDR_NUMS
FROM DWA_V_D_CUS_3G_IMEI_FLUX_010
WHERE PART_ID = '1'
AND MONTH_ID = '201509'
AND DAY_ID = '01') T
GROUP BY USER_ID,
DEVICE_NUMBER,
IMEI) B
ON A.USER_ID = B.USER_ID
GROUP BY A.USER_ID,
B.DEVICE_NUMBER,
B.IMEI
file:///C:\Users\crying\AppData\Roaming\Tencent\Users\850490579\QQ\WinTemp\RichOle\2EB`_R7DM(2Z43SB09FNPY1.png
![](http://www.itpub.net/static/image/common/none.gif)
2EB`_R7DM(2Z43SB09FNPY1.png (31.91 KB, 下载次数: 0)
下载附件
2015-10-22 11:30 上传
select count(1) FROM DWA_V_D_CUS_2G_IMEI_VOICE_010
WHERE PART_ID = '1'
AND MONTH_ID = '201509'
AND DAY_ID = '01'--200w
select count(1)FROM DWA_V_D_CUS_2G_IMEI_FLUX_010
WHERE PART_ID = '1'
AND MONTH_ID = '201509'
AND DAY_ID = '01' --523712
select count(1) FROM DWA_V_D_CUS_3G_IMEI_VOICE_010
WHERE PART_ID = '1'
AND MONTH_ID = '201509'
AND DAY_ID = '01'--8342524
select count(1) FROM DWA_V_D_CUS_3G_IMEI_FLUX_010
WHERE PART_ID = '1'
AND MONTH_ID = '201509'
AND DAY_ID = '01' --699803
file:///C:\Users\crying\AppData\Roaming\Tencent\Users\850490579\QQ\WinTemp\RichOle\2EB`_R7DM(2Z43SB09FNPY1.png
|