mysql - 在内部查询中使用外部查询中的结果

Question

我想做的是从数据库中查询所有唯一的区名，并在每个区名上，得到其对应的 MIN 和 MAX ID.

这是我现在拥有的:

SELECT DISTINCT DISTRICTNAME, 
(SELECT MIN(`ID`)
    FROM [DATABASE_NAME]
        WHERE DISTRICTNAME = DISTRICTNAME) AS 'MIN', 
(SELECT MAX(`ID`) 
    FROM [DATABASE_NAME]
        WHERE DISTRICTNAME = DISTRICTNAME) AS 'MAX'
FROM [DATABASE_NAME]
WHERE [CONDITION]
ORDER BY DISTRICTNAME;

问题:MIN 和 MAX 列显示相同的值。我不太确定我的内部查询的 WHERE 子句。我认为这就是问题所在。

帮忙吗？

Answer 1

使用 GROUP BY 子句尝试此查询

SELECT DISTRICTNAME, MIN(`ID`) AS 'MIN', MAX(`ID`) AS 'MAX'
FROM [DATABASE_NAME]
WHERE [CONDITION]
GROUP BY DISTRICTNAME
ORDER BY DISTRICTNAME;;