简单的应用问题!
有如下列：
班级代码(dm) 分数(score)
1 10
2 30
3 -20
4 -30
5 40
1 60
如何实现如下结果：
班级代码(dm) 加奖分数(score) 加奖合计 班级代码 扣奖分数 扣奖合计
1 10 70 3 -20 -20
1 60 4 -30 -30
2 30 30
5 40 40
也就是说将单独的那个分数列，用两列显示出来分别显示的> 0的一列和 <0的一列。完了算出来每个班级代码分数的合计也单独用一列显示出来。（如上图的结果）
[解决办法]
看看這個效果可以接受不？
--創建測試環境
Create Table TEST
(班级代码 Int,
分数 Int)
--插入數據
Insert TEST Select 1, 10
Union All Select 2, 30
Union All Select 3, -20
Union All Select 4, -30
Union All Select 5, 40
Union All Select 1, 60
GO
--測試
Select ID = Identity(Int, 1, 1), * Into #T From TEST Order By 班级代码, 分数
Select OrderID = (Select Count(ID) From #T Where ID <= A.ID And 分数 > 0), * Into #T1 From #T A Where 分数 > 0
Select OrderID = (Select Count(ID) From #T Where ID <= A.ID And 分数 < 0), * Into #T2 From #T A Where 分数 < 0
Select
A.班级代码 As 班级代码1,
A.分数 As 加奖分数,
(Select SUM(分数) From #T1 Where 班级代码 = A.班级代码) As 加奖合计,
B.班级代码 As 班级代码2,
B.分数 As 扣奖分数,
(Select SUM(分数) From #T2 Where 班级代码 = A.班级代码) As 扣奖合计
From
#T1 A
Full Join
#T2 B
On A.OrderID = B.OrderID
Order By
IsNull(A.OrderID, B.OrderID)
Drop Table #T, #T1, #T2
GO
--刪除測試環境
Drop Table TEST
--結果
/*
班级代码1加奖分数加奖合计班级代码2扣奖分数扣奖合计
110703-20NULL
160704-30NULL
23030NULLNULLNULL
54040NULLNULLNULL
*/
[解决办法]
create table tab(dm int,score int)
insert tab
select 1,10
union select 2,30
union select 3,-20
union select 4,-30
union select 5,40
union select 1,60
go
select ID=IDENTITY(INT,1,1),
班级代码=case when score> 0 then dm end,
加奖分数=case when score> 0 then score end,
加奖合计=(select c1=sum(case when score> 0 then score end) from tab where dm=a.dm)
INTO #
from tab a
where score> 0
order by dm,score
select ID=IDENTITY(INT,1,1),
班级代码=case when score <0 then dm end,
扣奖分数=case when score <0 then score end,
扣奖合计=(select c1=sum(case when score <0 then score end) from tab where dm=a.dm)
INTO #1
from tab a
where score <0
order by dm,score
go
SELECT * FROM # full JOIN #1 ON #.ID=#1.ID
drop table tab,#,#1
/* 结果
ID 班级代码 加奖分数 加奖合计 ID 班级代码 扣奖分数 扣奖合计
----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1 1 10 70 1 3 -20 -20
2 1 60 70 2 4 -30 -30
3 2 30 30 NULL NULL NULL NULL
4 5 40 40 NULL NULL NULL NULL
(4 row(s) affected)
*/
[解决办法]
create table t(dm int,score int)
insert t select 1,10
union select 2,30
union select 3,-20
union select 4,-30
union select 5,40
union select 1,60
--select * from t
select 班级代码=dm,
--下面一行不行,因为子查询可能返回多个值得,但是用case可以!!!!!!!
--加分奖励=(select score from t where dm=t1.dm and score> 0),
加分奖励=(case when dm=t1.dm and score> 0 then score else 0 end),
加分合计=isnull((select sum(score) from t where dm=t1.dm and score> 0),0),
扣分奖励=(case when dm=t1.dm and score <0 then score else 0 end),
扣分合计=isnull((select sum(score) from t where dm=t1.dm and score <0),0)
from t t1
drop table t
--结果
/*
班级代码 加分奖励 加分合计 扣分奖励 扣分合计
----------- ----------- ----------- ----------- -----------
1 10 70 0 0
1 60 70 0 0
2 30 30 0 0
3 0 0 -20 -20
4 0 0 -30 -30
5 40 40 0 0
*/
