user表中有phone字段(是手机号),现在需要统计移动、联通、电信的手机好个数// ============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')
// ============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')// ============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)t
where flag in ('134', '135', '136', '137', '138', '139', '150', '151', '152', '157', '158', '159', '187', '188')
// ============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')// ============3===========电信
select count(*) from
(select substr(user.phone, 1, 3) as flag from user)t
where flag in ('180', '189', '133', '153')怎么能一条SQL查出这三个结果?
(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')
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