请教关于联赛的积分统计问题

Question

I'll give you answer tonight If you can wait.

Answer 1

I'll give you score tomorrow If I satisfy.

Answer 2

如果你的赛程安排表中记录数不多，完全不需要积分榜表，因为可以写一个view来计算。
如果你用想要建积分榜表，update的时候很麻烦，要通过trigger来完成。以下的方案供你参考：
表：赛程安排（轮次，主队，客队，主队得分，客队得分）
Table: agenda(round_no, team1, team2, score1, score2)生成view（ORACLE PL/SQL格式）:
select rownum rank, 
       team, 
       win_games, draw_games, loss_games, 
       total_score, total_loss, total_point
  from (
        select team, 
               sum(win_game)  win_games, 
               sum(draw_game) draw_games,
               sum(loss_game) loss_games,
               sum(score)     total_score,
               sum(loss)      total_loss,
               sum(point)     total_point
          from (
                select team1 team, 
                       decode(sign(score1 - score2), 1, 1, 0） win_game,  -- 胜
                       decode(sign(score1 - score2), 0, 1, 0） draw_game, -- 平
                       decode(sign(score1 - score2),-1, 1, 0） loss_game, -- 负
                       score1 score, -- 进球
                       score2 loss,  -- 失球
                       decode(sign(score1 - score2), 1, 3,  -- 胜积3分
                                                     0, 1,  -- 平积1分
                                                        0) point, -- 负积0分
                  from agenda
                uion all
                select team2 team,
                       decode(sign(score2 - score1), 1, 1, 0） win_game,  -- 胜
                       decode(sign(score2 - score1), 0, 1, 0） draw_game, -- 平
                       decode(sign(score2 - score1),-1, 1, 0） loss_game, -- 负
                       score2 score, -- 进球
                       score1 loss,  -- 失球
                       decode(sign(score2 - score1), 1, 3,  -- 胜积3分
                                                     0, 1,  -- 平积1分
                                                        0) point, -- 负积0分                  from agenda
                )
         group by team
        )
 order by total_point, (total_score - total_loss), total_score祝你好运！

Answer 3

decode一句没看懂，能不能解释一下decode的用法

Answer 4

decode(expr, para1, value1,
             para2, value2,
             ...
                    value_default)get expr
if expr = para1 then
   decode() = value1
else if expr = para2 then
        decode = value2
else....else
   decode() = value_default
end ifcan you understand?

Answer 5

如果你的數據庫是SQL Server,
那麼將decode(...)換成case when ... then ... else ... end

Answer 6

agree with zhuzhichao(竹之草), and I think IIF(expr, true_value, flase_value) can also do this. depends on what database you're using.

Answer 7

在select的from中可以在用select嵌套吗？我这边不好用

Answer 8

I don't know, if your database can't use union. but if you can use union, you can create views regarding on all the sub-queries. for example:
create 1 view, as
 select team1 team, 
       decode(sign(score1 - score2), 1, 1, 0? win_game,  -- ?
       decode(sign(score1 - score2), 0, 1, 0? draw_game, -- ?
       decode(sign(score1 - score2),-1, 1, 0? loss_game, -- ?
       score1 score, -- ??
       score2 loss,  -- ??
       decode(sign(score1 - score2), 1, 3,  -- ??3?
                                     0, 1,  -- ??1?
                                         0) point, -- ??0?
   from agenda
 uion all
 select team2 team,
       decode(sign(score2 - score1), 1, 1, 0? win_game,  -- ?
       decode(sign(score2 - score1), 0, 1, 0? draw_game, -- ?
       decode(sign(score2 - score1),-1, 1, 0? loss_game, -- ?
       score2 score, -- ??
       score1 loss,  -- ??
       decode(sign(score2 - score1), 1, 3,  -- ??3?
                                     0, 1,  -- ??1?
                                         0) point, -- ??0?
   from agendasave it, let's say 'v_score_detail'.
then you can select ... from v_scroe_detailgood luck!