请帮我写一段SQL语句，实现的功能不是很复杂的，有兴趣的进来看看。

Question

我写多几个数据吧：
Id1     Id2      date1        date2
1        0       2002-1-1     2002-1-2
2        1       2002-2-2     2002-1-3
3        1       2002-1-20    2002-1-4
4        1       2002-1-3     2002-2-3
5        0       2002-2-1     2002-1-5
6        5       2002-2-2     2002-1-6
7        5       2002-2-3     2002-1-7
变成：
Id1     Id2      date1        date2
1        0       2002-2-3     2002-1-2
2        1       2002-2-3     2002-1-3
3        1       2002-2-3     2002-1-4
4        1       2002-2-3     2002-1-5
5        0       2002-1-7     2002-1-5
6        5       2002-1-7     2002-1-6
7        5       2002-1-7     2002-1-7

Answer 1

我写多几个数据吧：  
Id1          Id2            date1                date2  
1                0              2002-1-1          2002-1-2  
2                1              2002-2-2          2002-1-3  
3                1              2002-1-20        2002-1-4  
4                1              2002-1-3          2002-2-3  
5                0              2002-2-1          2002-1-5  
6                5              2002-2-2          2002-1-6  
7                5              2002-2-3          2002-1-7  
变成：  
Id1          Id2            date1                date2  
1                0              2002-2-3          2002-1-2  
2                1              2002-2-3          2002-1-3  
3                1              2002-2-3          2002-1-4  
4                1              2002-2-3          2002-1-5  
5                0              2002-1-7          2002-1-5  
6                5              2002-1-7          2002-1-6  
7                5              2002-1-7          2002-1-7

Answer 2

UPDATE table1
SET date1=(SELECT MAX date2 FROM table1 WHERE id2=a.id2 or id2=a.id1)
FROM table1 a
WHERE ID2=0 OR ID2 IN (SELECT id1 FROM table1 WHERE Id2=0)

Answer 3

斑竹,这样写是不是严密点,否则id2=0的数据更新的结果不对吧(除了最后一个id2=0的数据)UPDATE table1
SET date1=(SELECT MAX(date2) FROM table1 WHERE (id2<>0 and id2=a.id2) or (id2=0 and id2=a.id1))
FROM table1 a
WHERE ID2=0 OR ID2 IN (SELECT id1 FROM table1 WHERE Id2=0)

Answer 4

好像外面的WHERE可以不要!!

Answer 5

嘿嘿,楼主要ACCESS的,我测试过了

Answer 6

是可以省
UPDATE table1
SET date1=(SELECT MAX date2 FROM table1 WHERE id2=a.id2 or id2=a.id1)
FROM table1 a

Answer 7

斑竹,不是我钻牛角尖,你把数据换成:Id1          Id2            date1                date2  
1                0              2002-1-1          2002-1-2  
2                1              2002-2-2          2002-1-3  
3                1              2002-1-20        2002-1-4  
4                1              2002-1-3          2002-1-10  
5                0              2002-2-1          2002-1-5  
6                5              2002-2-2          2002-1-6  
7                5              2002-2-3          2002-2-7  
你的结果变成：  
Id1          Id2            date1                date2  
1                0              2002-2-7          2002-1-2  
2                1              2002-2-7          2002-1-3  
3                1              2002-2-7          2002-1-4  
4                1              2002-2-7          2002-1-10  
5                0              2002-2-7          2002-1-5  
6                5              2002-2-7          2002-1-6  
7                5              2002-2-7          2002-2-7搂主要求的结果：  
Id1          Id2            date1                date2  
1                0              2002-1-10          2002-1-2  
2                1              2002-1-10          2002-1-3  
3                1              2002-1-10          2002-1-4  
4                1              2002-1-10          2002-1-10  
5                0              2002-2-7          2002-1-5  
6                5              2002-2-7          2002-1-6  
7                5              2002-2-7          2002-2-7

Answer 8

写错了:你的结果变成：  
Id1          Id2            date1                date2  
1                0              2002-2-7          2002-1-2  
2                1              2002-1-10          2002-1-3  
3                1              2002-1-10          2002-1-4  
4                1              2002-1-10          2002-1-10  
5                0              2002-2-7          2002-1-5  
6                5              2002-2-7          2002-1-6  
7                5              2002-2-7          2002-2-7

Answer 9

还是举错例子了,这个问题和ACCESS没有关系,看以下用SQL SERVER 的测试过程:create table #t (
id1 int,
id2 int,
date1 datetime,
date2 datetime
)insert #t values (1,                0,              '2002-1-1','2002-1-2')
insert #t values (2,                1,              '2002-2-2',          '2002-1-3'  )
insert #t values (3 ,               1 ,             '2002-1-20',        '2002-1-4'  )
insert #t values (4  ,              1  ,            '2002-1-3'  ,        '2002-1-10'  )
insert #t values (5   ,             0   ,           '2002-2-1'   ,       '2002-4-5' )
insert #t values (6    ,            5    ,          '2002-2-2'    ,      '2002-1-6' ) 
insert #t values (7     ,           5     ,         '2002-2-3'     ,     '2002-2-7')  
UPDATE #t
SET date1=(SELECT MAX(date2) FROM #t WHERE id2=a.id2 or id2=a.id1)
FROM #t a
WHERE ID2=0 OR ID2 IN (SELECT id1 FROM #t WHERE Id2=0)select * from #t结果:id1         id2         date1                       date2                       
----------- ----------- --------------------------- --------------------------- 
1           0           04 5 2002 12:00AM           01 2 2002 12:00AM           
2           1           01 10 2002 12:00AM          01 3 2002 12:00AM           
3           1           01 10 2002 12:00AM          01 4 2002 12:00AM           
4           1           01 10 2002 12:00AM          01 10 2002 12:00AM          
5           0           04 5 2002 12:00AM           04 5 2002 12:00AM           
6           5           02 7 2002 12:00AM           01 6 2002 12:00AM           
7           5           02 7 2002 12:00AM           02 7 2002 12:00AM           (7 row(s) affected)我的语句也有错误,应该改为:UPDATE table1
SET date1=(SELECT MAX(date2) FROM table1 WHERE (a.id2<>0 and id2=a.id2) or (a.id2=0 and id2=a.id1))
FROM table1 a结果:id1         id2         date1                       date2                       
----------- ----------- --------------------------- --------------------------- 
1           0           01 10 2002 12:00AM          01 2 2002 12:00AM           
2           1           01 10 2002 12:00AM          01 3 2002 12:00AM           
3           1           01 10 2002 12:00AM          01 4 2002 12:00AM           
4           1           01 10 2002 12:00AM          01 10 2002 12:00AM          
5           0           02 7 2002 12:00AM           04 5 2002 12:00AM           
6           5           02 7 2002 12:00AM           01 6 2002 12:00AM           
7           5           02 7 2002 12:00AM           02 7 2002 12:00AM           (7 row(s) affected)

Answer 10

no prblem!
UPDATE test2
SET date1=(SELECT MAX(date2) FROM test2 WHERE id2=a.id2 or id2=a.id1)
FROM test2 a
id1         id2         date1                                                  date2                                                  
----------- ----------- ------------------------------------------------------ ------------------------------------------------------ 
1           0           2002-01-01 00:00:00.000                                2002-01-02 00:00:00.000
2           1           2002-02-02 00:00:00.000                                2002-01-03 00:00:00.000
3           1           2002-01-20 00:00:00.000                                2002-01-04 00:00:00.000
4           1           2002-01-03 00:00:00.000                                2002-01-10 00:00:00.000
5           0           2002-02-01 00:00:00.000                                2002-01-05 00:00:00.000
6           5           2002-02-02 00:00:00.000                                2002-01-06 00:00:00.000
7           5           2002-02-03 00:00:00.000                                2002-01-07 00:00:00.000(7 row(s) affected)id1         id2         date1                                                  date2                                                  
----------- ----------- ------------------------------------------------------ ------------------------------------------------------ 
1           0           2002-01-10 00:00:00.000                                2002-01-02 00:00:00.000
2           1           2002-01-10 00:00:00.000                                2002-01-03 00:00:00.000
3           1           2002-01-10 00:00:00.000                                2002-01-04 00:00:00.000
4           1           2002-01-10 00:00:00.000                                2002-01-10 00:00:00.000
5           0           2002-01-07 00:00:00.000                                2002-01-05 00:00:00.000
6           5           2002-01-07 00:00:00.000                                2002-01-06 00:00:00.000
7           5           2002-01-07 00:00:00.000                                2002-01-07 00:00:00.000(7 row(s) affected)

Answer 11

IN SQL,字段最好取前面部分比较,因为带时间
UPDATE test2
SET date1=(SELECT MAX(convert(char(10),date2,20)) FROM test2 WHERE id2=a.id2 or id2=a.id1)
FROM test2 a

Answer 12

前面的例子错了，看以下例子：斑竹,不是我钻牛角尖,你把数据换成:Id1          Id2            date1                date2  
1                0              2002-1-1          2002-1-2  
2                1              2002-2-2          2002-1-3  
3                1              2002-1-20        2002-1-4  
4                1              2002-1-3          2002-1-10  
5                0              2002-2-1          2002-4-5  
6                5              2002-2-2          2002-1-6  
7                5              2002-2-3          2002-2-7  

Answer 13

所以我才提这个问题呀，在SQLServer中我们有很大的想象空间，用Access往往就不是那么得心应手了。

Answer 14

Access中，兩個或以上的table Join起來再做Update,則這些用做Join的table必須都是要可做修改的(即不是Derived Table)，才可以做更新操作。所以，解決方法是，再開一個table,當temp table來用，把符合條件的資料先Insert到此table中，再用這個table與原table做JOIN，再更新。