更新数据集以添加计算字段

Question

我有一个数据集，如下所示：

SELECT jockey.jockey_skey
, raceresults.place 
FROM   jockey 
       INNER JOIN runnersandriders 
               ON jockey.jockey_skey = runnersandriders.jockey_skey 
       INNER JOIN horse 
               ON runnersandriders.horse_skey = horse.horse_skey 
       INNER JOIN raceresults 
               ON horse.horse_skey = raceresults.horse_skey 
GROUP  BY jockey.jockey_skey, 
          raceresults.place 
ORDER  BY jockey.jockey_skey 

这为我提供了：

Jockey_Skey    Place
1              01
1              04
2              03
2              04
3              02
3              02

我想要做的是计算一个骑师已经跑了多少场比赛，以及他们来过的地方的列表。

所以Jockey 1，参加了两次比赛，第一次和第四次，等等，但对于我的生活，我不记得怎么做了。

Answer 1

您可以使用类似下面的代码来获得一行中每个骑师的结果：

SELECT  jockey.jockey_skey,
        TotalRaces = COUNT(*),
        [1sts] = COUNT(CASE WHEN raceresults.place = '01' THEN 1 END),
        [2nds] = COUNT(CASE WHEN raceresults.place = '02' THEN 1 END),
        [3rds] = COUNT(CASE WHEN raceresults.place = '03' THEN 1 END),
        [4ths] = COUNT(CASE WHEN raceresults.place = '04' THEN 1 END),
        [5ths] = COUNT(CASE WHEN raceresults.place = '05' THEN 1 END),
        [6ths] = COUNT(CASE WHEN raceresults.place = '06' THEN 1 END),
        [7ths] = COUNT(CASE WHEN raceresults.place = '07' THEN 1 END),
        [8ths] = COUNT(CASE WHEN raceresults.place = '08' THEN 1 END),
        -- etc
        [NonRunner] = COUNT(CASE WHEN raceresults.place = 'NR' THEN 1 END),
        [Fell] = COUNT(CASE WHEN raceresults.place = 'F' THEN 1 END),
        [PulledUp] = COUNT(CASE WHEN raceresults.place = 'PU' THEN 1 END),
        [Unseated] = COUNT(CASE WHEN raceresults.place = 'U' THEN 1 END),
        [Refused] = COUNT(CASE WHEN raceresults.place = 'R' THEN 1 END),
        [BroughtDown] = COUNT(CASE WHEN raceresults.place = 'B' THEN 1 END)
FROM    jockey 
        INNER JOIN runnersandriders 
            ON jockey.jockey_skey = runnersandriders.jockey_skey 
        INNER JOIN horse 
            ON runnersandriders.horse_skey = horse.horse_skey 
        INNER JOIN raceresults 
            ON horse.horse_skey = raceresults.horse_skey 
GROUP  BY jockey.jockey_skey
ORDER  BY jockey.jockey_skey 

ALternatively您可以使用WITH ROLLUP来获得包含总计的附加行：

SELECT  jockey.jockey_skey,
        raceresults.place,
        [CountOfResult] = COUNT(*)
FROM    jockey 
        INNER JOIN runnersandriders 
            ON jockey.jockey_skey = runnersandriders.jockey_skey 
        INNER JOIN horse 
            ON runnersandriders.horse_skey = horse.horse_skey 
        INNER JOIN raceresults 
            ON horse.horse_skey = raceresults.horse_skey 
GROUP  BY jockey.jockey_skey, raceresults.place
WITH ROLLUP
ORDER  BY jockey.jockey_skey, raceresults.place;

其中NULL值表示总计