MySQL的IN子句的特点

Question

我刚使用MySQL的IN解决了一个有趣的问题，但我不知道这个“技巧”叫什么。

在我的设计中，我需要过滤掉不需要某种技能的行，或者技能名称和技能水平都符合要求的行。以下是解决方案:

SELECT * FROM table WHERE
    skillname = "" OR
    (skillname, skilllevel) IN (
        SELECT name, level FROM skills WHERE user="Mikhail"
    )

我不知道expr IN (value,...)的含义，根据dev.mysql.com的说明，expr和value都可以是多个列。

这被称为什么，还有其他类似的快捷方式吗？
更重要的是 - 是否有一种方法可以将此查询转换为匹配skilllevel为“大于或等于”？

---

解决方案根据@yokoloko（尽管他不会承认）

SELECT * FROM t1 WHERE
    skillname = "" OR
    skillname IN (
        SELECT name FROM skills WHERE user="Mikhail" AND level >= t1.skilllevel
    )

- Mikhail

Answer 1

抱歉，IN谓词总是比较值的相等性，而不是大于等于。

以下是我会写的方式：

SELECT table.* 
FROM table WHERE skillname = ''
UNION ALL
SELECT table.* 
FROM table JOIN skills 
  ON table.skillname = skills.name AND table.skilllevel >= skills.level
WHERE skills.user = 'Mikhail'

关于您查询的术语，@guide指出您运行了一个“行子查询”，并使用“行构造器”语法将其与文字行进行比较。 SQL允许您使用列、表达式或值创建匿名行，并在某些情况下使用它们，例如与具有兼容列数的另一行进行比较。我会称之为“行比较”。您还可以在连接条件中使用此功能，但如果您想要对一列进行等式比较，而对另一列进行不等式比较，则会变得非常不清楚。

SELECT table.* 
FROM table JOIN skills 
  ON (table.skillname, table.skilllevel) >= (skills.name, skills.level)
WHERE skills.user = 'Mikhail'

上述内容可能不符合您的要求。例如：

SELECT (1,2) = (1,2);   -- returns true (i.e. 1 in MySQL)

SELECT (1,2) >= (1,2);  -- returns true

SELECT (1,3) >= (1,2);  -- returns true

SELECT (1,2) >= (1,3);  -- returns false

SELECT (2,3) >= (1,2);  -- returns true

SELECT (1,3) >= (2,2);  -- returns false

在这些行中，相同的比较运算符适用于所有列。但它也会短路（不像 AND ）：

SELECT (2,3) >= (1,4);  -- returns true

2比1大，但3不比4大。令人困惑？来看一个字母排序的例子：

SELECT ('Smith', 'Agent') >= ('Anderson', 'Thomas'); -- returns true

Smith比Anderson更大，但Agent不比Thomas更大。然而，我们知道如何在电话簿中排列这两个名字。

所有这些的重点是，如果您想更好地控制比较，则需要编写完整的布尔表达式。

- Bill Karwin

Answer 2

这被称为行子查询

行子查询是一种子查询变体，返回单个行并且可以返回多列值。

- guido

---

行子查询而不是原始查询，当然不能编辑少于6个字符。 - yokoloko

Answer 3

我不知道它叫什么，但如果我理解正确，您可以像这样做以获得大于符号：

SELECT * FROM table t1 WHERE
    skillname = "" OR
    (skillname, skilllevel) IN (
        SELECT name, level FROM skills s WHERE user="Mikhail" and t1.skilllevel >= s.level
    )

但我不知道它是否真正优化良好。

- yokoloko

---

虽然这是有道理的，但由于IN比较相等性，所以仍然行不通。 - Mikhail

我相信如果你只是在 IN 的左边和 SELECT 中删除 skilllevel 和 level，整个查询将会工作，因为如果等级不匹配，name 将不会被返回。 - Mikhail

是的，但你就不知道技能水平了，而显然你需要它。 - yokoloko

子查询的 WHERE 子句仍然可以访问 t1.skilllevel，因此 >= 将正常工作。 - Mikhail