UPDATE a SET a.col1 = MIN(b.col1) , a.col2 = b.col2 FROM tb1 a INNER JOIN tb2 b ON a.id = b.id WHERE a.starttime <= b.starttime --order by b.starttime desc 不知道是不是你想要的,但是order by是不用再update里面的
我感觉和我想的不一样 我是要top第一条 但是第一条的数据不代表min吧?
我是看到你使用了order by desc,也就是倒序排序,叫你试试其实也是为了证明我的是不是正确而已,你给点数据和表结构来
如果说order by的字段和获得的列是同一个倒是可以用min或max 你认为呢?
MSSQL2008及以上版本:CREATE TABLE t1 ( id INT, col1 VARCHAR(10), col2 VARCHAR(10), starttime DATE ) INSERT INTO t1 SELECT 1,'aa','a1','2012-10-01' UNION ALL SELECT 1,'bb','b1','2012-10-02' UNION ALL SELECT 1,'cc','c1','2012-10-03' UNION ALL SELECT 2,'dd','d1','2012-10-04' UNION ALL SELECT 2,'ee','e1','2012-10-05' CREATE TABLE t2 ( id INT, col1 VARCHAR(10), col2 VARCHAR(10), starttime DATE ) INSERT INTO t2 SELECT 1,'AA','A1','2012-10-3' UNION ALL SELECT 1,'BB','B1','2012-10-3' UNION ALL SELECT 2,'CC','C1','2012-10-5' UNION ALL SELECT 2,'DD','D1','2012-10-5' SELECT * FROM t1 SELECT * FROM t2;WITH aaa AS ( SELECT TOP (1) a.id AS aid,a.col1 AS acol1,a.col2 AS acol2,a.starttime AS astarttime, b.id AS bid,b.col1 AS bcol1,b.col2 AS bcol2,b.starttime AS bstarttime FROM t1 AS a INNER JOIN t2 AS b ON a.id=b.id AND a.starttime<b.starttime ORDER BY b.starttime ) UPDATE aaa SET acol1=bcol1,acol2=bcol2
SET a.col1 = MIN(b.col1) ,
a.col2 = b.col2
FROM tb1 a
INNER JOIN tb2 b ON a.id = b.id
WHERE a.starttime <= b.starttime
--order by b.starttime desc
不知道是不是你想要的,但是order by是不用再update里面的
(
id INT,
col1 VARCHAR(10),
col2 VARCHAR(10),
starttime DATE
)
INSERT INTO t1
SELECT 1,'aa','a1','2012-10-01' UNION ALL
SELECT 1,'bb','b1','2012-10-02' UNION ALL
SELECT 1,'cc','c1','2012-10-03' UNION ALL
SELECT 2,'dd','d1','2012-10-04' UNION ALL
SELECT 2,'ee','e1','2012-10-05'
CREATE TABLE t2
(
id INT,
col1 VARCHAR(10),
col2 VARCHAR(10),
starttime DATE
)
INSERT INTO t2
SELECT 1,'AA','A1','2012-10-3' UNION ALL
SELECT 1,'BB','B1','2012-10-3' UNION ALL
SELECT 2,'CC','C1','2012-10-5' UNION ALL
SELECT 2,'DD','D1','2012-10-5'
SELECT * FROM t1
SELECT * FROM t2;WITH aaa AS
(
SELECT TOP (1) a.id AS aid,a.col1 AS acol1,a.col2 AS acol2,a.starttime AS astarttime,
b.id AS bid,b.col1 AS bcol1,b.col2 AS bcol2,b.starttime AS bstarttime
FROM t1 AS a INNER JOIN
t2 AS b ON a.id=b.id
AND a.starttime<b.starttime
ORDER BY b.starttime
)
UPDATE aaa SET acol1=bcol1,acol2=bcol2