简单的应用问题!

Question

有如下列： 班级代码(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的一列。完了算出来每个班级代码分数的合计也单独用一列显示出来。（如上图的结果）

Answer 1

看看這個效果可以接受不？
--創建測試環境
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 分数 < 0Select 
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 扣奖分数 扣奖合计
1 10 70 3 -20 NULL
1 60 70 4 -30 NULL
2 30 30 NULL NULL NULL
5 40 40 NULL NULL NULL
*/

Answer 2

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,60goselect  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,scoregoSELECT * FROM # full JOIN #1 ON #.ID=#1.IDdrop 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)*/

Answer 3

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,60goselect  ID=IDENTITY(INT,1,1),
班级代码=dm,
加奖分数=score,
加奖合计=(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),
班级代码=dm,
扣奖分数=score,
扣奖合计=(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,scoregoSELECT a.班级代码,a.加奖分数,a.加奖合计,b.班级代码,b.扣奖分数,b.扣奖合计 
FROM # a full JOIN #1 b ON a.ID=b.IDdrop 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)*/

Answer 4

各位高手:用过以上的查询语句后实现的效果如下:
班级代码  加奖   加奖合计   班级代码   扣奖   扣奖合计
 1         10      20         1         -10     -10
 1         10                 2         -20     -20
根本没有一一对应!应该显示的是:
班级代码  加奖   加奖合计   班级代码   扣奖   扣奖合计
 1         10      20         1         -10     -10
 1         10                null      null    null
 2         -20     -20        2         -20     -20

Answer 5

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
*/

Answer 6

忘了删除注释了~~~..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 班级代码=dm,
       加分奖励=(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
*/

Answer 7

luluxiaoniu() ( ) 信誉：100  2007-8-22 11:23:18  得分: 0  
 
 
   
各位高手:用过以上的查询语句后实现的效果如下:
班级代码  加奖   加奖合计   班级代码   扣奖   扣奖合计
 1         10      20         1         -10     -10
 1         10                 2         -20     -20
根本没有一一对应!应该显示的是:
班级代码  加奖   加奖合计   班级代码   扣奖   扣奖合计
 1         10      20         1         -10     -10
 1         10                null      null    null
 2         -20     -20        2         -20     -20-----------------------
你的需求到底是怎樣的？怎麼和上面完全不一樣？