足球比赛的积分，请问只有赛程表，通过sql语句能不能写出积分

Question

本帖最后由 wzy528 于 2011-03-27 21:02:19 编辑

Answer 1

CREATE Table BiFen(
id int identity(1,1),
LunCi int,
BiSaiRiQi datetime,
ZhuDui varchar(20),
KeDui varchar(20),
ZhuDuiJinQiu int,
KeDuiJinQiu int
)
insert into BiFen Values (1,'2010-6-14','德国','澳大利亚',4,0);
insert into BiFen Values (1,'2010-6-13','塞尔维亚','加纳',0,1);insert into BiFen Values (2,'2010-6-18','德国','塞尔维亚',0,1);
insert into BiFen Values (2,'2010-6-19','加纳','澳大利亚',1,1);insert into BiFen Values (3,'2010-6-24','加纳','德国',0,1);
insert into BiFen Values (3,'2010-6-24','澳大利亚','塞尔维亚',2,1);

Answer 2

select row_number() over(order by sum(jifen)) mingci,qiudui,sum(jifen) jifen,sum(sheng) sheng,sum(ping) ping,
     sum(fu) fu,sum(jinqiu) jinqiu,sum(diuqiu) diuqiu
(select zhudui as qiudui,sum(case when ZhuDuiJinQiu>keDuiJinQiu then 3 when ZhuDuiJinQiu=keDuiJinQiu then 1 else 0 end) jifen,
          sum(case when ZhuDuiJinQiu>keDuiJinQiu then 1 else o end) sheng,sum(case when ZhuDuiJinQiu=keDuiJinQiu then 1 else 0) ping,
          sum(case when ZhuDuiJinQiu<keDuiJinQiu then 1 else 0 end) fu,sum(ZhuDuiJinQiu) jinqiu,sum(keduijinqiu) diuqiu
from BiFen group by zhudui
union all
select kedui,sum(case when ZhuDuiJinQiu<keDuiJinQiu then 3 when ZhuDuiJinQiu=keDuiJinQiu then 1 else 0 end) jifen,
          sum(case when ZhuDuiJinQiu<keDuiJinQiu then 1 else o end) sheng,sum(case when ZhuDuiJinQiu=keDuiJinQiu then 1 else 0) ping,
          sum(case when ZhuDuiJinQiu>keDuiJinQiu then 1 else 0 end) fu,sum(keDuiJinQiu) jinqiu,sum(zhuduijinqiu) diuqiu
from BiFen group by kedui) a group by qiudui

Answer 3

select mingci=Identity(int,1,1),qiudui,sum(jifen) jifen,sum(sheng) sheng,sum(ping) ping,
     sum(fu) fu,sum(jinqiu) jinqiu,sum(diuqiu) diuqiu into #tb from 
(select zhudui as qiudui,sum(case when ZhuDuiJinQiu>keDuiJinQiu then 3 when ZhuDuiJinQiu=keDuiJinQiu then 1 else 0 end) jifen,
          sum(case when ZhuDuiJinQiu>keDuiJinQiu then 1 else o end) sheng,sum(case when ZhuDuiJinQiu=keDuiJinQiu then 1 else 0) ping,
          sum(case when ZhuDuiJinQiu<keDuiJinQiu then 1 else 0 end) fu,sum(ZhuDuiJinQiu) jinqiu,sum(keduijinqiu) diuqiu
from BiFen group by zhudui
union all
select kedui,sum(case when ZhuDuiJinQiu<keDuiJinQiu then 3 when ZhuDuiJinQiu=keDuiJinQiu then 1 else 0 end) jifen,
          sum(case when ZhuDuiJinQiu<keDuiJinQiu then 1 else o end) sheng,sum(case when ZhuDuiJinQiu=keDuiJinQiu then 1 else 0) ping,
          sum(case when ZhuDuiJinQiu>keDuiJinQiu then 1 else 0 end) fu,sum(keDuiJinQiu) jinqiu,sum(zhuduijinqiu) diuqiu
from BiFen group by kedui) a group by qiudui order by sum(jifen)
select * from #tb

Answer 4

少个from
select row_number() over(order by sum(jifen)) mingci,qiudui,sum(jifen) jifen,sum(sheng) sheng,sum(ping) ping,
     sum(fu) fu,sum(jinqiu) jinqiu,sum(diuqiu) diuqiu from
(select zhudui as qiudui,sum(case when ZhuDuiJinQiu>keDuiJinQiu then 3 when ZhuDuiJinQiu=keDuiJinQiu then 1 else 0 end) jifen,
          sum(case when ZhuDuiJinQiu>keDuiJinQiu then 1 else o end) sheng,sum(case when ZhuDuiJinQiu=keDuiJinQiu then 1 else 0) ping,
          sum(case when ZhuDuiJinQiu<keDuiJinQiu then 1 else 0 end) fu,sum(ZhuDuiJinQiu) jinqiu,sum(keduijinqiu) diuqiu
from BiFen group by zhudui
union all
select kedui,sum(case when ZhuDuiJinQiu<keDuiJinQiu then 3 when ZhuDuiJinQiu=keDuiJinQiu then 1 else 0 end) jifen,
          sum(case when ZhuDuiJinQiu<keDuiJinQiu then 1 else o end) sheng,sum(case when ZhuDuiJinQiu=keDuiJinQiu then 1 else 0) ping,
          sum(case when ZhuDuiJinQiu>keDuiJinQiu then 1 else 0 end) fu,sum(keDuiJinQiu) jinqiu,sum(zhuduijinqiu) diuqiu
from BiFen group by kedui) a group by qiudui

Answer 5

select 排名=row_number() over(order by sum(得分) desc),qiudui,得分=sum(得分),胜场=sum(胜场),胜场=sum(平场),负场=sum(负场),进球=sum(zhuduijinqiu),丢球=sum(keduijinqiu)
from
(
select qiudui=zhudui,得分=case when zhuduijinqiu>keduijinqiu then 3
                        when zhuduijinqiu=keduijinqiu then 1
                        else 0 end,
              胜场=case when zhuduijinqiu>keduijinqiu then 1 else 0 end,
              平场=case when zhuduijinqiu=keduijinqiu then 1 else 0 end,
              负场=case when zhuduijinqiu<keduijinqiu then 1 else 0 end,
              zhuduijinqiu,keduijinqiu
from bifen
union
select qiudui=kedui,得分=case when zhuduijinqiu<keduijinqiu then 3
                        when zhuduijinqiu=keduijinqiu then 1
                        else 0 end,
              胜场=case when zhuduijinqiu<keduijinqiu then 1 else 0 end,
              平场=case when zhuduijinqiu=keduijinqiu then 1 else 0 end,
              负场=case when zhuduijinqiu>keduijinqiu then 1 else 0 end,
              keduijinqiu,zhuduijinqiu
from bifen
) K
group by qiudui
order by sum(得分) desc
1 德国 6 2 0 1 5 1
2 加纳 4 1 1 1 2 2
3 澳大利亚 4 1 1 1 3 6
4 塞尔维亚 3 1 0 2 2 3

Answer 6

CREATE Table BiFen
(
id int identity(1,1),
LunCi int,
BiSaiRiQi datetime,
ZhuDui varchar(20),
KeDui varchar(20),
ZhuDuiJinQiu int,
KeDuiJinQiu int
)
insert into BiFen Values (1,'2010-6-14','德国','澳大利亚',4,0);
insert into BiFen Values (1,'2010-6-13','塞尔维亚','加纳',0,1);
insert into BiFen Values (2,'2010-6-18','德国','塞尔维亚',0,1);
insert into BiFen Values (2,'2010-6-19','加纳','澳大利亚',1,1);
insert into BiFen Values (3,'2010-6-24','加纳','德国',0,1);
insert into BiFen Values (3,'2010-6-24','澳大利亚','塞尔维亚',2,1);
select * from bifen;with abc as 
(
select lunci,zhudui,kedui,zhuduijinqiu,keduijinqiu,
case when zhuduijinqiu>keduijinqiu then 1 else 0 end as win,
case when zhuduijinqiu=keduijinqiu then 1 else 0 end as ping,
case when zhuduijinqiu<keduijinqiu then 1 else 0 end as loss,
case when zhuduijinqiu>keduijinqiu then 3 
 when zhuduijinqiu=keduijinqiu then 1
 else 0 end as zhuduijifen,
case when zhuduijinqiu>keduijinqiu then 0 
 when zhuduijinqiu=keduijinqiu then 1
 else 3 end as keduijifen
from bifen 
)
--select * from abc
,efg as
(select zhudui as duiwu,sum(win) as win,sum(ping) as ping,sum(loss) as loss,sum(zhuduijinqiu) as jinqiu,sum(keduijinqiu) as diuqiu,sum(zhuduijifen) as jifen from abc group by zhudui
union 
select kedui as duiwu,sum(loss) as win,sum(ping) as ping,sum(win) as loss,sum(keduijinqiu) asjinqiu,sum(zhuduijinqiu) as diuqiu,sum(keduijifen) as jifen from abc group by kedui)
--select * from efg
select duiwu,sum(jifen) as '积分',sum(win) as '胜',sum(ping) as '平',sum(loss) as '负',
sum(jinqiu) as '进球',sum(diuqiu) as '丢球' from efg group by duiwu order by '积分' desc

Answer 7

use tempdb;
/*
CREATE Table BiFen(
id int identity(1,1),
LunCi int,
BiSaiRiQi datetime,
ZhuDui varchar(20),
KeDui varchar(20),
ZhuDuiJinQiu int,
KeDuiJinQiu int
);insert into BiFen Values (1,'2010-6-14','德国','澳大利亚',4,0);
insert into BiFen Values (1,'2010-6-13','塞尔维亚','加纳',0,1);insert into BiFen Values (2,'2010-6-18','德国','塞尔维亚',0,1);
insert into BiFen Values (2,'2010-6-19','加纳','澳大利亚',1,1);insert into BiFen Values (3,'2010-6-24','加纳','德国',0,1);
insert into BiFen Values (3,'2010-6-24','澳大利亚','塞尔维亚',2,1);
*/
--SQL 2005写法
select 
ROW_NUMBER() over(order by SUM(t.[积分]) desc,SUM(t.净胜球) desc) as [名称],
t.[球队],
ISNULL(SUM(t.[积分]),0) as [积分],
ISNULL(SUM(t.[胜]),0) as [胜],
ISNULL(SUM(t.[平]),0) as [平],
ISNULL(SUM(t.[负]),0) as [负],
ISNULL(SUM(t.进球),0) as [进球],
ISNULL(SUM(t.丢球),0) as [丢球]
from
(
select 
t1.ZhuDui as [球队],
(case when t1.ZhuDuiJinQiu > t1.KeDuiJinQiu then 3 
when t1.ZhuDuiJinQiu = t1.KeDuiJinQiu then 1 
else 0 end) as [积分],
case when t1.ZhuDuiJinQiu > t1.KeDuiJinQiu then 1 end as [胜],
case when t1.ZhuDuiJinQiu = t1.KeDuiJinQiu then 1 end as [平],
case when t1.ZhuDuiJinQiu < t1.KeDuiJinQiu then 1 end as [负],
t1.ZhuDuiJinQiu as [进球],
t1.KeDuiJinQiu as [丢球],
(t1.ZhuDuiJinQiu - t1.KeDuiJinQiu) as [净胜球]
from BiFen as t1
union
select
t2.KeDui as [球队],
(case when t2.KeDuiJinQiu > t2.ZhuDuiJinQiu then 3 
when t2.KeDuiJinQiu = t2.ZhuDuiJinQiu then 1 
else 0 end) as [积分],
case when t2.KeDuiJinQiu > t2.ZhuDuiJinQiu then 1 end as [胜],
case when t2.KeDuiJinQiu = t2.ZhuDuiJinQiu then 1 end as [平],
case when t2.KeDuiJinQiu < t2.ZhuDuiJinQiu then 1 end as [负],
t2.KeDuiJinQiu as [进球],
t2.ZhuDuiJinQiu as [丢球],
(t2.KeDuiJinQiu - t2.ZhuDuiJinQiu) as [净胜球]
from BiFen as t2
) as t
group by t.[球队]
order by [积分] desc,SUM(t.净胜球) desc;--SQL 2000写法
select identity(int,1,1) as [名称],
t.[球队],
ISNULL(SUM(t.[积分]),0) as [积分],
ISNULL(SUM(t.[胜]),0) as [胜],
ISNULL(SUM(t.[平]),0) as [平],
ISNULL(SUM(t.[负]),0) as [负],
ISNULL(SUM(t.进球),0) as [进球],
ISNULL(SUM(t.丢球),0) as [丢球]
into #test
from
(
select 
t1.ZhuDui as [球队],
(case when t1.ZhuDuiJinQiu > t1.KeDuiJinQiu then 3 
when t1.ZhuDuiJinQiu = t1.KeDuiJinQiu then 1 
else 0 end) as [积分],
case when t1.ZhuDuiJinQiu > t1.KeDuiJinQiu then 1 end as [胜],
case when t1.ZhuDuiJinQiu = t1.KeDuiJinQiu then 1 end as [平],
case when t1.ZhuDuiJinQiu < t1.KeDuiJinQiu then 1 end as [负],
t1.ZhuDuiJinQiu as [进球],
t1.KeDuiJinQiu as [丢球],
(t1.ZhuDuiJinQiu - t1.KeDuiJinQiu) as [净胜球]
from BiFen as t1
union
select
t2.KeDui as [球队],
(case when t2.KeDuiJinQiu > t2.ZhuDuiJinQiu then 3 
when t2.KeDuiJinQiu = t2.ZhuDuiJinQiu then 1 
else 0 end) as [积分],
case when t2.KeDuiJinQiu > t2.ZhuDuiJinQiu then 1 end as [胜],
case when t2.KeDuiJinQiu = t2.ZhuDuiJinQiu then 1 end as [平],
case when t2.KeDuiJinQiu < t2.ZhuDuiJinQiu then 1 end as [负],
t2.KeDuiJinQiu as [进球],
t2.ZhuDuiJinQiu as [丢球],
(t2.KeDuiJinQiu - t2.ZhuDuiJinQiu) as [净胜球]
from BiFen as t2
) as t
group by t.[球队]
order by [积分] desc,SUM(t.净胜球) desc;select * from #test;