积分榜统计问题！

[chd2001]

比赛表：match
ID 比赛日期 轮次 赛事类型 主队 客队 主队得分 客队得分
1 2008-4-5 30 英超 AAA BBB 3 1
2 2008-4-5 30 英超 ccc DDD 0 0
3 2008-4-5 30 英超 EEE FFF 1 2
4 2008-4-12 31 英超 BBB ccc 1 1
5 2008-4-12 31 英超 EEE AAA 2 0
6 2008-4-12 31 英超 FFF DDD 1 2

如上所示，表中记录了英超球队的所有比赛，要将根据信息统计出积分榜，也就是大家常见的积分榜

名次 球队 比赛场次 胜 平 负 进球 失球 净胜球 积分

应该怎么做？？

[zanyzyg]

你这里面哪里有进球数啊？

[dawugui]

2 2008-4-5 30 英超 ccc DDD 0 0
这行有问题.不可能主客对都不得分.

另:同分情况下怎么办?

[chd2001]

主队得分 客队得分 就是分别的进球数
不好意思 我没表述清楚

[烈火焚身]

表的结构不完整????

[chd2001]

排名依据：
1、积分
2、净胜球
3、进球数

以上都相同则并列

[dawugui]

用程序都很难搞这个问题,用SQL就更麻烦了.帮顶.

[chd2001]

老龟 你都说难搞，我郁闷啊！

还有，关于比赛表，我要怎样修改表结构，会更有利于复杂的统计?

[子陌红尘]

呵呵，按照足球比赛的规则：

select
    t.球队,
    count(*) as 比赛场次,
    sum(t.胜) as 胜,
    sum(t.平) as 平,
    sum(t.负) as 负,
    sum(t.进球) as 进球,
    sum(t.失球) as 失球,
    sum(t.进球-t.失球) as 净胜球,
    sum(t.胜*3+t.平) as 积分
from
    (select 
         主队 as 球队,  
         (case when 主队得分>客队得分 then 1 else 0 end) as 胜,
         (case when 主队得分=客队得分 then 1 else 0 end) as 平,
         (case when 主队得分<客队得分 then 1 else 0 end) as 负,
         主队得分 as 进球,
         客队得分 as 失球
     from 
         match
     union all
     select 
         客队 as 球队,  
         (case when 客队得分>主队得分 then 1 else 0 end) as 胜,
         (case when 客队得分=主队得分 then 1 else 0 end) as 平,
         (case when 客队得分<主队得分 then 1 else 0 end) as 负,
         客队得分 as 进球,
         主队得分 as 失球
     from 
         match) t
group by
    t.球队
order by
    积分 desc

[dawugui]

[Quote=引用 5 楼 chd2001 的回复:]
排名依据：
1、积分
2、净胜球
3、进球数

以上都相同则并列
[/Quote]
4.两队之间胜负关系.

估计用SQL是难搞了.

[zanyzyg]

select 球队,sum(比赛场次) as 比赛场次,sum(胜) as 胜,sum(平) as 平,sum(负) as 负,sum(进球) as 进球,sum(失球) as 失球
from
(
select 主队 as 球队, count(*) as 比赛场次,sum(case when 主队得分>客队得分 then 1 else 0 end) as 胜,
sum(case when 主队得分=客队得分 then 1 else 0 end) as 平,
sum(case when 主队得分<客队得分 then 1 else 0 end) as 负,
sum(主队得分) as 进球,
sum(客队得分) as 失球
from match
group by 主队
union all
select 客队 as 球队, count(*) as 比赛场次,sum(case when 主队得分<客队得分 then 1 else 0 end) as 胜,
sum(case when 主队得分=客队得分 then 1 else 0 end) as 平,
sum(case when 主队得分>客队得分 then 1 else 0 end) as 负,
sum(客队得分) as 进球,
sum(主队得分) as 失球
from match
group by 客队
)temptable
group by 球队

具体细节再修改一下就可以了

[utpcb]

select
t.球队,
count(*) as 比赛场次,
sum(t.胜) as 胜,
sum(t.平) as 平,
sum(t.负) as 负,
sum(t.进球) as 进球,
sum(t.失球) as 失球,
sum(t.进球-t.失球) as 净胜球,
sum(t.胜*3+t.平) as 积分
from
(select
主队 as 球队,
(case when 主队得分>客队得分 then 1 else 0 end) as 胜,
(case when 主队得分=客队得分 then 1 else 0 end) as 平,
(case when 主队得分<客队得分 then 1 else 0 end) as 负,
主队得分 as 进球,
客队得分 as 失球
from
match
union all
select
客队 as 球队,
(case when 客队得分>主队得分 then 1 else 0 end) as 胜,
(case when 客队得分=主队得分 then 1 else 0 end) as 平,
(case when 客队得分<主队得分 then 1 else 0 end) as 负,
客队得分 as 进球,
主队得分 as 失球
from
match) t
group by
t.球队
order by
积分 desc

[子陌红尘]

--得出名次之外的比赛信息
select
    0 as 名次,
    t.球队,
    count(t.*) as 比赛场次,
    sum(t.胜) as 胜,
    sum(t.平) as 平,
    sum(t.负) as 负,
    sum(t.进球) as 进球,
    sum(t.失球) as 失球,
    sum(t.进球-t.失球) as 净胜球,
    sum(t.胜*3+t.平) as 积分
into #score
from
    (select 
         主队 as 球队,  
         (case when 主队得分>客队得分 then 1 else 0 end) as 胜,
         (case when 主队得分=客队得分 then 1 else 0 end) as 平,
         (case when 主队得分<客队得分 then 1 else 0 end) as 负,
         主队得分 as 进球,
         客队得分 as 失球
     from 
         match
     union all
     select 
         客队 as 球队,  
         (case when 客队得分>主队得分 then 1 else 0 end) as 胜,
         (case when 客队得分=主队得分 then 1 else 0 end) as 平,
         (case when 客队得分<主队得分 then 1 else 0 end) as 负,
         客队得分 as 进球,
         主队得分 as 失球
     from 
         match) t
group by
    t.球队
order by
    积分,净胜球,进球 desc


--计算名次
update t
set
    名次=(select 
              isnull(count(*),0)+1 
          from 
              #score 
          where 
              积分<t.积分 
              or 
              (积分=t.积分 and 净胜球<t.净胜球) 
              or
              (积分=t.积分 and 净胜球=t.净胜球 and 进球<t.进球))
from
    #score t


--查看最终结果
select * from #score order by 名次,积分,净胜球,进球

[playwarcraft]

--就用子查詢寫一個好了，也不會很多資料，效率不考慮了
--名次有些聯賽，如果積分相同，是看各隊之間的相互戰績，就更麻煩了

create table match(id int identity(1,1),[date] datetime,turn int, type varchar(10),team_A varchar(10), team_B varchar(10), A_s int, B_s int)
insert into match select '2008-4-5',30,'E','AAA','BBB',3,1
insert into match select '2008-4-5',30,'E','CCC','DDD',0,0
insert into match select '2008-4-5',30,'E','EEE','FFF',1,2
insert into match select '2008-4-12',31,'E','BBB','CCC',1,1
insert into match select '2008-4-12',31,'E','EEE','AAA',2,0
insert into match select '2008-4-12',31,'E','FFF','DDD',1,2


select  mingci=identity(int,1,1), *
into #t
from
(select top 100 percent      team,
           num,
           win,
           ping,
           fail,
           goal,
           lose,
           goal-lose as g_w,
           3*win+ping as s
from
(
select team,
  isnull((select max(turn) from match where team_A=T.team or team_B=T.team),0) as num,

 isnull( (select  sum(case when A_s>B_s then 1 else 0 end )  from match where team_A=T.team),0)
 +  isnull( (select  sum(case when B_s>A_s then 1 else 0 end )  from match where team_B=T.team),0)  as win,

 isnull( (select  sum(case  when A_s=B_s then 1 else 0 end)  
    from match where team_A=T.team or team_B=T.team),0) as ping,

 isnull( (select  sum(case when A_s<B_s then 1 else 0 end )  from match where team_A=T.team),0)
 +  isnull( (select  sum(case when B_s<A_s then 1 else 0 end )  from match where team_B=T.team),0)  as fail,

 isnull( (select  sum(A_s )  from match where team_A=T.team),0)
 +   isnull((select  sum(B_s )  from match where team_B=T.team),0)  as goal,

 isnull( (select  sum(B_s )  from match where team_A=T.team),0)
 +  isnull( (select  sum(A_s )  from match where team_B=T.team),0)  as lose
  
from  (select  distinct team_A as team 
      from (select team_A from match union all select team_B from match) t1) T
) Z
order by s desc,g_w desc
) X

select * from #t order by mingci
/*
mingci      team       num         win         ping        fail        goal        lose        g_w         s           
----------- ---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 
1           DDD        31          1           1           0           2           1           1           4
2           EEE        31          1           0           1           3           2           1           3
3           FFF        31          1           0           1           3           3           0           3
4           AAA        31          1           0           1           3           3           0           3
5           CCC        31          0           2           0           1           1           0           2
6           BBB        31          0           1           1           2           4           -2          1
*/

drop table match,#t