SELECT * FROM ( select T.*,ROW_NUMBER()OVER(PARTITION BY "cInvCode" ORDER BY "dEnd" DESC,"cAcc_Id" DESC) RN from Temp_YY_INVENTORY T )WHERE RN=1 类似需求使用分析函数处理典型的去重处理需求 给你介绍几种去重的方式,你可以仿照实现下,最后一种效率应该是最高的,但是写起来比较麻烦 下面的语句实现的都是同一个id下取日期最大的记录 select ID,日期,其他字段 from ( select ID,日期,其他字段,row_number() over(partition by ID order by 日期 DESC) rn from T )where rn=1select * from T T1 WHERE NOT EXISTS(SELECT 1 FROM T WHERE ID=T1.ID AND 日期>T1.日期)
select * from T WHERE (ID,日期) IN (SELECT ID,MAX(日期) FROM T GROUP BY ID)
SELECT ID,MAX(日期), MAX(字段1)KEEP(DENSE_RANK LAST ORDER BY 日期) MAX(字段2)KEEP(DENSE_RANK LAST ORDER BY 日期) ……--其他字段同上 FROM T GROUP BY ID
根据版主的方法,我测试了一下: SELECT * FROM ( select T.*,ROW_NUMBER()OVER(PARTITION BY "cInvCode" ORDER BY "cAcc_Id" DESC, dEnd DESC) RN from Temp_YY_INVENTORY T )WHERE RN=1;SELECT * FROM Temp_YY_INVENTORY T WHERE ("cInvCode","cAcc_Id",dEnd) IN (SELECT "cInvCode",MAX("cAcc_Id"),MAX(dEnd) FROM Temp_YY_INVENTORY T GROUP BY "cInvCode");SELECT "cInvCode",MAX("cAcc_Id"),MAX(dEnd), MAX("cInvName")KEEP(DENSE_RANK LAST ORDER BY "cAcc_Id" DESC, dEnd DESC), MAX(COST)KEEP(DENSE_RANK LAST ORDER BY "cAcc_Id" DESC, dEnd DESC) FROM Temp_YY_INVENTORY T GROUP BY "cInvCode"第一句和第三句会在13W条数据中提取每个cInvCode排序排第一的记录,取到了13624条, 第二句话的逻辑和我之前的NOT EXISTS一样,从13W条记录中取到了12679条, 第二句话没有取到的那945条属于不存在cAcc_Id和dEnd都处于最大的记录。版主的回复完美的解决了我的问题,非常感谢
只是count,也用不到exists啊
"id" ,"cAcc_Id" ,"iYear" ,"iPeriod" ,”dEnd“,"cInvCode" ,"cInvName" ,"fcost"
id,账套号,年份,月份,日期,物资编码,物资名称,成本我要取这一年所有物料在最大账套号中的最新成本,
代码中用COUNT(*)只是测试用的,最后还是要指定字段名的。
select T.*,ROW_NUMBER()OVER(PARTITION BY "cInvCode" ORDER BY "dEnd" DESC,"cAcc_Id" DESC) RN from Temp_YY_INVENTORY T
)WHERE RN=1
类似需求使用分析函数处理典型的去重处理需求
给你介绍几种去重的方式,你可以仿照实现下,最后一种效率应该是最高的,但是写起来比较麻烦
下面的语句实现的都是同一个id下取日期最大的记录
select ID,日期,其他字段
from (
select ID,日期,其他字段,row_number() over(partition by ID order by 日期 DESC) rn
from T
)where rn=1select * from T T1
WHERE NOT EXISTS(SELECT 1 FROM T WHERE ID=T1.ID AND 日期>T1.日期)
select * from T
WHERE (ID,日期) IN (SELECT ID,MAX(日期) FROM T GROUP BY ID)
SELECT ID,MAX(日期),
MAX(字段1)KEEP(DENSE_RANK LAST ORDER BY 日期)
MAX(字段2)KEEP(DENSE_RANK LAST ORDER BY 日期)
……--其他字段同上
FROM T GROUP BY ID
SELECT * FROM (
select T.*,ROW_NUMBER()OVER(PARTITION BY "cInvCode" ORDER BY "cAcc_Id" DESC, dEnd DESC) RN from Temp_YY_INVENTORY T )WHERE RN=1;SELECT * FROM Temp_YY_INVENTORY T
WHERE ("cInvCode","cAcc_Id",dEnd) IN (SELECT "cInvCode",MAX("cAcc_Id"),MAX(dEnd) FROM Temp_YY_INVENTORY T GROUP BY "cInvCode");SELECT "cInvCode",MAX("cAcc_Id"),MAX(dEnd),
MAX("cInvName")KEEP(DENSE_RANK LAST ORDER BY "cAcc_Id" DESC, dEnd DESC),
MAX(COST)KEEP(DENSE_RANK LAST ORDER BY "cAcc_Id" DESC, dEnd DESC)
FROM Temp_YY_INVENTORY T GROUP BY "cInvCode"第一句和第三句会在13W条数据中提取每个cInvCode排序排第一的记录,取到了13624条,
第二句话的逻辑和我之前的NOT EXISTS一样,从13W条记录中取到了12679条,
第二句话没有取到的那945条属于不存在cAcc_Id和dEnd都处于最大的记录。版主的回复完美的解决了我的问题,非常感谢