难题！求一视图写法～100分相送！！
现有数据库表：
字段有
轮次 int
主队 varchar
客队 varchar
主队比分 int
客队比分 int
现要求一个视图
显示 胜、平、负、进球、失球
该如何写这个视图，写出来现场就给分！
问题点数：100、回复次数：9Top
1 楼tinyhuhu（~~～御风而行～~~）回复于 2003-06-02 16:59:11 得分 0
怎么没人理？？
自己顶一下，先！Top
2 楼cpp2017（慕白兄）回复于 2003-06-02 17:06:10 得分 100
因为没有进球，失球字段所以。。
create view aa as
Select Distinct 主队,轮次,case when A.主队比分>A.客队比分 then 1
else case when A.主队比分<A.客队比分 then -1
else 0 end end
from table as A
union select
Distinct 客队,轮次,case when A.主队比分>A.客队比分 then -1
else case when A.主队比分<A.客队比分 then 1
else 0 end end
from table as A
Top
3 楼tinyhuhu（~~～御风而行～~~）回复于 2003-06-02 17:30:49 得分 0
可能是我没有说清楚：
胜、负球字段为累计值：比如
某队参赛6场，那么他可能的一种情况就是： 胜3、平2、负1
如果再赛一场，则会在相应情况下累加
我就是想问这个功能该如何实现～Top
4 楼tinyhuhu（~~～御风而行～~~）回复于 2003-06-02 17:34:20 得分 0
up！！Top
5 楼cpp2017（慕白兄）回复于 2003-06-02 17:45:02 得分 0
改一改,不知可行否
create view aa as
select
Team.team_name,( (Select count(轮次) from table1 where 主队=Team.team_name and 主队比分>客队比分)+
(Select count(轮次) from table1 where 客队=Team.team_name and 主队比分<客队比分)) as win,
(Select count(轮次) from table1 where 主队=Team.team_name and 主队比分<客队比分)+
(Select count(轮次) from table1 where 客队=Team.team_name and 主队比分>客队比分)) as lost,
(Select count(轮次) from table1 where 主队=Team.team_name and 主队比分=客队比分) as equip
from
(select distinct 主队 as team_name from table1 union select distinct 客队 as team_name from table1) as Team
Top
6 楼cpp2017（慕白兄）回复于 2003-06-02 17:52:22 得分 0
再改一改，以下测试成功!
create view aa as
select
Team.team_name,( (Select count(轮次) from table1 where 主队=Team.team_name and 主队比分>客队比分)+
(Select count(轮次) from table1 where 客队=Team.team_name and 主队比分<客队比分)) as win,
(Select count(轮次) from table1 where 主队=Team.team_name and 主队比分<客队比分)+
(Select count(轮次) from table1 where 客队=Team.team_name and 主队比分>客队比分)
as lost,
((Select count(轮次) from table1 where 主队=Team.team_name and 主队比分=客队比分) +(Select count(轮次) from table1 where 客队=Team.team_name and 主队比分=客队比分 )) as equip
from
(select distinct 主队 as team_name from table1 union select distinct 客队 as team_name from table1) as Team
Top
7 楼cpp2017（慕白兄）回复于 2003-06-02 18:15:31 得分 0
再加两个字段
create view aa as
select
Team.team_name,( (Select count(轮次) from table1 where 主队=Team.team_name and 主队比分>客队比分)+
(Select count(轮次) from table1 where 客队=Team.team_name and 主队比分<客队比分)) as win,
(Select count(轮次) from table1 where 主队=Team.team_name and 主队比分<客队比分)+
(Select count(轮次) from table1 where 客队=Team.team_name and 主队比分>客队比分)
as lost,
((Select count(轮次) from table1 where 主队=Team.team_name and 主队比分=客队比分) +(Select count(轮次) from table1 where 客队=Team.team_name and 主队比分=客队比分 )) as equip
,
( (select isnull(sum(主队比分),0) from table1 where 主队=Team.team_name)+(select isnull(sum(客队比分),0) from table1 where 客队=Team.team_name)) as 进球,
((select isnull(sum(主队比分),0) from table1 where 客队=Team.team_name)+(select isnull(sum(客队比分),0) from table1 where 主队=Team.team_name)) as 失球
from
(select distinct 主队 as team_name from table1 union select distinct 客队 as team_name from table1) as Team
Top
8 楼xjy521（笑剑影）回复于 2003-06-02 18:40:45 得分 0
帮你顶一下。Top
9 楼tinyhuhu（~~～御风而行～~~）回复于 2003-06-02 18:51:04 得分 0
谢谢，揭帖。
散分！！ ^_^Top
