一张表里,不同条件,同时出现结果(在线等)
我有一张 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