是否可以用一条SQL实现解决方法

是否可以用一条SQL实现
user表中有phone字段（是手机号）,现在需要统计移动、联通、电信的手机好个数

SQL code

// ============1===========移动   select count(*) from        (select substr(user.phone, 1, 3) as flag from user)t   where flag in ('134', '135', '136', '137', '138', '139', '150', '151', '152', '157', '158', '159', '187', '188')   

SQL code

 // ============2===========联通   select count(*) from        (select substr(user.phone, 1, 3) as flag from user)t   where flag in ('130', '131', '132', '155', '156', '185', '186')

SQL code

// ============3===========电信   select count(*) from        (select substr(user.phone, 1, 3) as flag from user)t   where flag in ('180', '189', '133', '153')

怎么能一条SQL查出这三个结果？

[解决办法]
select '移动',count(*) from 
(select substr(user.phone, 1, 3) as flag from user)t1
where flag in ('134', '135', '136', '137', '138', '139', '150', '151', '152', '157', '158', '159', '187', '188')
union all
select '联通',count(*) from 
(select substr(user.phone, 1, 3) as flag from user)t2
where flag in ('130', '131', '132', '155', '156', '185', '186')SQL code
select '电信',count(*) from 
(select substr(user.phone, 1, 3) as flag from user)t3
where flag in ('180', '189', '133', '153')
[解决办法]

SQL code

selectsum( case   when flag in ('134', '135', '136', '137', '138', '139', '150', '151', '152', '157', '158', '159', '187', '188')     then 1   else 0 end) as 移动,sum( case   when flag in('180', '189', '133', '153')    then 1   else 0  end) as 联通,sum( case   when flag in('130', '131', '132', '155', '156', '185', '186')    then 1   else 0  end) as 电信from (select substr(user.phone, 1, 3) as flag from user)t 

 

查看更多 下一篇