要更新hm_em_contracts 表每一个人的最早的一份合同 把serialNO=C.code+'01' 其中 (select min(enddate) enddate,em_id,max(convert(nvarchar(50),id)) ID from hm_em_contracts C group by em_id) T 是查出每个人的最早一份合同 下面是我的实现办法。后来我发现了问题,就是开帖提的问题。update hm_em_contracts set serialNO=C.code+'01' from (select min(enddate) enddate,em_id,max(convert(nvarchar(50),id)) ID from hm_em_contracts C group by em_id) T inner join hm_employees C on C.em_id=T.em_id where hm_em_contracts.id=T.id
肯定不是你想要的了,min(C.id)) ID 是在结果中找最小的id,min(enddate) 是在所有结果中找最小的enddate,是打乱了找的,并不是一行记录,如果想找enddate最小的ID 的话 这样:select distinct a.id,b.* from hm_em_contracts a inner join ( select C.em_id, min(enddate) enddate from hm_em_contracts C where C.em_id='111222' group by C.em_id) b on a.em_id=b.em_id and a.enddate=b.enddate
update hm_em_contracts set serialNO=C.code+'01' from (select min(enddate) enddate,em_id from hm_em_contracts C group by em_id) T inner join hm_employees C on C.em_id=T.em_id and c.enddate=t.enddate where hm_em_contracts.id=C.id
update hm_em_contracts set serialNO='01' from ((select C.em_id, min(enddate) enddate from hm_em_contracts C group by C.em_id) b inner join hm_em_contracts a on a.em_id=b.em_id and a.enddate=b.enddate) d where d.em_id is not null这样写有错吗? d 符近有错误 提示??
d 前面 的括号里面没有 select 不是一个结果集,所以不能加别名 这样试试 update hm_em_contracts set serialNO='01' from (select C.em_id, min(enddate) enddate from hm_em_contracts C group by C.em_id) b inner join hm_em_contracts a on a.em_id=b.em_id and a.enddate=b.enddate where a.em_id is not null不是要找 ID嘛? 最后一句也可以用 where a.id is not null
from hm_em_contracts C group by em_id) T 是查出每个人的最早一份合同
下面是我的实现办法。后来我发现了问题,就是开帖提的问题。update hm_em_contracts set serialNO=C.code+'01'
from
(select min(enddate) enddate,em_id,max(convert(nvarchar(50),id)) ID
from hm_em_contracts C group by em_id) T
inner join hm_employees C on C.em_id=T.em_id
where hm_em_contracts.id=T.id
from hm_em_contracts a
inner join (
select C.em_id, min(enddate) enddate from hm_em_contracts C where C.em_id='111222'
group by C.em_id) b
on a.em_id=b.em_id and a.enddate=b.enddate
from
(select min(enddate) enddate,em_id
from hm_em_contracts C group by em_id) T
inner join hm_employees C on C.em_id=T.em_id and c.enddate=t.enddate
where hm_em_contracts.id=C.id
from
((select C.em_id, min(enddate) enddate
from hm_em_contracts C group by C.em_id) b
inner join hm_em_contracts a
on a.em_id=b.em_id and a.enddate=b.enddate) d
where d.em_id is not null这样写有错吗? d 符近有错误 提示??
这样试试
update hm_em_contracts set serialNO='01'
from
(select C.em_id, min(enddate) enddate
from hm_em_contracts C group by C.em_id) b
inner join hm_em_contracts a
on a.em_id=b.em_id and a.enddate=b.enddate
where a.em_id is not null不是要找 ID嘛? 最后一句也可以用 where a.id is not null