对 SQL 子查询的结果使用“LIKE" [英] Using 'LIKE' with the result of a SQL subquery

问题描述

以下查询对我来说非常好:

The following query is working absolutely fine for me:

SELECT * From Customers
WHERE Customers.ContactName = (SELECT FirstName
                               FROM Employees as E, orders as O
                               WHERE <condition>
                               LIMIT 1);

但是，如果我使用 LIKE 而不是 = 来与子查询的结果进行比较，我不会得到任何结果.如何在上述查询中使用 LIKE '%%'?

However, if i use LIKE instead of = to compare with the result of the subquery, I'm not getting any results. How do I use LIKE '%%' in the above query?

推荐答案

首先，这个查询应该不能正常工作:

First, this query should not be working fine:

SELECT *
From Customers
WHERE Customers.ContactName = (SELECT FirstName
                               from Employees as E, orders as O
                               WHERE LIMIT 1);

因为WHERE LIMIT 1 不是正确的 SQL.而且，您应该学会使用正确的 join 语法.据推测，您打算:

Because WHERE LIMIT 1 is not proper SQL. And, you should learn to use proper join syntax. Presumably, you intend:

SELECT c.*
From Customers c
WHERE c.ContactName = (SELECT FirstName
                       FROM Employees as E JOIN
                            Orders as O
                            ON . . .
                       LIMIT 1
                      );

您可以想象在子查询中添加 LIKE 而不是 = 和 '%':

You could conceivably add LIKE instead of = and '%' in the subquery:

WHERE c.ContactName LIKE (SELECT CONCAT('%', FirstName, '%') . . .

但我会用EXISTS来写这个:

SELECT c.*
From Customers c
WHERE EXISTS (SELECT 1
              FROM Employees as E JOIN
                   Orders as O
                   ON . . .
              WHERE c.ContactName LIKE CONCAT('%', FirstName, '%')
             );

这与您的查询不完全相同.它做了一些更合理的事情.它不是比较子查询中的一个随机名称，而是确定子查询中是否有any 匹配项.这似乎是查询的更合理意图.

This does not do exactly the same thing as your query. It does something more reasonable. Instead of comparing one random name from the subquery, it will determine if there are any matches in the subquery. That seems a more reasonable intention for the query.

这篇关于对 SQL 子查询的结果使用“LIKE"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！