我有一张 SERV 表,里面有area_code的字段,area_code有3个值,分别为0790,0791,0792
如果我单独求area_code=0790 时的记录条数
就应该是
select count(*) from serv where area_code=0790
但,我有什么办法,能运行一次,就能同时得到
area_code=0790 的记录条数
area_code=0791 的记录条数
area_code=0792 的记录条数
跪求................
------解决方案--------------------
如果只需要输出一条记录的话
select sum(case when area_code= '0790 ' then 1 else 0 end) as [0790],
sum(case when area_code= '0791 ' then 1 else 0 end) as [0791],
sum(case when area_code= '0792 ' then 1 else 0 end) as [0792]
from serv
------解决方案--------------------
select sum(case when area_code= '0790 ' then 1 else 0 end) as [0790],
sum(case when area_code= '0791 ' then 1 else 0 end) as [0791],
sum(case when area_code= '0792 ' then 1 else 0 end) as [0792]
from serv a where exists(select 1 from dj where area_code=a.area_code and serv_state= 'F1R ')
------解决方案--------------------
--我写了个实例,你看一下吧.
--表名 SERV 字段 area_code (0790,0791,0792)
--表名 dj 字段 serv_state (F1R 和F1A)
create table SERV
(
area_code char(4)
)
select * from SERV
insert into serv
select '0790 ' union all
select '0791 ' union all
select '0792 '
create table dj
(
area_code char(4),
serv_state char(3)
)
insert into dj (area_code,serv_state)
select '0790 ', 'F1R ' union all
select '0790 ', 'F1A ' union all
select '0791 ', 'F1A ' union all
select '0792 ', 'F1A ' union all
select '0793 ', 'F1A ' union all
select '0791 ', 'F1R ' union all
select '0792 ', 'F1R ' union all
select '0793 ', 'F1R ' union all
select '0790 ', 'F1R '
select *
from dj
-------------
--生成临时表
select distinct area_code into # from dj where serv_state= 'F1A '
select * from #
--最终结果
select sum(case when a.area_code= '0790 ' then 1 else 0 end) as [0790],
sum(case when a.area_code= '0791 ' then 1 else 0 end) as [0791],
sum(case when a.area_code= '0792 ' then 1 else 0 end) as [0792]
from serv a left join # b on (a.area_code=b.area_code)
------解决方案--------------------
Select * from (
select area_code,count(area_code) as number from group by area_code) T
where t.rea_code in ( '0790 ', '0791 ', '0792 ')
------解决方案--------------------
SELECT area_code,COUNT(area_code) FROM SERV GROUP By area_code