对测试环境做了一下改变,发现了问题:
--建立測試環境
Create Table 表1
(编号 Int,
名称 Char(1),
月份 Nvarchar(10),
销量 Int)Create Table 表2
(编号 Int,
名称 Char(1),
月份 Nvarchar(10),
指标 Int)
--插入數據
Insert 表1 Select 1, 'A', N'01月', 10
Union All Select 2, 'A', N'02月', 20
Union All Select 3, 'B', N'01月', 10
Union All Select 4, 'B', N'02月', 15
Union All Select 5, 'C', N'01月', 10
Union All Select 6, 'C', N'02月', 15
Union All Select 7, 'A', N'03月', 10
Union All Select 8, 'A', N'04月', 15
Insert 表2 Select 1, 'A', N'01月', 20
Union All Select 2, 'A', N'02月', 15
Union All Select 3, 'B', N'01月', 10
Union All Select 4, 'B', N'02月', 15
Union All Select 5, 'C', N'01月', 10
Union All Select 6, 'C', N'02月', 15
Union All Select 7, 'A', N'03月', 20
Union All Select 8, 'A', N'04月', 25
Union All Select 3, 'B', N'03月', 10
Union All Select 4, 'B', N'04月', 15
Union All Select 5, 'C', N'03月', 10
Union All Select 6, 'C', N'04月', 15GO
--測試
Declare @S1 Nvarchar(4000),@S2 Nvarchar(4000),@S3 Nvarchar(4000)
Select @S1='',@S2='',@S3=''
Select @S1=@S1+N',Rtrim(SUM(Case 月份 When N'''+月份+N''' Then 销量 Else 0 End)) As N'''+月份+'''',
@S2=@S2+N',Rtrim(SUM(Case 月份 When N'''+月份+N''' Then 指标 Else 0 End)) As N'''+月份+'''',
@S3=@S3+N',Rtrim(Cast(SUM(Case A.月份 When N'''+月份+N''' Then 销量 Else 0 End)*100.0/SUM(Case B.月份 When N'''+月份+N''' Then B.指标 Else 0 End) As Numeric(10,1)))+''%'' As N'''+月份+''''
From (Select 月份 From 表1 Union Select 月份 From 表2 ) A Order By 月份
EXEC(N'Select *From(Select 名称,N''销量'' As 月份'+@S1+N',Rtrim(SUM(销量)) As 合计 From 表1 Group By 名称 Union All Select 名称,N''指标'' As 月份'+@S2+N',Rtrim(SUM(指标)) As 合计 From 表2 Group By 名称 Union All Select A.名称,N''达成率'' As 月份'+@S3+N',Rtrim(Cast(SUM(A.销量)*100.0/SUM(B.指标) As Numeric(10,1)))+''%'' As 合计 From 表1 A Inner Join 表2 B On A.名称=B.名称 Group By A.名称) A Order By 名称,CharIndex(月份,N''销量指标达成率'')')
GO
--刪除測試環境
Drop Table 表1,表2
GO结果名称 月份 01月 02月 03月 04月 合计
A 销量 10 20 10 15 55
A 指标 20 15 20 25 80
A 达成率 50.0% 133.3% 50.0% 60.0% 68.8%
B 销量 10 15 0 0 25
B 指标 10 15 10 15 50
B 达成率 200.0% 200.0% 0.0% 0.0% 100.0%
C 销量 10 15 0 0 25
C 指标 10 15 10 15 50
C 达成率 200.0% 200.0% 0.0% 0.0% 100.0%达成率的计算出错了!!!另外:如何再将C在03、04月的销量和指标删除掉,也就是说C只有01、02月的数据时,会出现“除数为零”的错误,类似的问题应如何避免????
--建立測試環境
Create Table 表1
(编号 Int,
名称 Char(1),
月份 Nvarchar(10),
销量 Int)Create Table 表2
(编号 Int,
名称 Char(1),
月份 Nvarchar(10),
指标 Int)
--插入數據
Insert 表1 Select 1, 'A', N'01月', 10
Union All Select 2, 'A', N'02月', 20
Union All Select 3, 'B', N'01月', 10
Union All Select 4, 'B', N'02月', 15
Union All Select 5, 'C', N'01月', 10
Union All Select 6, 'C', N'02月', 15
Union All Select 7, 'A', N'03月', 10
Union All Select 8, 'A', N'04月', 15
Insert 表2 Select 1, 'A', N'01月', 20
Union All Select 2, 'A', N'02月', 15
Union All Select 3, 'B', N'01月', 10
Union All Select 4, 'B', N'02月', 15
Union All Select 5, 'C', N'01月', 10
Union All Select 6, 'C', N'02月', 15
Union All Select 7, 'A', N'03月', 20
Union All Select 8, 'A', N'04月', 25
Union All Select 3, 'B', N'03月', 10
Union All Select 4, 'B', N'04月', 15
Union All Select 5, 'C', N'03月', 10
Union All Select 6, 'C', N'04月', 15GO
--測試
Declare @S1 Nvarchar(4000),@S2 Nvarchar(4000),@S3 Nvarchar(4000)
Select @S1='',@S2='',@S3=''
Select @S1=@S1+N',Rtrim(SUM(Case 月份 When N'''+月份+N''' Then 销量 Else 0 End)) As N'''+月份+'''',
@S2=@S2+N',Rtrim(SUM(Case 月份 When N'''+月份+N''' Then 指标 Else 0 End)) As N'''+月份+'''',
@S3=@S3+N',Rtrim(Cast(SUM(Case A.月份 When N'''+月份+N''' Then 销量 Else 0 End)*100.0/SUM(Case B.月份 When N'''+月份+N''' Then B.指标 Else 0 End) As Numeric(10,1)))+''%'' As N'''+月份+''''
From (Select 月份 From 表1 Union Select 月份 From 表2 ) A Order By 月份
EXEC(N'Select *From(Select 名称,N''销量'' As 月份'+@S1+N',Rtrim(SUM(销量)) As 合计 From 表1 Group By 名称 Union All Select 名称,N''指标'' As 月份'+@S2+N',Rtrim(SUM(指标)) As 合计 From 表2 Group By 名称 Union All Select A.名称,N''达成率'' As 月份'+@S3+N',Rtrim(Cast(SUM(A.销量)*100.0/SUM(B.指标) As Numeric(10,1)))+''%'' As 合计 From 表1 A Inner Join 表2 B On A.名称=B.名称 Group By A.名称) A Order By 名称,CharIndex(月份,N''销量指标达成率'')')
GO
--刪除測試環境
Drop Table 表1,表2
GO结果名称 月份 01月 02月 03月 04月 合计
A 销量 10 20 10 15 55
A 指标 20 15 20 25 80
A 达成率 50.0% 133.3% 50.0% 60.0% 68.8%
B 销量 10 15 0 0 25
B 指标 10 15 10 15 50
B 达成率 200.0% 200.0% 0.0% 0.0% 100.0%
C 销量 10 15 0 0 25
C 指标 10 15 10 15 50
C 达成率 200.0% 200.0% 0.0% 0.0% 100.0%达成率的计算出错了!!!另外:如何再将C在03、04月的销量和指标删除掉,也就是说C只有01、02月的数据时,会出现“除数为零”的错误,类似的问题应如何避免????
--建立測試環境
Create Table 表1
(编号 Int,
名称 Char(1),
月份 Nvarchar(10),
销量 Int)Create Table 表2
(编号 Int,
名称 Char(1),
月份 Nvarchar(10),
指标 Int)
--插入數據
Insert 表1 Select 1, 'A', N'01月', 10
Union All Select 2, 'A', N'02月', 20
Union All Select 3, 'B', N'01月', 10
Union All Select 4, 'B', N'02月', 15
Union All Select 5, 'C', N'01月', 10
Union All Select 6, 'C', N'02月', 15
Union All Select 7, 'A', N'03月', 10
Union All Select 8, 'A', N'04月', 15Insert 表2 Select 1, 'A', N'01月', 20
Union All Select 2, 'A', N'02月', 15
Union All Select 3, 'B', N'01月', 10
Union All Select 4, 'B', N'02月', 15
Union All Select 5, 'C', N'01月', 10
Union All Select 6, 'C', N'02月', 15
Union All Select 7, 'A', N'03月', 20
Union All Select 8, 'A', N'04月', 25
Union All Select 3, 'B', N'03月', 10
Union All Select 4, 'B', N'04月', 15
Union All Select 5, 'C', N'03月', 10
Union All Select 6, 'C', N'04月', 15
GO
--測試
Declare @S1 Nvarchar(4000),@S2 Nvarchar(4000),@S3 Nvarchar(4000)
Select @S1='',@S2='',@S3=''
Select @S1=@S1+N',Rtrim(SUM(Case 月份 When N'''+月份+N''' Then 销量 Else 0 End)) As N'''+月份+'''',
@S2=@S2+N',Rtrim(SUM(Case 月份 When N'''+月份+N''' Then 指标 Else 0 End)) As N'''+月份+'''',
@S3=@S3+N',Rtrim(Cast(SUM(Case A.月份 When N'''+月份+N''' Then 销量 Else 0 End)*100.0/SUM(Case B.月份 When N'''+月份+N''' Then B.指标 Else 0 End) As Numeric(10,1)))+''%'' As N'''+月份+''''
From (Select 月份 From 表1 Union Select 月份 From 表2 ) A Order By 月份
EXEC(N'Select *From(Select 名称,N''销量'' As 月份'+@S1+N',Rtrim(SUM(销量)) As 合计 From 表1 Group By 名称 Union All Select 名称,N''指标'' As 月份'+@S2+N',Rtrim(SUM(指标)) As 合计 From 表2 Group By 名称 Union All Select B.名称,N''达成率'' As 月份'+@S3+N',Rtrim(Cast(SUM(A.销量)*100.0/SUM(B.指标) As Numeric(10,1)))+''%'' As 合计 From 表1 A Right Join 表2 B On A.名称=B.名称 And A.月份=B.月份 Group By B.名称) A Order By 名称,CharIndex(月份,N''销量指标达成率'')')
GO
--刪除測試環境
Drop Table 表1,表2
GO
--結果
/*
名称 月份 01月 02月 03月 04月 合计
A 销量 10 20 10 15 55
A 指标 20 15 20 25 80
A 达成率 50.0% 133.3% 50.0% 60.0% 68.8%
B 销量 10 15 0 0 25
B 指标 10 15 10 15 50
B 达成率 100.0% 100.0% 0.0% 0.0% 100.0%
C 销量 10 15 0 0 25
C 指标 10 15 10 15 50
C 达成率 100.0% 100.0% 0.0% 0.0% 100.0%
*/
譬如说,将上面C03\04月的指标数据删除?SQLSERVER会报下面错误,得出来的结果是空集.消息 8134,级别 16,状态 1,第 1 行
遇到以零作除数错误。
警告: 聚合或其他 SET 操作消除了空值。能不能让没有数据的月份的名称下面为空或用某符合代替?例如--测试环境
表1
名称 月份 销量
A 01 10
B 01 10
C 01 20
A 02 20
B 02 15表2
名称 月份 指标
A 01 15
B 01 20
C 01 15
A 02 15
B 02 15
--结果
名称 月份 01月 02月 合计
A 销量 10 20 30
A 指标 15 15 30
A 达成率 66.6% 133.3% 100.0%
B 销量 10 15 25
B 指标 20 15 30
B 达成率 50% 100.0% 83.3%
C 销量 20 - 20
C 指标 15 - 15
C 达成率 133.3% - 133.3%再烦请paoluo(一天到晚游泳的鱼)解答,谢谢!
Create Table 表1
(编号 Int,
名称 Char(1),
月份 Nvarchar(10),
销量 Int)Create Table 表2
(编号 Int,
名称 Char(1),
月份 Nvarchar(10),
指标 Int)
--插入數據
Insert 表1 Select 1, 'A', N'01月', 10
Union All Select 2, 'A', N'02月', 20
Union All Select 3, 'B', N'01月', 10
Union All Select 4, 'B', N'02月', 15
Union All Select 5, 'C', N'01月', 10
Union All Select 6, 'C', N'02月', 15
Union All Select 7, 'A', N'03月', 10
Union All Select 8, 'A', N'04月', 15Insert 表2 Select 1, 'A', N'01月', 20
Union All Select 2, 'A', N'02月', 15
Union All Select 3, 'B', N'01月', 10
Union All Select 4, 'B', N'02月', 15
Union All Select 5, 'C', N'01月', 10
Union All Select 6, 'C', N'02月', 15
Union All Select 7, 'A', N'03月', 20
Union All Select 8, 'A', N'04月', 25
Union All Select 3, 'B', N'03月', 10
Union All Select 4, 'B', N'04月', 15
GO
--測試
Declare @S1 Nvarchar(4000),@S2 Nvarchar(4000),@S3 Nvarchar(4000)
Select @S1='',@S2='',@S3=''
Select @S1=@S1+N',Rtrim(SUM(Case 月份 When N'''+月份+N''' Then 销量 Else 0 End)) As N'''+月份+'''',
@S2=@S2+N',Rtrim(SUM(Case 月份 When N'''+月份+N''' Then 指标 Else 0 End)) As N'''+月份+'''',
@S3=@S3+N',Rtrim(Cast(Case When SUM(Case B.月份 When N'''+月份+N''' Then B.指标 Else 0 End)=0 Then 0 Else SUM(Case A.月份 When N'''+月份+N''' Then 销量 Else 0 End)*100.0/SUM(Case B.月份 When N'''+月份+N''' Then B.指标 Else 0 End) End As Numeric(10,1)))+''%'' As N'''+月份+''''
From (Select 月份 From 表1 Union Select 月份 From 表2 ) A Order By 月份
EXEC(N'Select *From(Select 名称,N''销量'' As 月份'+@S1+N',Rtrim(SUM(销量)) As 合计 From 表1 Group By 名称 Union All Select 名称,N''指标'' As 月份'+@S2+N',Rtrim(SUM(指标)) As 合计 From 表2 Group By 名称 Union All Select B.名称,N''达成率'' As 月份'+@S3+N',Rtrim(Cast(SUM(A.销量)*100.0/SUM(B.指标) As Numeric(10,1)))+''%'' As 合计 From 表1 A Right Join 表2 B On A.名称=B.名称 And A.月份=B.月份 Group By B.名称) A Order By 名称,CharIndex(月份,N''销量指标达成率'')')
GO
--刪除測試環境
Drop Table 表1,表2
GO
--結果
/*
名称 月份 01月 02月 03月 04月 合计
A 销量 10 20 10 15 55
A 指标 20 15 20 25 80
A 达成率 50.0% 133.3% 50.0% 60.0% 68.8%
B 销量 10 15 0 0 25
B 指标 10 15 10 15 50
B 达成率 100.0% 100.0% 0.0% 0.0% 50.0%
C 销量 10 15 0 0 25
C 指标 10 15 0 0 25
C 达成率 100.0% 100.0% 0.0% 0.0% 100.0%
*/