文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
首页
学习
活动
专区
圈层
工具
MCP广场
返回腾讯云官网
社区首页 >问答首页 >SQL选择最新项并删除重复项

问SQL选择最新项并删除重复项
EN

Stack Overflow用户
提问于 2021-06-16 20:35:44
回答 3查看 66关注 0票数 0

你好，我这里有一个问题：

代码语言：javascript
运行
复制
SELECT Erp.QuoteDtl.Company
 ,Erp.OrderHed.ShipToNum
 ,Erp.OrderHed.PONum
 ,Erp.OrderDtl.PartNum
 ,Erp.OrderHed.OrderNum
 ,Erp.OrderHed.OrderDate
 ,Erp.QuoteDtl.QuoteNum
 ,Erp.QuoteHed.DateQuoted
 ,Erp.QuoteHed.ExpirationDate
 ,Erp.Customer.CustID
FROM Erp.OrderHed
INNER JOIN Erp.Customer ON Erp.OrderHed.Company = Erp.Customer.Company
 AND Erp.OrderHed.CustNum = Erp.Customer.CustNum
INNER JOIN Erp.OrderDtl ON Erp.OrderHed.Company = Erp.OrderDtl.Company
 AND Erp.OrderHed.OrderNum = Erp.OrderDtl.OrderNum
 AND Erp.OrderHed.CustNum = Erp.OrderDtl.CustNum
INNER JOIN Erp.QuoteDtl ON Erp.OrderHed.Company = Erp.QuoteDtl.Company
 AND Erp.OrderDtl.PartNum = Erp.QuoteDtl.PartNum
INNER JOIN Erp.QuoteHed ON Erp.OrderHed.Company = Erp.QuoteHed.Company
INNER JOIN Erp.Part ON Erp.OrderHed.Company = Erp.Part.Company
 AND Erp.OrderDtl.PartNum = Erp.Part.PartNum
WHERE (Erp.OrderHed.OrderDate >= '6/15/2021')
 AND (Erp.QuoteHed.DateQuoted <= '2021-06-15')
 AND (Erp.QuoteHed.ExpirationDate >= '2021-06-15')
 AND (Erp.QuoteDtl.Company = N'CYT')
GROUP BY OrderDtl.PartNum
 ,Erp.QuoteDtl.Company
 ,Erp.OrderHed.ShipToNum
 ,Erp.OrderHed.PONum
 ,Erp.OrderDtl.PartNum
 ,Erp.OrderHed.OrderNum
 ,Erp.OrderHed.OrderDate
 ,Erp.QuoteDtl.QuoteNum
 ,Erp.QuoteHed.DateQuoted
 ,Erp.QuoteHed.ExpirationDate
 ,Erp.Customer.CustID
ORDER BY QuoteNum

目前仍在尝试构建它，这是我得到的结果

​

​

因此，我的目标是为最新的DateQuoted仅选择1个PartNum

因此，在本例中，它将仅显示PartNum的1行

期望的结果将是

代码语言：javascript
运行
复制
CYT | NKP | 188719 | 26251-RJ2-0000 | 207504 | 11373 | 2021-06-01
CYT | BAC | 188719 | 18130-TLA-0000 | 207509 | 12250 | 2021-06-01

然后在具有相同概念的那个下添加一个新的部分。

sql
EN

回答 3

Stack Overflow用户

发布于 2021-06-16 21:06:05

要获得每个cust_id和零件编号的单行，可以使用row_number()排名窗口函数，如下所示：

代码语言：javascript
运行
复制
  WITH CTE AS
    (
      SELECT Erp.QuoteDtl.Company, Erp.OrderHed.ShipToNum, Erp.OrderHed.PONum, Erp.OrderDtl.PartNum, Erp.OrderHed.OrderNum, Erp.OrderHed.OrderDate, Erp.QuoteDtl.QuoteNum, Erp.QuoteHed.DateQuoted, Erp.QuoteHed.ExpirationDate,
                             Erp.Customer.CustID,ROW_NUMBER()OVER(PARTITION BY Erp.Customer.CustID, Erp.OrderDtl.PartNum ORDER BY DateQuoted desc )RNK
      FROM            Erp.OrderHed INNER JOIN
                             Erp.Customer ON Erp.OrderHed.Company = Erp.Customer.Company AND Erp.OrderHed.CustNum = Erp.Customer.CustNum INNER JOIN
                             Erp.OrderDtl ON Erp.OrderHed.Company = Erp.OrderDtl.Company AND Erp.OrderHed.OrderNum = Erp.OrderDtl.OrderNum AND Erp.OrderHed.CustNum = Erp.OrderDtl.CustNum INNER JOIN
                             Erp.QuoteDtl ON Erp.OrderHed.Company = Erp.QuoteDtl.Company AND Erp.OrderDtl.PartNum = Erp.QuoteDtl.PartNum INNER JOIN
                             Erp.QuoteHed ON Erp.OrderHed.Company = Erp.QuoteHed.Company INNER JOIN
                             Erp.Part ON Erp.OrderHed.Company = Erp.Part.Company AND Erp.OrderDtl.PartNum = Erp.Part.PartNum
      WHERE        (Erp.OrderHed.OrderDate >= '6/15/2021') AND (Erp.QuoteHed.DateQuoted <= '2021-06-15') AND (Erp.QuoteHed.ExpirationDate >= '2021-06-15') AND (Erp.QuoteDtl.Company = N'CYT')
      Group by OrderDtl.PartNum, Erp.QuoteDtl.Company, Erp.OrderHed.ShipToNum, Erp.OrderHed.PONum, Erp.OrderDtl.PartNum, Erp.OrderHed.OrderNum, Erp.OrderHed.OrderDate, Erp.QuoteDtl.QuoteNum, Erp.QuoteHed.DateQuoted, Erp.QuoteHed.ExpirationDate, Erp.Customer.CustID 
    )
    SELECT Erp.QuoteDtl.Company, Erp.OrderHed.ShipToNum, Erp.OrderHed.PONum, Erp.OrderDtl.PartNum, Erp.OrderHed.OrderNum, Erp.OrderHed.OrderDate, Erp.QuoteDtl.QuoteNum, Erp.QuoteHed.DateQuoted, Erp.QuoteHed.ExpirationDate, Erp.Customer.CustID
    FROM CTE
    WHERE RNK=1

如果您使用的是sql order by DateQuoted desc，则可以使用top 1 server。

代码语言：javascript
运行
复制
SELECT TOP 1 Erp.QuoteDtl.Company
 ,Erp.OrderHed.ShipToNum
 ,Erp.OrderHed.PONum
 ,Erp.OrderDtl.PartNum
 ,Erp.OrderHed.OrderNum
 ,Erp.OrderHed.OrderDate
 ,Erp.QuoteDtl.QuoteNum
 ,Erp.QuoteHed.DateQuoted
 ,Erp.QuoteHed.ExpirationDate
 ,Erp.Customer.CustID
FROM Erp.OrderHed
INNER JOIN Erp.Customer ON Erp.OrderHed.Company = Erp.Customer.Company
 AND Erp.OrderHed.CustNum = Erp.Customer.CustNum
INNER JOIN Erp.OrderDtl ON Erp.OrderHed.Company = Erp.OrderDtl.Company
 AND Erp.OrderHed.OrderNum = Erp.OrderDtl.OrderNum
 AND Erp.OrderHed.CustNum = Erp.OrderDtl.CustNum
INNER JOIN Erp.QuoteDtl ON Erp.OrderHed.Company = Erp.QuoteDtl.Company
 AND Erp.OrderDtl.PartNum = Erp.QuoteDtl.PartNum
INNER JOIN Erp.QuoteHed ON Erp.OrderHed.Company = Erp.QuoteHed.Company
INNER JOIN Erp.Part ON Erp.OrderHed.Company = Erp.Part.Company
 AND Erp.OrderDtl.PartNum = Erp.Part.PartNum
WHERE (Erp.OrderHed.OrderDate >= '6/15/2021')
 AND (Erp.QuoteHed.DateQuoted <= '2021-06-15')
 AND (Erp.QuoteHed.ExpirationDate >= '2021-06-15')
 AND (Erp.QuoteDtl.Company = N'CYT')
GROUP BY OrderDtl.PartNum
 ,Erp.QuoteDtl.Company
 ,Erp.OrderHed.ShipToNum
 ,Erp.OrderHed.PONum
 ,Erp.OrderDtl.PartNum
 ,Erp.OrderHed.OrderNum
 ,Erp.OrderHed.OrderDate
 ,Erp.QuoteDtl.QuoteNum
 ,Erp.QuoteHed.DateQuoted
 ,Erp.QuoteHed.ExpirationDate
 ,Erp.Customer.CustID
ORDER BY DateQuoted DESC

如果您使用的是MySQL，那么它将是

代码语言：javascript
运行
复制
SELECT Erp.QuoteDtl.Company
 ,Erp.OrderHed.ShipToNum
 ,Erp.OrderHed.PONum
 ,Erp.OrderDtl.PartNum
 ,Erp.OrderHed.OrderNum
 ,Erp.OrderHed.OrderDate
 ,Erp.QuoteDtl.QuoteNum
 ,Erp.QuoteHed.DateQuoted
 ,Erp.QuoteHed.ExpirationDate
 ,Erp.Customer.CustID
FROM Erp.OrderHed
INNER JOIN Erp.Customer ON Erp.OrderHed.Company = Erp.Customer.Company
 AND Erp.OrderHed.CustNum = Erp.Customer.CustNum
INNER JOIN Erp.OrderDtl ON Erp.OrderHed.Company = Erp.OrderDtl.Company
 AND Erp.OrderHed.OrderNum = Erp.OrderDtl.OrderNum
 AND Erp.OrderHed.CustNum = Erp.OrderDtl.CustNum
INNER JOIN Erp.QuoteDtl ON Erp.OrderHed.Company = Erp.QuoteDtl.Company
 AND Erp.OrderDtl.PartNum = Erp.QuoteDtl.PartNum
INNER JOIN Erp.QuoteHed ON Erp.OrderHed.Company = Erp.QuoteHed.Company
INNER JOIN Erp.Part ON Erp.OrderHed.Company = Erp.Part.Company
 AND Erp.OrderDtl.PartNum = Erp.Part.PartNum
WHERE (Erp.OrderHed.OrderDate >= '6/15/2021')
 AND (Erp.QuoteHed.DateQuoted <= '2021-06-15')
 AND (Erp.QuoteHed.ExpirationDate >= '2021-06-15')
 AND (Erp.QuoteDtl.Company = N'CYT')
GROUP BY OrderDtl.PartNum
 ,Erp.QuoteDtl.Company
 ,Erp.OrderHed.ShipToNum
 ,Erp.OrderHed.PONum
 ,Erp.OrderDtl.PartNum
 ,Erp.OrderHed.OrderNum
 ,Erp.OrderHed.OrderDate
 ,Erp.QuoteDtl.QuoteNum
 ,Erp.QuoteHed.DateQuoted
 ,Erp.QuoteHed.ExpirationDate
 ,Erp.Customer.CustID
ORDER BY DateQuoted DESC Limit 1
票数 0
EN

Stack Overflow用户

发布于 2021-06-16 21:10:32

可以在DateQuoted上使用max()函数，将其从group by语句中删除。

票数 0
EN

Stack Overflow用户

发布于 2021-06-16 21:26:03

正如@fra所解释的:使用max应该是可行的

代码语言：javascript
运行
复制
SELECT Erp.QuoteDtl.Company
 ,Erp.OrderHed.ShipToNum
 ,Erp.OrderHed.PONum
 ,Erp.OrderDtl.PartNum
 ,Erp.OrderHed.OrderNum
 ,Erp.OrderHed.OrderDate
 ,Erp.QuoteDtl.QuoteNum
 ,max(Erp.QuoteHed.DateQuoted)
 ,Erp.QuoteHed.ExpirationDate
 ,Erp.Customer.CustID
FROM Erp.OrderHed
INNER JOIN Erp.Customer ON Erp.OrderHed.Company = Erp.Customer.Company
 AND Erp.OrderHed.CustNum = Erp.Customer.CustNum
INNER JOIN Erp.OrderDtl ON Erp.OrderHed.Company = Erp.OrderDtl.Company
 AND Erp.OrderHed.OrderNum = Erp.OrderDtl.OrderNum
 AND Erp.OrderHed.CustNum = Erp.OrderDtl.CustNum
INNER JOIN Erp.QuoteDtl ON Erp.OrderHed.Company = Erp.QuoteDtl.Company
 AND Erp.OrderDtl.PartNum = Erp.QuoteDtl.PartNum
INNER JOIN Erp.QuoteHed ON Erp.OrderHed.Company = Erp.QuoteHed.Company
INNER JOIN Erp.Part ON Erp.OrderHed.Company = Erp.Part.Company
 AND Erp.OrderDtl.PartNum = Erp.Part.PartNum
WHERE (Erp.OrderHed.OrderDate >= '6/15/2021')
 AND (Erp.QuoteHed.DateQuoted <= '2021-06-15')
 AND (Erp.QuoteHed.ExpirationDate >= '2021-06-15')
 AND (Erp.QuoteDtl.Company = N'CYT')
GROUP BY OrderDtl.PartNum
 ,Erp.QuoteDtl.Company
 ,Erp.OrderHed.ShipToNum
 ,Erp.OrderHed.PONum
 ,Erp.OrderDtl.PartNum
 ,Erp.OrderHed.OrderNum
 ,Erp.OrderHed.OrderDate
 ,Erp.QuoteDtl.QuoteNum
 ,Erp.QuoteHed.ExpirationDate
 ,Erp.Customer.CustID
ORDER BY QuoteNum
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/68002786

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2025 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 深公网安备号 44030502008569

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号 | 京公网安备号11010802020287

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档