1.表格如下:
等级 城市 分店名 数量
一级 广州 A1 100
二级 广州 A2 150
二级 广州 A3 150
一级 深圳 B1 200
三级 深圳 B2 200
三级 深圳 B3 200
三级 珠海 C1 150
二级 珠海 C2 150
一级 珠海 C3 150 想要得到如下结果:
等级 城市 数量 百分比
一级 广州 100 25.00%
深圳 200 33.33%
珠海 150 33.33%
合计 450 31.03%
二级 广州 300 75.00%
珠海 150 33.33%
合计 450 31.03%
三级 深圳 400 66.67%
珠海 150 33.33%
合计 550 37.93%答案
--示例--示例数据
create table tb(等级 varchar(10),城市 varchar(10),分店名 varchar(10),数量 int)
insert tb select '一级','广州','A1',100
union all select '二级','广州','A2',150
union all select '二级','广州','A3',150
union all select '一级','深圳','B1',200
union all select '三级','深圳','B2',200
union all select '三级','深圳','B3',200
union all select '三级','珠海','C1',150
union all select '二级','珠海','C2',150
union all select '一级','珠海','C3',150
等级 城市 分店名 数量
一级 广州 A1 100
二级 广州 A2 150
二级 广州 A3 150
一级 深圳 B1 200
三级 深圳 B2 200
三级 深圳 B3 200
三级 珠海 C1 150
二级 珠海 C2 150
一级 珠海 C3 150 想要得到如下结果:
等级 城市 数量 百分比
一级 广州 100 25.00%
深圳 200 33.33%
珠海 150 33.33%
合计 450 31.03%
二级 广州 300 75.00%
珠海 150 33.33%
合计 450 31.03%
三级 深圳 400 66.67%
珠海 150 33.33%
合计 550 37.93%答案
--示例--示例数据
create table tb(等级 varchar(10),城市 varchar(10),分店名 varchar(10),数量 int)
insert tb select '一级','广州','A1',100
union all select '二级','广州','A2',150
union all select '二级','广州','A3',150
union all select '一级','深圳','B1',200
union all select '三级','深圳','B2',200
union all select '三级','深圳','B3',200
union all select '三级','珠海','C1',150
union all select '二级','珠海','C2',150
union all select '一级','珠海','C3',150
GO
create table tb(等级 varchar(10),城市 varchar(10),分店名 varchar(10),数量 int)
insert tb select '一级','广州','A1',100
union all select '二级','广州','A2',150
union all select '二级','广州','A3',150
union all select '一级','深圳','B1',200
union all select '三级','深圳','B2',200
union all select '三级','深圳','B3',200
union all select '三级','珠海','C1',150
union all select '二级','珠海','C2',150
union all select '一级','珠海','C3',150
SELECT
[等级],[城市]
,CAST(CAST(数量*100/SUM(数量) OVER(PARTITION BY [城市]) AS NUMERIC(19,2)) AS VARCHAR(21))+'%'
FROM TB
ORDER BY CASE [等级] WHEN '一级' THEN 1 WHEN '二级' THEN 2 ELSE 3 END
,[城市]
/*
一级 广州 25.00%
一级 深圳 33.00%
一级 珠海 33.00%
二级 广州 37.00%
二级 广州 37.00%
二级 珠海 33.00%
三级 深圳 33.00%
三级 深圳 33.00%
三级 珠海 33.00%
*/
create table tb(等级 varchar(10),城市 varchar(10),分店名 varchar(10),数量 int)
insert tb select '一级','广州','A1',100
union all select '二级','广州','A2',150
union all select '二级','广州','A3',150
union all select '一级','深圳','B1',200
union all select '三级','深圳','B2',200
union all select '三级','深圳','B3',200
union all select '三级','珠海','C1',150
union all select '二级','珠海','C2',150
union all select '一级','珠海','C3',150
select isnull(等级,'合计')等级, isnull(城市,'合计') 城市, sum(数量) 数量,
cast(cast(sum(数量)*100.0/(select sum(数量) from tb) as decimal(8,2)) as varchar(10))+'%'百分比
from tb
group by 等级,城市
with rollup
order by charindex(left(等级,1),'一,二,三')
/*
等级 城市 数量 百分比
---------- ---------- ----------- -----------
合计 合计 1450 100.00%
一级 广州 100 6.90%
一级 深圳 200 13.79%
一级 珠海 150 10.34%
一级 合计 450 31.03%
二级 广州 300 20.69%
二级 珠海 150 10.34%
二级 合计 450 31.03%
三级 深圳 400 27.59%
三级 珠海 150 10.34%
三级 合计 550 37.93%(11 行受影响)
*/
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
create table tb(等级 varchar(10),城市 varchar(10),分店名 varchar(10),数量 int)
insert tb select '一级','广州','A1',100
union all select '二级','广州','A2',150
union all select '二级','广州','A3',150
union all select '一级','深圳','B1',200
union all select '三级','深圳','B2',200
union all select '三级','深圳','B3',200
union all select '三级','珠海','C1',150
union all select '二级','珠海','C2',150
union all select '一级','珠海','C3',150
SELECT * FROM (
SELECT DISTINCT
[等级],[城市]
,CAST(CAST(数量*100/SUM(数量) OVER(PARTITION BY [城市]) AS NUMERIC(19,2)) AS VARCHAR(21))+'%' '比例'
FROM TB
) T
ORDER BY CASE [等级] WHEN '一级' THEN 1 WHEN '二级' THEN 2 ELSE 3 END
,[城市]
/*
一级 广州 25.00%
一级 深圳 33.00%
一级 珠海 33.00%
二级 广州 37.00%
二级 珠海 33.00%
三级 深圳 33.00%
三级 珠海 33.00%
*/
create table tb(等级 varchar(10),城市 varchar(10),分店名 varchar(10),数量 int)
insert tb select '一级','广州','A1',100
union all select '二级','广州','A2',150
union all select '二级','广州','A3',150
union all select '一级','深圳','B1',200
union all select '三级','深圳','B2',200
union all select '三级','深圳','B3',200
union all select '三级','珠海','C1',150
union all select '二级','珠海','C2',150
union all select '一级','珠海','C3',150
select isnull(等级,'合计')等级, isnull(城市,'合计') 城市, sum(数量) 数量,
cast(cast(sum(数量)*100.0/
(select sum(数量) from tb where 城市=a.城市) as decimal(8,2)) as varchar(10))+'%'百分比
from tb a
group by 等级,城市
with rollup
order by charindex(left(等级,1),'一,二,三')
/*
等级 城市 数量 百分比
---------- ---------- ----------- -----------
合计 合计 1450 NULL
一级 广州 100 25.00%
一级 深圳 200 33.33%
一级 珠海 150 33.33%
一级 合计 450 NULL
二级 广州 300 75.00%
二级 珠海 150 33.33%
二级 合计 450 NULL
三级 深圳 400 66.67%
三级 珠海 150 33.33%
三级 合计 550 NULL
(11 行受影响)
*/貌似还有差距,楼主是05的吗?
select A.*, 百分比 = convert(varchar, convert(decimal(18, 2), A.数量 * 100.0 /B.总数量)) + '%'
from (
select 等级, 城市= isnull(城市, '合计'), 数量= sum(数量)
from tb
group by 等级, 城市 with rollup
having grouping(等级) = 0) a
join
(
select 城市= isnull(城市, '合计'), sum(数量) as 总数量
from tb
group by 城市 with rollup) b
on a.城市 = B.城市
order by case when 等级 = '一级' then 1
when 等级 = '二级' then 2
when 等级 = '三级' then 3
end
GO
create table tb(等级 varchar(10),城市 varchar(10),分店名 varchar(10),数量 int)
insert tb select '一级','广州','A1',100
union all select '二级','广州','A2',150
union all select '二级','广州','A3',150
union all select '一级','深圳','B1',200
union all select '三级','深圳','B2',200
union all select '三级','深圳','B3',200
union all select '三级','珠海','C1',150
union all select '二级','珠海','C2',150
union all select '一级','珠海','C3',150SELECT 等级=CASE WHEN 城市=(SELECT TOP 1 城市 FROM TB WHERE 等级=T.等级 ORDER BY CHARINDEX(LEFT(T.等级,1),'一二三')) OR 等级='合计' THEN 等级 ELSE '' END,
城市,数量,百分比
FROM(
SELECT 等级,城市,SUM(数量) 数量,px1=CHARINDEX(left(t.等级,1),'一二三'),px2=0,
LTRIM(CAST(CAST(SUM(数量)*100./
(SELECT SUM(数量) FROM tb WHERE 城市=t.城市) AS DEC(9,2)) AS FLOAT))+'%' 百分比
FROM TB t
GROUP BY 等级,城市
UNION ALL
SELECT '合计','',SUM(数量),CHARINDEX(left(t.等级,1),'一二三'),1,
LTRIM(CAST(CAST(SUM(数量)*100./
(SELECT SUM(数量) FROM TB) AS DEC(9,2)) AS FLOAT))+'%' 百分比
FROM tb t
GROUP BY 等级
)T
ORDER BY px1,px2,
CASE CASE WHEN 城市=(SELECT TOP 1 城市 FROM TB WHERE 等级=T.等级 ORDER BY CHARINDEX(LEFT(T.等级,1),'一二三'))
THEN 等级 ELSE '' END
WHEN '' THEN 1 ELSE 0 END
/*
等级 城市 数量 百分比
---------- ---------- ----------- ------------------------
一级 广州 100 25%
深圳 200 33.33%
珠海 150 33.33%
合计 450 31.03%
二级 广州 300 75%
珠海 150 33.33%
合计 450 31.03%
三级 深圳 400 66.67%
珠海 150 33.33%
合计 550 37.93%(10 行受影响)
*/