SELECT语句中的多个记录数以及子查询语句中LIMIT为1的多个子查询数是多少

Question

SELECT
(SELECT NAME FROM OCCUPATIONS WHERE OCCUPATION = 'Doctor'    ORDER BY NAME LIMIT 1),
(SELECT NAME FROM OCCUPATIONS WHERE OCCUPATION = 'Professor' ORDER BY NAME LIMIT 1),
(SELECT NAME FROM OCCUPATIONS WHERE OCCUPATION = 'Singer'    ORDER BY NAME LIMIT 1),
(SELECT NAME FROM OCCUPATIONS WHERE OCCUPATION = 'Actor'     ORDER BY NAME LIMIT 1)
FROM OCCUPATIONS

输出显示了10条记录，但我觉得它应该返回1条记录，因为我们在子查询中已经有了LIMIT 1条件。需要帮助！

Answer 1

如果您在查询结束时删除FROM OCCUPATIONS，它可能会在这里提供您想要的内容：

SELECT
(SELECT NAME FROM OCCUPATIONS WHERE OCCUPATION = 'Doctor'     ORDER BY NAME LIMIT 1), 
(SELECT NAME FROM OCCUPATIONS WHERE OCCUPATION = 'Professor'  ORDER BY NAME LIMIT 1),
(SELECT NAME FROM OCCUPATIONS WHERE OCCUPATION = 'Singer'     ORDER BY NAME LIMIT 1),
(SELECT NAME FROM OCCUPATIONS WHERE OCCUPATION = 'Actor'      ORDER BY NAME LIMIT 1);

然而，在MySQL中，更典型的方法是通过聚合：

SELECT o1.NAME
FROM OCCUPATIONS o1
INNER JOIN
(
SELECT OCCUPATION, MIN(NAME) AS NAME
FROM OCCUPATIONS
GROUP BY OCCUPATION
) o2
ON o2.OCCUPATION = o1.OCCUPATION AND
o2.NAME = o1.NAME;

如果您使用的是MySQL 8+，那么ROW_NUMBER提供了另一种方法：

WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY OCCUPATION ORDER BY NAME) rn
FROM OCCUPATIONS
)
SELECT NAME
FROM cte
WHERE rn = 1;

Answer 2

使用聚合！

SELECT MIN(CASE WHEN OCCUPATION = 'Doctor' THEN NAME END),
MIN(CASE WHEN OCCUPATION = 'Professor' THEN NAME END),
MIN(CASE WHEN OCCUPATION = 'Singer' THEN NAME END),
MIN(CASE WHEN OCCUPATION = 'Actor' THEN NAME END)
FROM OCCUPATIONS;

这似乎是表达逻辑的最简单方式。

您的查询将为OCCUPATIONS中的每一行返回一行，因为该查询没有筛选或聚合。每一行恰好具有相同的值，因为除了FROM子句之外，查询中的任何地方都不使用外部表。

条件聚合(如上所示(是表达这种逻辑的一种更简单的方式。