是否可以用一条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实现解决方法》,跪求各位点评，by 搞代码
- 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
select sum( 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
搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权
转载请注明原文链接：是否可以用一条SQL实现解决方法 
