北京 轻卡 个人 非运营
北京 重卡 个人 运营
上海 重卡 个人 非运营
北京 轻卡 团体 运营
上海 轻卡 团体 非运营
北京 重卡 团体 运营 要这样的结果
--机构名称(省),货车各类 个人 团体 运营 非运营
--上海 轻卡 30% 70% 70% 30% 数据如下:
create table #tb
(province varchar(20),
cartype varchar(20),
owner varchar(20),
services varchar(20) )
insert into #tb
select '北京','轻卡','个人','非运营' union all
select '北京','重卡','个人','运营' union all
select '上海','重卡','个人','非运营' union all
select '北京','轻卡','团体','运营' union all
select '上海','轻卡','团体','非运营' union all
select '北京','重卡','团体','运营'
create table #tb
(province varchar(20),
cartype varchar(20),
owner varchar(20),
services varchar(20) )
insert into #tb
select '北京','轻卡','个人','非运营' union all
select '北京','重卡','个人','运营' union all
select '上海','重卡','个人','非运营' union all
select '北京','轻卡','团体','运营' union all
select '上海','轻卡','团体','非运营' union all
select '北京','重卡','团体','运营'
SELECT distinct province,cartype,
个人=
SUM(CASE owner WHEN '个人' THEN 1.00 ELSE 0.00 END )/(SELECT COUNT(*) FROM #tb) ,
团体=
SUM(CASE owner WHEN '团体' THEN 1.00 ELSE 0.00 END )/(SELECT COUNT(*) FROM #tb) ,
运营=
SUM(CASE services WHEN '运营' THEN 1.00 ELSE 0.00 END )/(SELECT COUNT(*) FROM #tb) ,
非运营=
SUM(CASE services WHEN '非运营' THEN 1.00 ELSE 0.00 END )/(SELECT COUNT(*) FROM #tb)
FROM #tb
GROUP BY
province,cartypeDROP TABLE #tb
select province,cartype,
(case when owner = '个人' then (cast (convert(decimal(10,2),((select count(1) from #tb where owner ='个人') *100.00 / (select count(*) from #tb))) as varchar(5))+ '%') else null end) as '个人',
(case when owner = '团体' then (cast (convert(decimal(10,2),((select count(1) from #tb where owner ='团体') *100.00 / (select count(*) from #tb))) as varchar(5))+ '%') else null end) as '团体',
(case when services = '运营' then (cast (convert(decimal(10,2),((select count(1) from #tb where services ='运营') *100.00 / (select count(*) from #tb))) as varchar(5))+ '%') else null end) as '营运',
(case when services = '非运营' then(cast (convert(decimal(10,2),((select count(1) from #tb where services ='非运营') *100.00 / (select count(*) from #tb))) as varchar(5))+ '%') else null end) as '非营运'
from dbo.#tb
group by province,cartype,owner,services order by province北京 轻卡 50.00% NULL NULL 50.00%
北京 轻卡 NULL 50.00% 50.00% NULL
北京 重卡 50.00% NULL 50.00% NULL
北京 重卡 NULL 50.00% 50.00% NULL
上海 轻卡 NULL 50.00% NULL 50.00%
上海 重卡 50.00% NULL NULL 50.00%不明白的是
北京 重卡 50.00% NULL 50.00% NULL
北京 重卡 NULL 50.00% 50.00% NULL
怎么有重复的
create table #tb
(province varchar(20),
cartype varchar(20),
owner varchar(20),
services varchar(20) )
insert into #tb
select '北京','轻卡','个人','非运营' union all
select '北京','重卡','个人','运营' union all
select '上海','重卡','个人','非运营' union all
select '北京','轻卡','团体','运营' union all
select '上海','轻卡','团体','非运营' union all
select '北京','重卡','团体','运营' SELECT
distinct province,
cartype,
个人=SUM(CASE owner WHEN '个人' THEN 1.00 ELSE 0.00 END )/(SELECT COUNT(*) FROM #tb WHERE owner='个人') ,
团体=SUM(CASE owner WHEN '团体' THEN 1.00 ELSE 0.00 END )/(SELECT COUNT(*) FROM #tb WHERE owner='团体') ,
运营=SUM(CASE services WHEN '运营' THEN 1.00 ELSE 0.00 END )/(SELECT COUNT(*) FROM #tb WHERE services='运营') ,
非运营=SUM(CASE services WHEN '非运营' THEN 1.00 ELSE 0.00 END )/(SELECT COUNT(*) FROM #tb WHERE services='非运营')
FROM #tb
GROUP BY province,cartypeDROP TABLE #tb 修改了一下,刚才忘加条件了。
个人=(select count(1) from #tb where owner='个人' and a.province=province and cartype= a.cartype)*1.
/(select count(1) from #tb where owner='个人'),
团体=(select count(1) from #tb where owner='团体' and a.province=province and cartype= a.cartype)*1.
/(select count(1) from #tb where owner='团体'),
运营=(select count(1) from #tb where services='运营' and a.province=province and cartype= a.cartype)*1.
/(select count(1) from #tb where services='运营'),
非运营=(select count(1) from #tb where services='非运营' and a.province=province and cartype= a.cartype)*1.
/(select count(1) from #tb where services='非运营')
from #tb a
create table #tb
(province varchar(20),
cartype varchar(20),
owner1 varchar(20),
services1 varchar(20) )
insert into #tb
select '北京','轻卡','个人','非运营' union all
select '北京','重卡','个人','运营' union all
select '上海','重卡','个人','非运营' union all
select '北京','轻卡','团体','运营' union all
select '上海','轻卡','团体','非运营' union all
select '北京','重卡','团体','运营' SELECT
province,
cartype,
个人=SUM(CASE owner1 WHEN '个人' THEN 1.00 ELSE 0.00 END )/count(*),
团体=SUM(CASE owner1 WHEN '团体' THEN 1.00 ELSE 0.00 END )/count(*),
运营=SUM(CASE services1 WHEN '运营' THEN 1.00 ELSE 0.00 END )/count(*) ,
非运营=SUM(CASE services1 WHEN '非运营' THEN 1.00 ELSE 0.00 END )/count(*)
FROM #tb
GROUP BY province,cartypeDROP TABLE #tb