(id,items,version)复合主键 status 状态 version 版本
当有状态为1的时候 去状态为1 的 如 dx001 xx001 这条记录
当有状态都为0 去version 最大的 如 dx002 xx002 这条记录
id name items version status
dx001 aaaa xx001 1 0
dx001 bbbb xx001 2 1 dx002 cccc xx002 1 0
dx002 dddd xx002 2 0
dx002 eeee xx002 3 0 dx003 ffff xx003 1 0
dx003 gggg xx003 2 0
dx003 hhhh xx003 3 1 ---------------得到的结果
id name items version status
dx001 bbbb xx001 2 1
dx002 eeee xx002 3 0
dx003 hhhh xx003 3 1
当有状态为1的时候 去状态为1 的 如 dx001 xx001 这条记录
当有状态都为0 去version 最大的 如 dx002 xx002 这条记录
id name items version status
dx001 aaaa xx001 1 0
dx001 bbbb xx001 2 1 dx002 cccc xx002 1 0
dx002 dddd xx002 2 0
dx002 eeee xx002 3 0 dx003 ffff xx003 1 0
dx003 gggg xx003 2 0
dx003 hhhh xx003 3 1 ---------------得到的结果
id name items version status
dx001 bbbb xx001 2 1
dx002 eeee xx002 3 0
dx003 hhhh xx003 3 1
from
(
select a.*,row_number() over(partition by id order by status desc,version desc) rn
from a
)
where rn =1
from table1
where status = 1
union all
select id, name, items, version, status
from table1
where status = 0
and version = (select max(version) from table1 where status = 0)
order by id
insert into @t
select 'dx001','aaaa','xx001','1','0' union all
select 'dx001','bbbb','xx001','2','1' union allselect 'dx002','cccc','xx002','1','0' union all
select 'dx002','dddd','xx002','2','0' union all
select 'dx002','eeee','xx002','3','0' union allselect 'dx003','ffff','xx003','1','0' union all
select 'dx003','gggg','xx003','2','0' union all
select 'dx003','hhhh','xx003','3','1' select a.id,a.name,a.items,a.version,a.status
from
(
select a.*,row_number() over(partition by id order by status desc,version desc) rn
from @t a
) a
where rn =1(8 行受影响)
id name items version status
-------------------- -------------------- -------------------- -------------------- --------------------
dx001 bbbb xx001 2 1
dx002 eeee xx002 3 0
dx003 hhhh xx003 3 1(3 行受影响)