H*M 发帖数: 1268 | 1 write in ANSI SQL only.
you are given two tables:
Table A:
IDA(int) x(double) y(double) correlatedIDB(int)
1 1.0 2.0
2 2.1 3.3
Table B:
IDB(int) x(double) y(double)
1 1.1 2.1
2 2 3
3 10 15
find the cloest ID in table B for each row in A and update the value in Tabl
e A.
by closest, it means min(sqr(A.x-B.x) + sqr(A.y-B.y)).
Therefore,
The correlatedIDB of the first row in Table A should be 1
The correla | a9 发帖数: 21638 | 2 cursor算不算ansi sql里的?
Tabl
【在 H*M 的大作中提到】![](/moin_static193/solenoid/img/up.png) : write in ANSI SQL only. : you are given two tables: : Table A: : IDA(int) x(double) y(double) correlatedIDB(int) : 1 1.0 2.0 : 2 2.1 3.3 : Table B: : IDB(int) x(double) y(double) : 1 1.1 2.1 : 2 2 3
| a9 发帖数: 21638 | 3 看看这个行不?
update a set a.correlatedidb=d.minres from a left join (
select aid,min(res) minres from
(select a.id aid,b.id bid,min(sqr(a.x-b.x)+sql(a.y-b.y)) res from a cross
join b) c
group by aid) d on a.id=d.aid
Tabl
【在 H*M 的大作中提到】![](/moin_static193/solenoid/img/up.png) : write in ANSI SQL only. : you are given two tables: : Table A: : IDA(int) x(double) y(double) correlatedIDB(int) : 1 1.0 2.0 : 2 2.1 3.3 : Table B: : IDB(int) x(double) y(double) : 1 1.1 2.1 : 2 2 3
|
|