mysql - 使用联合混合结果的 SQL 子查询

Question

我试图找出哪个经销商的蓝色汽车最多。表架构如下：

AVAILABLE_AUTO(SERIAL_NO,MODEL,COLOR,DEALER)

我有以下查询，它返回以下结果：

SELECT COUNT(*) AS COUNT, DEALER
FROM global.AVAILABLE_AUTO
WHERE COLOR = "blue" AND DEALER = "DEALER ONE"
UNION
SELECT COUNT(*) AS COUNT, DEALER
FROM global.AVAILABLE_AUTO
WHERE COLOR = "blue" AND DEALER = "DEALER TWO";

|COUNT|DEALER    |
|----------------|
|1    |DEALER ONE|
|2    |DEALER TWO|

我试图选择这两个结果中的最大值，所以我使用以下查询：

SELECT MAX(COUNT) AS COUNT, DEALER FROM
(
SELECT COUNT(*) AS COUNT, DEALER
FROM global.AVAILABLE_AUTO
WHERE COLOR = "blue" AND DEALER = "DEALER ONE"
UNION
SELECT COUNT(*) AS COUNT, DEALER
FROM global.AVAILABLE_AUTO
WHERE COLOR = "blue" AND DEALER = "DEALER TWO"
) AS T1;

但它会产生不正确的结果：

|COUNT|DEALER    |
|----------------|
|2    |DEALER ONE|

Dealer在这种情况下应该是DEALER TWO

标签： mysqlsql

Answer 1

只需将单个查询与GROUP BYand一起使用LIMIT：

SELECT COUNT(*) AS COUNT, DEALER
FROM global.AVAILABLE_AUTO
WHERE COLOR = 'blue' AND DEALER IN ('DEALER ONE', 'DEALER TWO')
GROUP BY DEALER
ORDER BY COUNT(*) DESC
LIMIT 1;

您的最终查询格式不正确，因为DEALER不在 中GROUP BY但在SELECT.