Question

我的游戏成果表有MS-SQL,其中包含一个家庭团队、家庭团队的分数、小组的分数以及团队分数。我试图问小组,首先打赢,损失,总得赢,然后是总损失(损失)。表格不含,包括一栏或一栏损失。必须根据分数计算赢利和损失。如果我需要提出一个观点或临时表格,我如何与团队、赢利和损失形成这种看法?

Answer 1

select 
    teamName, 
     wins  = 
        case 
            when wins is null then 0 
            else wins 
        end, 
     losses  = 
        case 
            when losses is null then 0 
            else losses 
        end 
from 
    teams 
left join 
( 
    select 
        a.wins as wins, 
        b.losses as losses, 
         team  = 
            case 
                when a.team is null then b.team 
                else a.team 
            end 
    from 
    ( 
        select 
            homeTeam as team, 
            sum(wins) as wins 
        from 
        ( 
            select 
                homeTeam, 
                count(*) as wins 
            from 
                results 
            where 
                homeScore > awayScore 
            group by 
                homeTeam 
            union all 
            select 
                awayTeam, 
                count(*) as wins 
            from 
                results 
            where 
                homeScore < awayScore 
            group by 
                awayTeam         
        ) a 
        group by homeTeam 
    ) a 
    full outer join 
    ( 
        select 
            homeTeam as team, 
            sum(losses) as losses 
        from 
        ( 
            select 
                homeTeam, 
                count(*) as losses 
            from 
                results 
            where 
                homeScore < awayScore 
            group by 
                homeTeam 
            union all 
            select 
                awayTeam, 
                count(*) as losses 
            from 
                results 
            where 
                homeScore > awayScore 
            group by 
                awayTeam 
        ) a 
        group by 
            homeTeam 
    ) b 
    on a.team = b.team 
) c 
on teams.id = c.team

Answer 2

SELECT *
FROM MyTable
WHERE ...
ORDER BY SUM(win) DESC, SUM(loss) ASC

更详细的答复是提供你的图表和样本数据。

Answer 3

假设你能够把这些信息撤回到你的询问中,你可以使用<代码>中的合计数。页: 1 条款:

ORDER BY
    SUM(win) DESC
   ,SUM(loss) ASC

<>strong>edit/strong>: Update for comment

With only the scores being stored, you ll want to either use a temporary table or a CTE (depends on the DBMS you are using if the support them) to first create a table that has the teams and wins and losses in them, and then you can SELECT from that and ORDER accordingly.

Answer 4

家庭工作

不管怎么说,你可以通过按顺序排出一栏来具体注明发言顺序。例如,

select x, y, z from table order by x asc, y desc, z asc

友情链接