江湖救急 伤脑筋的mssql查询问题 在线等

[甩尾儿Boy]

[dawugui]

取最近的时间吗?

[dawugui]

select t1.* , (select top 1 t2.time from t2 where t1.bh = t2.bh and t2.time < t1.time order by time desc ) from t1

[gw6328]

图文并茂，还是没懂起。可能结构有点复杂

[甩尾儿Boy]

是的 取小于表1中的最近时间

[-晴天]

用动态语句.

[dawugui]

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 行）

*/

[甩尾儿Boy]

关键是表2的名字是要根据表1中的 时间字段动态生成的

[gw6328]

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]

[甩尾儿Boy]

表2的结构是就 图片上的那个 表表2的名字是要根据表1中的 时间字段动态生成的

例如表1中时间字段是 2011-01-11 那么表2的名称就因该是P201101

[甩尾儿Boy]

大侠们 救救小弟吧

[-晴天]

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 行受影响)
*/

[甩尾儿Boy]

[-晴天]

但是,你这样设计,对表的操作会变得很麻烦,每次都要采用动态语句,因为你的表名是动态生成的.

[甩尾儿Boy]

对了 类似表2的 表有很多 在数据库里面是 月表 已经存在了 我只是取数据

[甩尾儿Boy]

每个月都会生成的