主队 select 轮次 ,主队 , case when 主队比分>客队比分 then '胜' when 主队比分=客队比分 then '平' else '负' end,主队比分 进球,客队比分 失球 from table客队 select 轮次 ,客队 , case when 主队比分< 客队比分 then '胜' when 主队比分=客队比分 then '平' else '负' end,客队比分 进球 ,主队比分 失球 from table
select 主队,客队, case '胜' = when 主队比分>客队比分 then 1 else 0 end, case '平' = when 主队比分=客队比分 then 1 else 0 end, case '负' = when 主队比分<客队比分 then 1 else 0 end, 主队比分 进球,客队比分 失球 from table
Try 如果表内容为:1 AC米兰 国际米兰 5 3 select 轮次 , 主队 ,case when 主队比分>客队比分 then '胜' when 主队比分=客队比分 then '平' else '负' end,主队比分 as 进球,客队比分 as 失球 from tableselect 轮次 , 客队 ,case when 客队比分〉>主队比分 then '胜' when 客队比分=主队比分 then '平' else '负' end,客队比分 as 进球, 主队比分 as 失球 from table
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
select 轮次 ,主队 球队 ,(select sum(case when (a.主队=主队 and 主队比分>客队比分) or (a.主队=客队 and 主队比分<客队比分) then 1 else 0 end) from table where int <=a.int ) as 胜, (select sum(case when 主队比分=客队比分 then 1 else 0 end) from table where int <=a.int) as 平, (select sum(case when (a.主队=主队 and 主队比分<客队比分) or (a.主队=客队 and 主队比分>客队比分) then 1 else 0 end) from table where int <=a.int) as 负 , (select sum(case then a.主队=主队 then 主队比分 when a.主队=客队 else 客队比分 else 0 end ) from table where int <=a.int) as 进球, (select sum(case then a.主队=主队 then 客队比分 when a.主队=客队 else 主队比分 else 0 end ) from table where int <=a.int) as 失球from table a
select 轮次 ,主队 , case when 主队比分>客队比分 then '胜' when 主队比分=客队比分 then '平' else '负' end,主队比分 进球,客队比分 失球
from table客队
select 轮次 ,客队 , case when 主队比分< 客队比分 then '胜' when 主队比分=客队比分 then '平' else '负' end,客队比分 进球 ,主队比分 失球 from table
case '胜' = when 主队比分>客队比分 then 1 else 0 end,
case '平' = when 主队比分=客队比分 then 1 else 0 end,
case '负' = when 主队比分<客队比分 then 1 else 0 end,
主队比分 进球,客队比分 失球
from table
如果表内容为:1 AC米兰 国际米兰 5 3
select 轮次 , 主队 ,case when 主队比分>客队比分 then '胜' when 主队比分=客队比分 then '平' else '负' end,主队比分 as 进球,客队比分 as 失球
from tableselect 轮次 , 客队 ,case when 客队比分〉>主队比分 then '胜' when 客队比分=主队比分 then '平' else '负' end,客队比分 as 进球, 主队比分 as 失球
from table
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
(select sum(case when 主队比分=客队比分 then 1 else 0 end) from table where int <=a.int) as 平,
(select sum(case when (a.主队=主队 and 主队比分<客队比分) or (a.主队=客队 and 主队比分>客队比分) then 1 else 0 end) from table where int <=a.int) as 负 ,
(select sum(case then a.主队=主队 then 主队比分 when a.主队=客队 else 客队比分 else 0 end ) from table where int <=a.int) as 进球,
(select sum(case then a.主队=主队 then 客队比分 when a.主队=客队 else 主队比分 else 0 end ) from table where int <=a.int) as 失球from table a