SQL:根据第一列取两列的最大值
提问于 2016-01-10 14:02:54
我有三列正在返回，第一列是系统生成的" 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 换句话说，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
Stack Overflow用户
回答已采纳
发布于 2016-01-10 15:24:07
如果我正确理解这个问题，您可以使用ROW_NUMBER()来确定您希望在最终结果中保留哪些行。注意，您可能希望按此计算顺序包括其他列作为领带断线。
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
;顺便说一句: 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'Stack Overflow用户
发布于 2016-01-10 15:28:53
假设您的列名是'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对于具有重复最大值的in，这将返回所有这样的行(例如，对于示例中的TRL0520841，将返回计数=2的两行)；您需要决定如何处理这些行。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/34711299
复制相关文章
点击加载更多
腾讯云开发者
