SQL查询，将两个表和输出结果按适当顺序输出到两个报表

Question

我正在为一个钓鱼比赛开发软件，支持在杰克逊维尔这里维护海滨，我正在学习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

就像我说的，我不知道从哪里开始，所以任何帮助都是非常感谢的。

Answer 1

只是为了好玩，我想把一个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

Answer 2

解决了。下面是我使用的查询：

INSERT INTO WeightsScoreTable
    (ANFName, ANLName, BNBoatNAme, ANCity, ANState, BNBoatNumber, ANBFWeight, ANAggWeight)
     SELECT p.FAAFName, p.FAALName, p.BAABoatName, p.FAACity, p.FAAState, p.FAABoatNumber, c.FWWeight, p.FAAAggWieght
     FROM FAAReport AS p LEFT OUTER JOIN
     WeightsScoreTable AS n ON n.BNBoatNumber = p.FAABoatNumber LEFT OUTER JOIN
     FWReport AS c ON p.FAABoatNumber =c.FWABoatNumber
     ORDER BY ANBFWeight DESC

     DECLARE @LoopValue INT
     SET @LoopValue = (SELECT COUNT (*)
     FROM WeightsScoreTable)

     WHILE @LoopValue > 0
     BEGIN

     INSERT INTO BigFishScore (ANFName, ANLName, BNBoatName, ANCity, ANState, ANBFWeight, BNBoatNumber)
     SELECT ANFName, ANLName, BNBoatName, ANCity, ANState, ANBFWeight, BNBoatNumber
     FROM WeightsScoreTable
     WHERE (ANBFWeight = (SELECT MAX(ANBFWeight) AS Expr1
     FROM WeightsScoreTable))
     ORDER BY ANBFWeight DESC

     DELETE FROM WeightsScoreTable
     WHERE ANBFWeight
     IN (SELECT MAX(ANBFWeight)
     FROM WeightsScoreTable)

     INSERT INTO AggScore (ANFName, ANLName, BNBoatName, ANCity, ANState, ANAggWeight, BNBoatNumber)
     SELECT ANFName, ANLName, BNBoatName, ANCity, ANState, ANAggWeight, BNBoatNumber
     FWHERE (ANBFWeight = (SELECT MAX(ANAggWeight) AS Expr2
     FROM WeightsScoreTable))
     ORDER BY ANAggWeight DESC

     DELETE FROM WeightsScoreTable
     WHERE ANAggWeight
     IN (SELECT MAX(ANAggWeight)
     FROM WeightsScoreTable)

   SET @LoopValue = (@LoopValue - 1)
END