之前问过一个问题,是有个统计表如下客户类别 客户数量
A 5
B 5
C 11
D 42
E 77
F 107
G 169
统计每个类别分别占总客户数量的占比,用下面语句才能得到结果
select 客户类别,客户数量,
convert(varchar,cast(客户数量*100.0/(select SUM(客户数量) from t) as decimal(5,2)))+'%' as 数量占比
from
t现在遇到的新问题是,表前面多了一列字段年份,比如下表
年份 客户类别 客户数量
2010 A 19
2010 B 17
2010 C 62
2010 D 156
2010 E 75
2009 A 18
2009 B 15
2009 C 56
2009 D 132
2009 E 79
2008 A 8
2008 B 7
2008 C 25
2008 D 101
2008 E 77
2007 A 11
2007 B 8
2007 C 31
2007 D 124
2007 E 92如果要统计每年的每个客户类别的客户数量占比,并把每年的客户总量一起体现在表上,不用建新表什么的,是否能用一个语句搞定捏????或者用最简单的方法如下表。。谢谢大家年份 客户类别 客户数量 年客户总量 占比
2010 A 19 329 5.78%
2010 B 17 329 5.17%
2010 C 62 329 18.84%
2010 D 156 329 47.42%
2010 E 75 329 22.80%
2009 A 18 300 6.00%
2009 B 15 300 5.00%
2009 C 56 300 18.67%
2009 D 132 300 44.00%
2009 E 79 300 26.33%
2008 A 8 218 3.67%
2008 B 7 218 3.21%
2008 C 25 218 11.47%
2008 D 101 218 46.33%
2008 E 77 218 35.32%
2007 A 11 266 4.14%
2007 B 8 266 3.01%
2007 C 31 266 11.65%
2007 D 124 266 46.62%
2007 E 92 266 34.59%
A 5
B 5
C 11
D 42
E 77
F 107
G 169
统计每个类别分别占总客户数量的占比,用下面语句才能得到结果
select 客户类别,客户数量,
convert(varchar,cast(客户数量*100.0/(select SUM(客户数量) from t) as decimal(5,2)))+'%' as 数量占比
from
t现在遇到的新问题是,表前面多了一列字段年份,比如下表
年份 客户类别 客户数量
2010 A 19
2010 B 17
2010 C 62
2010 D 156
2010 E 75
2009 A 18
2009 B 15
2009 C 56
2009 D 132
2009 E 79
2008 A 8
2008 B 7
2008 C 25
2008 D 101
2008 E 77
2007 A 11
2007 B 8
2007 C 31
2007 D 124
2007 E 92如果要统计每年的每个客户类别的客户数量占比,并把每年的客户总量一起体现在表上,不用建新表什么的,是否能用一个语句搞定捏????或者用最简单的方法如下表。。谢谢大家年份 客户类别 客户数量 年客户总量 占比
2010 A 19 329 5.78%
2010 B 17 329 5.17%
2010 C 62 329 18.84%
2010 D 156 329 47.42%
2010 E 75 329 22.80%
2009 A 18 300 6.00%
2009 B 15 300 5.00%
2009 C 56 300 18.67%
2009 D 132 300 44.00%
2009 E 79 300 26.33%
2008 A 8 218 3.67%
2008 B 7 218 3.21%
2008 C 25 218 11.47%
2008 D 101 218 46.33%
2008 E 77 218 35.32%
2007 A 11 266 4.14%
2007 B 8 266 3.01%
2007 C 31 266 11.65%
2007 D 124 266 46.62%
2007 E 92 266 34.59%
create table tb(data varchar(10),ar varchar(10),num int)
insert into tb
select '2010','A',19 union all
select '2010','B',17 union all
select '2010','C',62 union all
select '2009','A',132 union all
select '2009','B',149 union all
select '2009','C',9 union all
select '2008','A',15 union all
select '2008','B',8 union all
select '2008','C',19
goselect t.*,ltrim(cast((t.num*100./(select sum(num) from tb where data = t.data)) as decimal(5,2))) + '%' per
from tb tdrop table tbdata ar num per
---------- ---------- ----------- ------------------------------------------
2010 A 19 19.39%
2010 B 17 17.35%
2010 C 62 63.27%
2009 A 132 45.52%
2009 B 149 51.38%
2009 C 9 3.10%
2008 A 15 35.71%
2008 B 8 19.05%
2008 C 19 45.24%(9 行受影响)
select a.年份,a.客户类别,a.客户数量,
convert(varchar,cast(a.客户数量*100.0/(select SUM(客户数量) from tb where 年份=a.年份 group by 年份) as decimal(5,2)))+'%' as 数量占比
from tb a
select 年份,客户类别,客户数量,
(select SUM(客户数量) from t where 年份=a.年份) as 年客户总量,
convert(varchar,cast(客户数量*100.0/(select SUM(客户数量) from t where 年份=a.年份) as decimal(5,2)))+'%' as 数量占比
from t as a
--没看仔细!
create table tb(data varchar(10),ar varchar(10),num int)
insert into tb
select '2010','A',19 union all
select '2010','B',17 union all
select '2010','C',62 union all
select '2009','A',132 union all
select '2009','B',149 union all
select '2009','C',9 union all
select '2008','A',15 union all
select '2008','B',8 union all
select '2008','C',19
goselect U.*,ltrim(cast(num*100./total as decimal(5,2))) + '%' per
from(
select t.*,(select sum(num) from tb where data = t.data) total
from tb t)Udrop table tbdata ar num total per
---------- ---------- ----------- ----------- ------------------------------------------
2010 A 19 98 19.39%
2010 B 17 98 17.35%
2010 C 62 98 63.27%
2009 A 132 290 45.52%
2009 B 149 290 51.38%
2009 C 9 290 3.10%
2008 A 15 42 35.71%
2008 B 8 42 19.05%
2008 C 19 42 45.24%(9 行受影响)
if object_id('tempdb.dbo.#') is not null drop table #
create table #(y int, c varchar(8), n int)
insert into #
select 2010, 'A', 19 union all
select 2010, 'B', 17 union all
select 2010, 'C', 62 union all
select 2010, 'D', 156 union all
select 2010, 'E', 75 union all
select 2009, 'A', 18 union all
select 2009, 'B', 15 union all
select 2009, 'C', 56 union all
select 2009, 'D', 132 union all
select 2009, 'E', 79 union all
select 2008, 'A', 8 union all
select 2008, 'B', 7 union all
select 2008, 'C', 25 union all
select 2008, 'D', 101 union all
select 2008, 'E', 77 union all
select 2007, 'A', 11 union all
select 2007, 'B', 8 union all
select 2007, 'C', 31 union all
select 2007, 'D', 124 union all
select 2007, 'E', 92select *,
sum(n)over(partition by y) total,
ltrim(convert(numeric(5,2), convert(float,n*100)/sum(n)over(partition by y)))+'%' rate
from # order by y desc/*
y c n total rate
----------- -------- ----------- ----------- ------------------------------------------
2010 A 19 329 5.78%
2010 B 17 329 5.17%
2010 C 62 329 18.84%
2010 D 156 329 47.42%
2010 E 75 329 22.80%
2009 A 18 300 6.00%
2009 B 15 300 5.00%
2009 C 56 300 18.67%
2009 D 132 300 44.00%
2009 E 79 300 26.33%
2008 A 8 218 3.67%
2008 B 7 218 3.21%
2008 C 25 218 11.47%
2008 D 101 218 46.33%
2008 E 77 218 35.32%
2007 A 11 266 4.14%
2007 B 8 266 3.01%
2007 C 31 266 11.65%
2007 D 124 266 46.62%
2007 E 92 266 34.59%
*/