求帮忙存储过程
表A
OID TID CreateTime Attr
1 01 2013-01-02 09:30:08.000 Excute
2 01 2013-02-02 09:30:08.000 Install
3 01 2013-07-02 09:30:08.000 Excute
表B
TID ConsoleTime Direction
01 null into
现在想更新B中的ConsoleTime字段，条件：通过TID查询表A，从得到的查询结果集中筛选CreateTime最小的也就是表A中的第一条记录来更新B中的ConsoleTime
[解决办法]
--先用select *测试看结果
UPDATE b SET b.ConsoleTime=a.minCreateTime
from 表B b join
(SELECT MIN(CreateTime) AS minCreateTime ,TID FROM 表A GROUP BY TID) a
on a.TID=b.TID
[解决办法]
--> 测试数据：表A
if object_id('表A') is not null drop table 表A
go
create table 表A([OID] int,[TID] varchar(6),[CreateTime] datetime,[Attr] varchar(10))
insert 表A
select 1,'01','2013-01-02 09:30:08.000','Excute' union all
select 2,'01','2013-02-02 09:30:08.000','Install' union all
select 3,'01','2013-07-02 09:30:08.000','Excute'
--> 测试数据：表B
if object_id('表B') is not null drop table 表B
go
create table 表B([TID] varchar(6),[CreateTime] datetime, [Direction] varchar(10))
insert 表B
select '01',null,'into'
update a set a.[CreateTime]=b.[CreateTime] from 表B a ,
(
select [TID],min([CreateTime]) as [CreateTime] from 表A group by [TID]
) b
where a.[TID]=b.[TID]
select * from 表B
if OBJECT_ID('tempdb..#A') is not null drop table #A;
if OBJECT_ID('tempdb..#B') is not null drop table #B;
create table #A(
OID INT,
TID INT,
CreateTime datetime,
Attr varchar(20)
);
create table #B(
TID INT,
ConsoleTime datetime,
Direction varchar(20)
);
insert into #A
select 1,01,'2013-01-02 09:30:08.000','Excute' union all
select 2 ,01,'2013-02-02 09:30:08.000', 'Install' union all
select 3, 01,'2013-07-02 09:30:08.000','Excute'
insert into #B
select 01,null,'into'
declare @TID int,@CreateTime datetime
select @TID =TID from #B --现在B表中只有一条数据,如果有多条数据需要考虑限制条件
select @CreateTime = MIN(CreateTime) from #A where TID = @TID
update #B set ConsoleTime= @CreateTime where TID = @TID
select * from #B
drop table #A
drop table #B
