一个MSSQL更新表问题
declare @t1 table(id int,number int)
insert into @t1(id,number) values(1,2)
insert into @t1(id,number) values(2,2)
declare @t2 table(id int,number int)
insert into @t2(id,number) values(1,1)
insert into @t2(id,number) values(1,2)
insert into @t2(id,number) values(1,3)
insert into @t2(id,number) values(2,1)
insert into @t2(id,number) values(2,3)
更新表@t1最后变成
id number
1 1
2 1
表@t1中有一条记录跟@t2中的多条记录相对应，需要找出@t2中的min（number）
两边关联条件是@t1.id=@t2.id
oracle的方法比较好些，mssql的写不出，请教各位大牛们，这个该咋写
[解决办法]
select * from t1 where number = (select min(number) from t2 where t1.id = t2.id)
[解决办法]
UPDATE A SET number = (select min(number) from @t2 where id = A.id)
FROM @T1 A
--OR
UPDATE A SET number = B.number
FROM @T1 A CROSS APPLY (
SELECT TOP 1 number
FROM @T2
WHERE ID = A.ID
ORDER BY number
) AS B
