MySQL使用select for LEFT JOIN ON

Just want to know can I make a such query?

只是想知道我可以进行这样的查询吗?

SELECT
  t1.id,
  t1.name
FROM t1
LEFT JOIN t2 ON t2.id = (SELECT id FROM t3 WHERE t1.address LIKE 'street%' ORDER BY name ASC)
ORDER BY name DESC

This is because I want to add more complex query to the LEFT JOIN with sorting and order and some statements which depends on another table.. Thanks!

这是因为我想通过排序和顺序以及一些依赖于另一个表的语句向LEFT JOIN添加更复杂的查询..谢谢!

U.P.D.

SELECT * FROM t3 WHERE t1.address LIKE CONCAT(address,'%') ORDER BY LENGTH(address) DESC

Actually, I want to write this query as LEFT JOIN subquery (ORDER BY in case of LIKE does make sense!).

实际上,我想将此查询写为LEFT JOIN子查询(如果LIKE的话,ORDER BY确实有意义!)。

1 个解决方案

#1

Theoretically you can do this.

从理论上讲,你可以做到这一点。

Writing subquery in join statement will have no effect other than filtering the cartesian product of the two tables just like the where condition. But writing query this way makes no sense as we don't know the context in which you are using it.

在join语句中编写子查询除了过滤两个表的笛卡尔积之外没有任何效果,就像where条件一样。但是以这种方式编写查询毫无意义,因为我们不知道您使用它的上下文。

The above query can be written in much cleaner way as follows :

上面的查询可以用更清晰的方式编写如下:

SELECT
  t1.id,
  t1.name
FROM t1, t2
WHERE t2.id in (SELECT id FROM t3 WHERE address LIKE 'street%') 
ORDER BY name DESC

It will produce the same result set as the query you provided

它将生成与您提供的查询相同的结果集

#1