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 的大作中提到】
: 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 的大作中提到】
: 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
|
|
