本文介绍了如何从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 197i需要查询才能返回值
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 197i 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 197thanks 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.RIDWITH DEDUPE AS ( SELECT * , ROW_NUMBER() OVER ( PARTITION BY TaskId ORDER BY TaskId ) AS OCCURENCE FROM table_name ) SELECT * FROM DEDUPE WHERE OCCURENCE = 1There'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更多推荐
如何从SQL获得独特的ros
发布评论