22,209
社区成员
发帖
与我相关
我的任务
分享
create table tb1(bh int,time datetime,other int)
insert into tb1 select 1,'2010-01-12 12:10:10',5
insert into tb1 select 1,'2010-02-15 12:10:10',5
insert into tb1 select 1,'2010-05-12 12:10:10',5
go
declare @sql varchar(1000)
select @sql='create table P'+convert(varchar(6),max(time),112)+
'(id int,bh int,[time] datetime,other int)' from tb1
exec(@sql)
insert into p201005 select 1,1,'2010-01-07',15
select * from p201005
go
drop table tb1,p201005
/*
id bh time other
----------- ----------- ----------------------- -----------
1 1 2010-01-07 00:00:00.000 15
(1 行受影响)
*/
UPDATE TABLE1 SET 其它=CAST( CAST(MAX(CONVERT(DATE,B.[TIME],120)) AS VARCHAR(10))+CAST(MIN(CONVERT(NVARCHAR(10),B.[TIME],120)) AS VARCHAR(5)) AS DATETIME)
FROM TABLE1 A LEFT JOIN TABLE2 B ON A.[BH]=B.[BH]
GROUP BY A.[BH]
create table t1(bh int,time datetime)
insert into t1 values(1,'2010-01-10 12:10')
insert into t1 values(2,'2010-01-11 12:10')
insert into t1 values(3,'2010-01-12 12:10')
insert into t1 values(5,'2010-01-13 12:10')
insert into t1 values(4,'2010-01-13 12:10')
create table t2(id int, bh int,time datetime)
insert into t2 values(1,1,'2010-01-07 12:10')
insert into t2 values(2,1,'2010-01-08 12:10')
insert into t2 values(3,1,'2010-01-10 13:10')
insert into t2 values(4,2,'2010-01-12 09:10')
insert into t2 values(5,2,'2010-01-12 13:10')
go
select t1.bh , t1.time time1 , time2 = (select top 1 t2.time from t2 where t1.bh = t2.bh and t2.time < t1.time order by t2.time desc ) from t1
drop table t1,t2
/*
bh time1 time2
----------- ------------------------------------------------------ ------------------------------------------------------
1 2010-01-10 12:10:00.000 2010-01-08 12:10:00.000
2 2010-01-11 12:10:00.000 NULL
3 2010-01-12 12:10:00.000 NULL
5 2010-01-13 12:10:00.000 NULL
4 2010-01-13 12:10:00.000 NULL
(所影响的行数为 5 行)
*/