SQL LEFT JOIN with LIKE子句仅提供一行
SQL LEFT JOIN with LIKE子句仅提供一行
提问于 2019-04-05 04:41:15
我有两个要连接的表，我希望生成与表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 |用于8行ENTRY_ID。
非常感谢！
回答 1
Stack Overflow用户
发布于 2019-04-05 04:56:52
请尝试相关子查询：
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 页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/55524583
复制相关文章
相似问题
腾讯云开发者
