Notes
0%

SQL-Join-Inequality

发表于 更新于 分类于 Valine：
SQL不等式的join案例

ODPS不等式join

情景A

A表中有一些数，想去B表某个字段比A小的所有数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
SELECT  *
FROM    (
            SELECT  4 AS f, 'a' as c
            UNION
            SELECT  8 AS f, 'a' as c
        ) a
JOIN    (
            SELECT  1 AS f, 'a' as c
            UNION
            SELECT  2 AS f, 'a' as c
            UNION
            SELECT  3 AS f, 'a' as c
            UNION
            SELECT  4 AS f, 'a' as c
            UNION
            SELECT  5 AS f, 'a' as c
            UNION
            SELECT  6 AS f, 'a' as c
            UNION
            SELECT  7 AS f, 'a' as c
            UNION
            SELECT  8 AS f, 'a' as c
        ) b
ON  a.c = b.c
and a.f > b.f
;

结果

情景：取B表某个字段在A表的一个范围内的

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
SELECT  *
FROM    (
            SELECT  4 AS f, 'a' as c
            UNION
            SELECT  8 AS f, 'a' as c
        ) a
JOIN    (
            SELECT  1 AS f, 'a' as c
            UNION
            SELECT  2 AS f, 'a' as c
            UNION
            SELECT  3 AS f, 'a' as c
            UNION
            SELECT  4 AS f, 'a' as c
            UNION
            SELECT  5 AS f, 'a' as c
            UNION
            SELECT  6 AS f, 'a' as c
            UNION
            SELECT  7 AS f, 'a' as c
            UNION
            SELECT  8 AS f, 'a' as c
        ) b
ON  a.c = b.c
and b.f BETWEEN a.f-2 and a.f
;

结果