有这样两段语句
select count(*) as [正常] from a where serv_state='F1A' and area_id=3
union all
select count(*) from a where serv_state='F1A' and area_id=4
union all
select count(*) from a where serv_state='F1A' and area_id=5执行这段语句的结果为:
正常
100
200
300--------------------------------------------------------------
select count(*) as [新装] from a where serv_state='F1N' and area_id=3
union all
select count(*) from a where serv_state='F1N' and area_id=4
union all
select count(*) from a where serv_state='F1N' and area_id=5
union all执行这段语句的结果为:
新装
50
60
100如果我把两段语句都给union 起来,最终只能显示一列结果
但,如果我想结果是这样显示:正常 新装
100 50
200 60
300 100该如何修改语句? 有没有比较简单的方法能实现,如能实现,40奉上。
select count(*) as [正常] from a where serv_state='F1A' and area_id=3
union all
select count(*) from a where serv_state='F1A' and area_id=4
union all
select count(*) from a where serv_state='F1A' and area_id=5执行这段语句的结果为:
正常
100
200
300--------------------------------------------------------------
select count(*) as [新装] from a where serv_state='F1N' and area_id=3
union all
select count(*) from a where serv_state='F1N' and area_id=4
union all
select count(*) from a where serv_state='F1N' and area_id=5
union all执行这段语句的结果为:
新装
50
60
100如果我把两段语句都给union 起来,最终只能显示一列结果
但,如果我想结果是这样显示:正常 新装
100 50
200 60
300 100该如何修改语句? 有没有比较简单的方法能实现,如能实现,40奉上。
select 3 aid, count(*) as [正常] from a where serv_state='F1A' and area_id=3
union all
select 4 aid, count(*) from a where serv_state='F1A' and area_id=4
union all
select 5 aid, count(*) from a where serv_state='F1A' and area_id=5
) a, (
select 3 aid, count(*) as [新装] from a where serv_state='F1N' and area_id=3
union all
select 4 aid, count(*) from a where serv_state='F1N' and area_id=4
union all
select 5 aid, count(*) from a where serv_state='F1N' and area_id=5
) b
on a.aid=b.aid
from
(
select 1 as id,count(*) as [正常] from a where serv_state='F1A' and area_id=3
union all
select 2 as id,count(*) from a where serv_state='F1A' and area_id=4
union all
select 3 as id, count(*) from a where serv_state='F1A' and area_id=5
) T1inner join (
select 1 as id,count(*) as [新装] from a where serv_state='F1N' and area_id=3
union all
select 2 as id,count(*) from a where serv_state='F1N' and area_id=4
union all
select 3 as id,count(*) from a where serv_state='F1N' and area_id=5
) T2
on T1.id=T2.id