现有一表有数据如下:RECEIVESMS_ID NOTES_ID MOBILE IS_NEW
83 bailaxiusx 13734039384 0
97 bailaxiusx 13734039384 0
101 bailaxiusx 13734039384 0
102 bailaxiusx 13734039384 1
84 SXADM 13734039384 0
98 bailaxiusx 13934116969 0
99 bailaxiusx 13934116969 1 提示:我用sqlSELECT NOTES_ID,MOBILE,COUNT(*) AS total FROM tb WHERE NOTES_ID ='bailaxiusx' GROUP BY NOTES_ID,MOBILE
仅得出这样的数据..
NOTES_ID MOBILE total
bailaxiusx 13734039384 4
bailaxiusx 13934116969 2
------------------希望输出以下结果------- NOTES_ID MOBILE total IS_NEW_0 IS_NEW_1
bailaxiusx 13734039384 4 3 1
bailaxiusx 13934116969 2 1 1谢谢各位仁兄指点...sql怎么写?
83 bailaxiusx 13734039384 0
97 bailaxiusx 13734039384 0
101 bailaxiusx 13734039384 0
102 bailaxiusx 13734039384 1
84 SXADM 13734039384 0
98 bailaxiusx 13934116969 0
99 bailaxiusx 13934116969 1 提示:我用sqlSELECT NOTES_ID,MOBILE,COUNT(*) AS total FROM tb WHERE NOTES_ID ='bailaxiusx' GROUP BY NOTES_ID,MOBILE
仅得出这样的数据..
NOTES_ID MOBILE total
bailaxiusx 13734039384 4
bailaxiusx 13934116969 2
------------------希望输出以下结果------- NOTES_ID MOBILE total IS_NEW_0 IS_NEW_1
bailaxiusx 13734039384 4 3 1
bailaxiusx 13934116969 2 1 1谢谢各位仁兄指点...sql怎么写?
SUM(CASE WHEN IS_NEW=0 THEN 1 ELSE 0 END) AS IS_NEW_0,
SUM(CASE WHEN IS_NEW=1 THEN 1 ELSE 0 END) AS IS_NEW_1
FROM tb
WHERE NOTES_ID ='bailaxiusx'
GROUP BY NOTES_ID,MOBILE
conversion from datatype 'VARCHAR' to 'INT' is not allowed. Use the CONVERT function to run this query.