是否可以用一条SQL实现解决方法
日期：2014-05-16 浏览次数：20806 次
是否可以用一条SQL实现
user表中有phone字段（是手机号）,现在需要统计移动、联通、电信的手机好个数
怎么能一条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')
------解决方案--------------------
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
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
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
