日期:2014-05-17 浏览次数:20604 次
SELECT userid,SUM(CASE WHEN REPORT_STAT='DELIVRD' THEN 1 ELSE 0 END) [成功条数],SUM(CASE WHEN REPORT_STAT<>'DELIVRD' THEN 1 ELSE 0 END)[失败条数] ,
SUM(CASE WHEN REPORT_STAT IS NULL THEN 1 ELSE 0 END)[未回短信条数],
SUM(CASE WHEN MOBILE_NO_TYPE =1 AND REPORT_STAT='DELIVRD' THEN 1 ELSE 0 END )[成功条数中的移动号码数],
SUM(CASE WHEN MOBILE_NO_TYPE =2 AND REPORT_STAT='DELIVRD' THEN 1 ELSE 0 END )[成功条数中的联通号码数],
SUM(CASE WHEN MOBILE_NO_TYPE =3 AND REPORT_STAT='DELIVRD' THEN 1 ELSE 0 END )[成功条数中的电信号码数]
FROM [SMS_MT]
GROUP BY userid
/*
userid 成功条数 失败条数 未回短信条数 成功条数中的移动号码数 成功条数中的联通号码数 成功条数中的电信号码数
-------------------- ----------- ----------- ----------- ----------- ----------- -----------
abc 4 0 0 4 0 0
admin 2 2 1 2 0 0
*/