sql - 带有 LIKE 子句的 SQL LEFT JOIN 仅给出一行

Question

我有 2 个要加入的表，我想生成与表 1 相同的行数。

表格1

+----------+------------+---------+-------+
| ENTRY_ID | ROUTE_NAME | STATION | BOUND |
+----------+------------+---------+-------+
|        1 |         1A |    ABCC |     1 |
|        2 |         2C |    CBDD |     1 |
|        3 |          5 |    AAAA |     2 |
|        4 |         1A |    EEEE |     1 |
|        5 |         2B |    ASFA |     2 |
|        6 |          5 |    DSAS |     1 |
|        7 |          3 |    QWEA |     2 |
|        8 |          4 |    ASDA |     1 |
+----------+------------+---------+-------+

表 2

+------------+-------+---------+---------------+
| ROUTE_NAME | BOUND | STATION | STOP_SEQUENCE |
+------------+-------+---------+---------------+
|         1A |     1 |     AAA |             1 |  
|         1A |     1 |     ABC |             2 |
|         1A |     1 |     CDA |             3 |
|         1A |     2 |     ABC |             1 |
|         1A |     2 |     ADC |             2 |
|         1A |     2 |     ACA |             3 |
|         2C |     1 |     BBB |             1 |
|         2C |     1 |     AAA |             2 |

对其他值重复

我使用的代码是：

SELECT t1.ENTRY_ID, t1.ROUTE_NAME, t1.STATION, t1.BOUND, MIN(t2.STOP_SEQUENCE)
FROM T1 
LEFT JOIN t2 ON 
(t1.STATION LIKE '*' & t2.STATION & '*') AND
(t1.BOUND = t2.BOUND) AND
(t1.ROUTE_NAME = t2.ROUTE_NAME)
GROUP BY t1.ENTRY_ID, t1.ROUTE_NAME, t1.STATION, t1.BOUND

;

在这种情况下，LIKE 功能是必须的。该代码返回的行数与表 1 的行数不同。相反，对于那些不符合 LIKE 要求的行，它们在查询结果中不可用。即使 LIKE 函数不返回任何内容，如何获取所有行？我的预期结果是

+----------+------------+---------+-------+---------------+
| ENTRY_ID | ROUTE_NAME | STATION | BOUND | STOP_SEQUENCE |
+----------+------------+---------+-------+---------------+
|        1 |         1A |    ABCC |     1 |             2 |
|        2 |         2C |    CBDD |     1 |          NULL |

对于 ENTRY_ID 的 8 行。

非常感谢！

标签： sqlms-access

Answer 1

尝试使用相关子查询：

SELECT t1.ENTRY_ID, t1.ROUTE_NAME, t1.STATION, t1.BOUND,
       (SELECT MIN(t2.STOP_SEQUENCE) FROM t2
        WHERE (t1.STATION LIKE '*' & t2.STATION & '*')
          AND (t1.BOUND = t2.BOUND)
          AND (t1.ROUTE_NAME = t2.ROUTE_NAME)) as STOP_SEQUENCE 
FROM T1