SQL查询以正确的顺序比较两个表并输出结果到两个报表

拉里·劳斯（Larry Rouse）

我正在开发一个钓鱼比赛的软件，该软件支持在杰克逊维尔（Jacksonville）维护海滨，并且我正在学习SQL。对于这个问题，我什至不知道从哪里开始：

我正在使用SQL Server 2008，并且有四个表：

Anglers, Boats, VIG_FishWeight and VIG_FishAggWeight

Anglers:
Angler_ANID, ANLname, ANFName, ANCity, ANState

Boats:
BT_ANID, BoatName

VIG_Fishweight:
FWAngler_ANID, FWBoatNumber, FWWeight,FWTimeStamp 

VIG_FishAggWeight:
FAAngler_ANID, FABoatNumber,FAggWeight,FATimestamp

VIG_FishWeight 包含所有称重的鱼类的清单。

VIG_FishAggWeight 包含每个钓鱼者称重的所有鱼的总和

ANID是Angler ID，一个自动生成的数字，在所有表格中都保持一致。

该锦标赛在最大的鱼和最大的总重量锦标赛中支付前25名。

如果钓鱼者的总重量比其最大鱼重高，则他将被置于总锦标赛中。基本上，每个钓鱼者都将在锦标赛中排名，这将使他获得最大的奖金。

我需要一个查询来创建两个报告，一个报告包含最大的鱼，另一个报告包含以下内容的汇总：

Rank, Boat Number AnglerFNAme, AnglerLName, ANCity, ANState, Weight

就像我说的，我不知道从哪里开始，所以我们将不胜感激。

shawnt00

只是为了好玩，我想把一个放在一起select，所以我很好奇，看看这是否真的产生了您想要描述的结果。听起来钓鱼者只能参加两次锦标赛之一（也只能参加一次最大的比赛）。

with individual as (
   select
        FAAngler_ANID as AnglerId, FWWeight as Weight,
        rank() over (order by FWWeight desc) as Rnk
    from VIG_FishWeight
), aggregate as (
    select
        FAAngler_ANID as AnglerId, sum(FWWeight) as Weight,
        rank() over (order by sum(FWWeight) desc) as Rnk
    from VIG_FishWeight
    group by FAAngler_ANID
), combined as (
    select
        i.AnglerId,
        /* Aggregate ranking must beat all the Individual rankings to win out.
           Remember there might be multiple fish on the Individual side. */
        case when min(a.Rnk) < min(i.Rnk)
             then 'Aggregate' else 'Big Fish' end as Tournament,
        case when min(a.Rnk) < min(i.Rnk)
             then min(a.Rnk) else min(i.Rnk) end as Rnk,
        case when min(a.Rnk) < min(i.Rnk)
             then max(a.Weight) else max(i.Weight) end as Weight
    from individual as i inner join aggregate as a on a.AnglerId = i.AnglerId
    group by i.AnglerId
), tournament as (
    select
        AnglerId, Tournament, Weight,
        rank() over (partition by Tournament order by Rnk) as TournamentRank
    from combined
)
select Tournament, TournamentRank, AnglerId, Weight
from tournament /* inner join to Anglers table for name, etc. */
where TournamentRank <= 25
order by Tournament, TournamentRank;

http://rextester.com/discussion/LBF76134/Ranking-split-across-tournaments

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。