SQL：根据第一列取最多两列

I have three columns being returned Column one is a system generated "ID" column two is the warehouse and the third column is the total that warehouse shows on that ID. There are duplicate ID's Im trying to figure out how to take the max count of the Warehouse and get the greatest warehouse on that ID for example:

我有三列被返回第一列是系统生成的“ID”列，第二列是仓库，第三列是仓库在该ID上显示的总和。有重复的ID我试图弄清楚如何获取仓库的最大数量并获得该ID上最大的仓库，例如：

id  WH  Count   
TRL0520841  6   2   Takes the greatest WH based on the greatest QTY 
TRL0520841  2   2   
TRL0520841  5   1   
TRL0525482  6   15  Takes the greatest WH based on the greatest QTY
TRL0525482  2   9   

in other words TRL0520841 would have a result of 6 and TRL0525482 would have a result of 6

换句话说，TRL0520841的结果为6，而TRL0525482的结果为6

    select *
from (select ar2.trlr_id,
            to_number(ar2.area) areanum,
            ar2.con
       from (select ar1.trlr_id,
                    ar1.area,
                    count(ar1.area) con
               from (SELECT t.trlr_id,
                            pm.prtnum,
                            case when pm.prtfam like 'WH%' then substr(pm.prtfam, 3, 1)
                                 else '6'
                            end area
                       FROM rcvtrk rt,
                            trlr t,
                            rcvlin rl,
                            prtftp pf,
                            prtftp_dtl pfd,
                            prtmst pm
                      where t.trlr_id = rt.trlr_id
                        and t.trlr_stat not in ('D', 'C')
                        and rl.trknum = rt.trknum
                        and rl.prtnum = pf.prtnum
                        and pfd.prtnum = pf.prtnum
                        and pfd.ftpcod = pf.ftpcod
                        and pf.defftp_flg = '1'
                        and pfd.uomcod = 'UL'
                        and pfd.wh_id = 'CP01'
                        AND pf.wh_id = 'CP01'
                        and pf.prtnum = pm.prtnum
                        and t.yard_loc is not null
                        and t.trlr_cod = 'RCV'
                        and t.yard_loc_wh_id = 'CP01'
                      group by t.trlr_id,
                            pm.prtnum,
                            pm.prtfam) ar1
              group by ar1.trlr_id,
                    ar1.area) ar2) ar3;

2 个解决方案

#1

If I understand the question correctly you can use ROW_NUMBER() to determine which of the rows you wish to retain in the final result. Note you may want to include other columns in the ORDER BY of that calculation as tie breakers.

如果我正确理解了这个问题，您可以使用ROW_NUMBER（）来确定您希望在最终结果中保留哪些行。请注意，您可能希望将该计算的ORDER BY中的其他列包含为断路器。

SELECT
      ar3.trlr_id
    , ar3.areanum
    , ar3.con
FROM (
      SELECT
            ar2.trlr_id
          , ar2.areanum
          , ar2.con
          , ROW_NUMBER() OVER(PARTITION BY ar2.trlr_id ORDER BY ar2.areanum, ar2.con DESC) as rn
      FROM (
            SELECT
                  ar1.trlr_id
                , ar1.areanum
                , COUNT(ar1.areanum) con
            FROM (
                  SELECT
                        t.trlr_id
                      , pm.prtnum
                      , to_number(CASE
                              WHEN pm.prtfam LIKE 'WH%' THEN substr(pm.prtfam, 3, 1)
                              ELSE '6'
                        END) areanum
                  FROM rcvtrk rt
                     , trlr t
                     , rcvlin rl
                     , prtftp pf
                     , prtftp_dtl pfd
                     , prtmst pm
                  WHERE t.trlr_id = rt.trlr_id
                        AND t.trlr_stat NOT IN ('D', 'C')
                        AND rl.trknum = rt.trknum
                        AND rl.prtnum = pf.prtnum
                        AND pfd.prtnum = pf.prtnum
                        AND pfd.ftpcod = pf.ftpcod
                        AND pf.defftp_flg = '1'
                        AND pfd.uomcod = 'UL'
                        AND pfd.wh_id = 'CP01'
                        AND pf.wh_id = 'CP01'
                        AND pf.prtnum = pm.prtnum
                        AND t.yard_loc IS NOT NULL
                        AND t.trlr_cod = 'RCV'
                        AND t.yard_loc_wh_id = 'CP01'
                  GROUP BY
                        t.trlr_id
                      , pm.prtnum
                      , pm.prtfam
                  ) ar1
            GROUP BY
                  ar1.trlr_id
                , ar1.areanum
            ) ar2
      ) ar3
WHERE ar3.rn = 1
;

By the way: It was a very long time ago that ANSI join syntax was adopted. It really is time to stop using the where clause for all joins. Like this (untested):

顺便说一下：很久以前就采用了ANSI连接语法。现在是时候停止对所有连接使用where子句了。像这样（未经测试）：

SELECT
      *
FROM rcvtrk rt
      INNER JOIN trlr t ON rt.trlr_id = t.trlr_id
      INNER JOIN rcvlin rl ON rt.trknum = rl.trknum
      INNER JOIN prtftp pf ON rl.prtnum = pf.prtnum
      INNER JOIN prtftp_dtl pfd ON pf.prtnum = pfd.prtnum
                  AND pf.ftpcod = pfd.ftpcod
      INNER JOIN prtmst pm ON pf.prtnum = pm.prtnum
WHERE t.trlr_stat NOT IN ('D', 'C')
      AND t.yard_loc IS NOT NULL
      AND t.trlr_cod = 'RCV'
      AND t.yard_loc_wh_id = 'CP01'
      AND pf.defftp_flg = '1'
      AND pf.wh_id = 'CP01'
      AND pfd.uomcod = 'UL'
      AND pfd.wh_id = 'CP01'

#2

Assuming your column names are 'id', 'WH' and 'Count', and that the table name is 'dbo.Warehouse', the query you need is:

假设您的列名称为“id”，“WH”和“Count”，并且表名为“dbo.Warehouse”，则您需要的查询是：

SELECT Warehouse.id, WH, Count
FROM dbo.Warehouse as Warehouse
INNER JOIN
    (SELECT id, MAX(Count) AS MaxCount
    FROM dbo.Warehouse
    GROUP BY id) as groupedWH 
ON Warehouse.id = groupedWH.id
AND Count = groupedWH.MaxCount

For IDs with duplicate maximums, this will return all such rows (e.g. for TRL0520841 in your example both rows with count = 2 will be returned); you'll need to decide how to handle that.

对于具有重复最大值的ID，这将返回所有这些行（例如，对于示例中的TRL0520841，将返回count = 2的两行）;你需要决定如何处理它。