所以我有两个表,一个是RAWtable,另一个是MAINtable,我必须得到最新的groupID
存在多个记录(比较相同的名称,代码).例如,我在RAWtable上有这个：
存在多个记录(比较相同的名称,代码).例如,我在RAWtable上有这个：
id groupid name code 1 G09161405 Name1 Code1 2 G09161406 Name1 Code1
这两个记录应该被视为一个,并且只应返回此值：
id groupid name code 2 G09161406 Name1 Code1
此行是shiuld在主表中插入的唯一行.提供返回最新的GroupID(groupid是日期和时间的组合)
我试过这个,但它不起作用：
SELECT MAST.ID,MAST.code,MAST.name FROM RAWtable AS MAST INNER JOIN (SELECT code,name,grouid,id FROM RAWtable AS DUPT GROUP BY code,groupid,id HAVING COUNT(*) >= 2) DUPT ON DUPT.code =MAST.code and DUPT.name =MAST.name where dupt.groupid >mast.groupid
我怎样才能做到这一点？非常感谢.
解决方法
select R.id,R.groupid,R.name,R.code
from (select id,code,row_number() over(partition by name,code order by groupid desc) as rn
from RawTable
) as R
where R.rn = 1
或者如果你没有row_number()
select R1.id,R1.groupid,R1.name,R1.code
from RawTable as R1
inner join (
select name,max(groupid) as groupid
from RawTable
group by name,code
) as R2
on R1.name = R2.name and
R1.code = R2.code and
R1.groupid = R2.groupid
