如何从SQL获得独特的ros

本文介绍了如何从SQL获得独特的ros的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有这样的价格。

TaskId InternalId（无列名）UploadId RequestId CreatedDate ProcessStatusMappingID 959 959 0 183 959636241330158000000 3月3日197 959 959 0 183 959636241463837000000 3/3/2017 197 960 960 0 183 960636241330205000000 3/3/2017 197 960 960 0 183 960636241463912000000 3 / 3/2017 197 961 961 0 183 961636241330219000000 3/3/2017 197 983 983 0 194 983636244258262000000 3/6/2017 197 983 983 0 194 983636244258532000000 3/6/2017 197 984 984 0 194 984636244258309000000 3/6/2017 197 984 984 0 194 984636244258553000000 3/6/2017 197 985 985 0 194 985636244258327000000 3/6/2017 197

i需要查询才能返回值

TaskId InternalId（No column name）UploadId RequestId CreatedDate ProcessStatusMappingID 959 959 0 183 959636241463837000000 3/3/2017 197 960 960 0 183 960636241 463912000000 3/3/2017 197 961 961 0 183 961636241330219000000 3/3/2017 197 983 983 0 194 983636244258532000000 3/6/2017 197 984 984 0 194 984636244258553000000 3/6 / 2017 197 985 985 0 194 985636244258327000000 3/6/2017 197

感谢advnace。 我尝试了什么： 尝试使用不同但不起作用。

SELECT * FROM TABLE T INNER JOIN （ SELECT TASKID， MAX（REQUESTID）RID FROM TABLE T ）TMP ON T.TASKID = TMP.TASKID AND T.REQUESTID = TMP.RID

WITH DEDUPE AS（ SELECT * ，ROW_NUMBER（）OVER（按TaskId划分的TaskId ORDER）发生 FROM table_name ） SELECT * FROM DEDUPE WHERE OCCURENCE = 1

没有重复项。所有行都是独特的！所有您想要达到的目标是获得最高 RequestId 的日期。 我建议使用 MAX（）聚合函数和OVER（）子句 [ ^ ]：

SELECT DISTINCT TaskId，InternalId， NoColumnName，UploadId， MAX（RequestId） OVER （ PARTITION BY TaskId ORDER BY CreatedDate） AS MaxRequestId，CreatedDate，ProcessStatusMappingID FROM YourTableName

I have a vales like this.

TaskId InternalId (No column name) UploadId RequestId CreatedDate ProcessStatusMappingID 959 959 0 183 959636241330158000000 3/3/2017 197 959 959 0 183 959636241463837000000 3/3/2017 197 960 960 0 183 960636241330205000000 3/3/2017 197 960 960 0 183 960636241463912000000 3/3/2017 197 961 961 0 183 961636241330219000000 3/3/2017 197 983 983 0 194 983636244258262000000 3/6/2017 197 983 983 0 194 983636244258532000000 3/6/2017 197 984 984 0 194 984636244258309000000 3/6/2017 197 984 984 0 194 984636244258553000000 3/6/2017 197 985 985 0 194 985636244258327000000 3/6/2017 197

i require a query to return the value has

TaskId InternalId (No column name) UploadId RequestId CreatedDate ProcessStatusMappingID 959 959 0 183 959636241463837000000 3/3/2017 197 960 960 0 183 960636241463912000000 3/3/2017 197 961 961 0 183 961636241330219000000 3/3/2017 197 983 983 0 194 983636244258532000000 3/6/2017 197 984 984 0 194 984636244258553000000 3/6/2017 197 985 985 0 194 985636244258327000000 3/6/2017 197

thanks in advnace. What I have tried: tried with distinct but not working.

解决方案

Try this,

SELECT * FROM TABLE T INNER JOIN ( SELECT TASKID, MAX(REQUESTID) RID FROM TABLE T ) TMP ON T.TASKID = TMP.TASKID AND T.REQUESTID = TMP.RID

WITH DEDUPE AS ( SELECT * , ROW_NUMBER() OVER ( PARTITION BY TaskId ORDER BY TaskId ) AS OCCURENCE FROM table_name ) SELECT * FROM DEDUPE WHERE OCCURENCE = 1

There's no duplicates. All rows are unique! All what you want to achieve, is to get date with the highest RequestId. I'd suggest to use MAX() aggregate function with OVER() clause[^]:

SELECT DISTINCT TaskId, InternalId, NoColumnName, UploadId, MAX(RequestId) OVER( PARTITION BY TaskId ORDER BY CreatedDate) AS MaxRequestId, CreatedDate, ProcessStatusMappingID FROM YourTableName