tba
id name group work
1 张三 1 1
2 李四 1 2
3 王五 1 3
4 赵六 2 1
tb2
id name group work
1 张三 2 3
1 张三 2 4
1 张三 2 5
1 张三 2 6
1 张三 2 7
2 李四 3 3
......
结果
tba
id name group work
1 张三 2 7
2 李四 3 3
3 王五 1 3
4 赵六 2 1
根据tbb中最后一条记录更新tba中对应id和name的 gruop 和work
------解决方案--------------------
----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-06-09 10:45:32
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[tbB]
if object_id('[tbB]') is not null drop table [tbB]
go
create table [tbB]([id] int,[name] nvarchar(4),[group] int,[work] int)
insert [tbB]
select 1,N'张三',2,3 union all
select 1,N'张三',2,4 union all
select 1,N'张三',2,5 union all
select 1,N'张三',2,6 union all
select 1,N'张三',2,7 union all
select 2,N'李四',3,3
--> 测试数据[tba]
if object_id('[tba]') is not null drop table [tba]
go
create table [tba]([id] int,[name] nvarchar(4),[group] int,[work] int)
insert [tba]
select 1,N'张三',1,1 union all
select 2,N'李四',1,2 union all
select 3,N'王五',1,3 union all
select 4,N'赵六',2,1
--------------生成数据--------------------------
select * from [tba]
UPDATE TBA
SET TBA.NAME=TBB.NAME ,TBA.[GROUP]=TBB.[GROUP],TBA.WORK=TBB.WORK
FROM (SELECT ID,NAME,[GROUP],WORK FROM (SELECT *,RN=ROW_NUMBER()OVER(PARTITION BY ID ORDER BY WORK DESC) FROM TBB)A WHERE RN=1 )TBB
WHERE TBA.ID=TBB.ID
select * from [tba]
----------------结果----------------------------
/*
id name group work
----------- ---- ----------- -----------
1 张三 1 1
2 李四 1 2
3 王五 1 3
4 赵六 2 1