sql—返回最大值和相应的fk(id)group by year的查询，该查询来自另一个具有group by fk(id)和year平均值的select

j8ag8udp 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(311)

我有一个原始表，其中包含id（pk ai），farm（或herd），birthdate，nm（一些指示符）。
我想得到每个现有年份的最大nm平均值和相应的农场面积。
你做了什么？
从表1中，我得到了每个farm\u fk组按年份的nm平均值，它返回类似于这样的结果，我添加了内部select查询（这里我还添加了order by，以便于阅读）

SELECT 
    YEAR(FA_BDATE) AS BYEAR, FARM_FK AS HERD, AVG(NET_MERIT) AS NM
FROM 
    __FARM_ANALYTICS_TBL
GROUP BY 
    FARM_FK, YEAR(FA_BDATE)
ORDER BY
    HERD, BYEAR

输出：

2006    10045   -181.553596496582
2007    10045   -252.123779296875
2008    10045   -97.3785720467567
2009    10045   -46.0490081493671
2010    10045   -2.05634630137476
2011    10045   33.5371181927621
2012    10045   74.3363304953117
2013    10045   124.057859333072
2014    10045   177.423686878542
2015    10045   188.846870697982
2016    10045   241.421725696403
2017    10045   318.593410228532
2018    10045   443.3983160078
2019    10045   483.12452005201
2010    10046   -99.2454333305359
2011    10046   42.3376306125096
2012    10046   71.8295436098769
2013    10046   90.7501822723432
2014    10046   133.500797046962
2015    10046   135.329324710063
2016    10046   223.211583482458
2017    10046   261.208083089193
2018    10046   409.256013000512
2019    10046   468.574509707364
2010    10047   -97.1618871688843
2011    10047   -1.06820678710938
2012    10047   20.5999549464746
2013    10047   5.93872594833374
2014    10047   134.559080774134
2015    10047   221.275759446621
2016    10047   203.30495039622
2017    10047   280.823856965995
2018    10047   304.591577597225
2019    10047   399.748709002112

其次，我试着提取每年和相应牛群的最大值（平均值（nm）），我试着这样做

SELECT 
    BYEAR, HERD, MAX(NM) AS MAX_NM
FROM
    (SELECT 
         YEAR(FA_BDATE) AS BYEAR, FARM_FK HERD, AVG(NET_MERIT) AS NM
     FROM 
         __FARM_ANALYTICS_TBL
     GROUP BY 
         FARM_FK, YEAR(FA_BDATE)) AS AVGNM
GROUP BY 
    BYEAR
ORDER BY 
    BYEAR

因为我认为按我需要的来分组是错误的，而且每年的选择都有很多。在上面的查询中没有从众列，我得到的结果是正确的，但是没有相应的从众id

2005    -258.71630859375
2006    -150.947634379069
2007    -127.1032270704
2008    -5.74109745025635
2009    -19.5938364664714
2010    -2.05634630137476
2011    64.6482777208895
2012    109.018188629743
2013    260.781127929688
2014    219.82367308171
2015    244.199884947291
2016    296.168976219786
2017    391.202879227419
2018    460.009900628413
2019    493.26334651952

sql sql-server Database

来源：https://stackoverflow.com/questions/63328564/query-that-returns-maximum-and-corresponding-fkid-group-by-year-which-comes-fr

1条答案

按热度按时间

ukqbszuj1#

我想你只是想用 ROW_NUMBER() :

SELECT hy.*
FROM (SELECT YEAR(FA_BDATE) AS BYEAR, FARM_FK AS HERD, AVG(NET_MERIT) AS NM,
             ROW_NUMBER() OVER (PARTITION BY YEAR(FS_BDATE) ORDER BY AVG(NET_MERIT) DESC) as seqnum
      FROM __FARM_ANALYTICS_TBL
      GROUP BY FARM_FK, YEAR(FA_BDATE)
     ) hy
WHERE seqnum = 1
ORDER BY BYEAR;

注意，如果是ties，它只返回其中一个ties。如果你想要所有的，使用 RANK() 而不是 ROW_NUMBER() .

赞(0）回复(0）举报 2021-07-24

我来回答

sql—返回最大值和相应的fk(id)group by year的查询，该查询来自另一个具有group by fk(id)和year平均值的select

1条答案

相关问题

热门标签

最新问答