select count(*) as 用户总数 from tb_user_dvbinfo
select count(*) as 塔城市 from tb_user_dvbinfo where areaid=00
select count(*) as 额敏县 from tb_user_dvbinfo where areaid=01
select count(*) as 托里县 from tb_user_dvbinfo where areaid=02
select count(*) as 裕民县 from tb_user_dvbinfo where areaid=03
select count(*) as 特殊用户 from tb_user_dvbinfo where areaid=04
这是提取出来的用户总数,我想把这些得到的数据插入一张新表中,该怎么写啊?求大家指教
select count(*) as 塔城市 from tb_user_dvbinfo where areaid=00
select count(*) as 额敏县 from tb_user_dvbinfo where areaid=01
select count(*) as 托里县 from tb_user_dvbinfo where areaid=02
select count(*) as 裕民县 from tb_user_dvbinfo where areaid=03
select count(*) as 特殊用户 from tb_user_dvbinfo where areaid=04
这是提取出来的用户总数,我想把这些得到的数据插入一张新表中,该怎么写啊?求大家指教
sum(case when areaid=00 then 1 else 0 end)塔城市,
sum(case when areaid=01 then 1 else 0 end)额敏县,
sum(case when areaid=02 then 1 else 0 end)托里县,
sum(case when areaid=03 then 1 else 0 end)裕民县,
sum(case when areaid=04 then 1 else 0 end)特殊用户
into 新表
from tb_user_dvbinfo
create table tb_user_dvbinfo(areaid varchar(20))
insert into tb_user_dvbinfo
select '00' union all
select '00' union all
select '01' union all
select '02' union all
select '03' union all
select '03' union all
select '03' union all
select '04' union all
select '04' union all
select '04' select
(select count(*) from tb_user_dvbinfo) as 用户总数,
(select count(*) from tb_user_dvbinfo where areaid='00') as 塔城市,
(select count(*) from tb_user_dvbinfo where areaid='01') as 额敏县,
(select count(*) from tb_user_dvbinfo where areaid='02') as 托里县,
(select count(*) from tb_user_dvbinfo where areaid='03') as 裕民县,
(select count(*) from tb_user_dvbinfo where areaid='04') as 特殊用户
into #tselect * from #t
/*
用户总数 塔城市 额敏县 托里县 裕民县 特殊用户
----------- ----------- ----------- ----------- ----------- -----------
10 2 1 1 3 3
*/
drop table #T
into 新表
select count(*)用户总数,
sum(case when areaid=00 then 1 else 0 end)塔城市,
sum(case when areaid=01 then 1 else 0 end)额敏县,
sum(case when areaid=02 then 1 else 0 end)托里县,
sum(case when areaid=03 then 1 else 0 end)裕民县,
sum(case when areaid=04 then 1 else 0 end)特殊用户
from tb_user_dvbinfo
如果areaid是varchar 的话 00和0 是不相等的
case areaid when '00' then '塔城市'
when '01' then '额敏县' when '02' then '托里县'
when '03' then '裕民县' when '04' then '特殊用户'
else isnull(areaid,'用户总数') end,
count(*)
from tb_user_dvbinfo group by areaid with rollup
/*
塔城市 2
额敏县 1
托里县 1
裕民县 3
特殊用户 3
用户总数 10
*/