编辑：我正在使用MySQL，我发现了另一个具有相同问题的帖子，但它在Postgres中;我需要MySQL。
获取 SQL 中另一列的每个值的最常见值
在广泛搜索本网站和其他网站后，我问了这个问题，但没有找到我想要的结果。
我有一个人员表(记录ID，人员ID，交易ID)和一个事务表(交易ID，评级)。我需要一个可以返回每个人最常见的评级的 SQL 语句。
我目前有这个SQL语句，它返回指定人员ID的最常见评级。它有效，也许可以帮助其他人。
SELECT transactionTable.rating as MostCommonRating
FROM personTable, transactionTable
WHERE personTable.transactionid = transactionTable.transactionid
AND personTable.personid = 1
GROUP BY transactionTable.rating
ORDER BY COUNT(transactionTable.rating) desc
LIMIT 1
但是，我需要一个语句来执行上述语句对personTable中的每个personid所做的操作。
我的尝试在下面;但是，它使我的MySQL服务器超时。
SELECT personid AS pid,
(SELECT transactionTable.rating as MostCommonRating
FROM personTable, transactionTable
WHERE personTable.transactionid = transactionTable.transactionid
AND personTable.personid = pid
GROUP BY transactionTable.rating
ORDER BY COUNT(transactionTable.rating) desc
LIMIT 1)
FROM persontable
GROUP BY personid
你能给我的任何帮助都会非常有义务。谢谢。
PERSONTABLE：
RecordID, PersonID, TransactionID
1, Adam, 1
2, Adam, 2
3, Adam, 3
4, Ben, 1
5, Ben, 3
6, Ben, 4
7, Caitlin, 4
8, Caitlin, 5
9, Caitlin, 1
TRANSACTIONTABLE：
TransactionID, Rating
1 Good
2 Bad
3 Good
4 Average
5 Average
我正在搜索的 SQL 语句的输出将是：
输出：
PersonID, MostCommonRating
Adam Good
Ben Good
Caitlin Average
初步评论
请学习使用显式 JOIN 表示法，而不是旧的(1992 年之前)隐式连接表示法。
旧式：
SELECT transactionTable.rating as MostCommonRating
FROM personTable, transactionTable
WHERE personTable.transactionid = transactionTable.transactionid
AND personTable.personid = 1
GROUP BY transactionTable.rating
ORDER BY COUNT(transactionTable.rating) desc
LIMIT 1
首选样式：
SELECT transactionTable.rating AS MostCommonRating
FROM personTable
JOIN transactionTable
ON personTable.transactionid = transactionTable.transactionid
WHERE personTable.personid = 1
GROUP BY transactionTable.rating
ORDER BY COUNT(transactionTable.rating) desc
LIMIT 1
您需要为每个连接提供一个 ON 条件。
此外，数据中的personID值是字符串，而不是数字，因此您需要编写
WHERE personTable.personid = "Ben"
例如，使查询对显示的表起作用。
主要答案
您正在寻找聚合的聚合：在本例中为计数的最大值。 因此，任何通用解决方案都将涉及MAX和COUNT。 不能将 MAX 直接应用于 COUNT，但可以将 MAX 应用于子查询中的列，其中列恰好是 COUNT。
使用测试驱动查询设计 — TDQD 构建查询。
选择个人和交易评级
SELECT p.PersonID, t.Rating, t.TransactionID
FROM PersonTable AS p
JOIN TransactionTable AS t
ON p.TransactionID = t.TransactionID
选择人员、评级和评级的出现次数
SELECT p.PersonID, t.Rating, COUNT(*) AS RatingCount
FROM PersonTable AS p
JOIN TransactionTable AS t
ON p.TransactionID = t.TransactionID
GROUP BY p.PersonID, t.Rating
此结果将成为子查询。
查找此人获得任何评分的最大次数
SELECT s.PersonID, MAX(s.RatingCount)
FROM (SELECT p.PersonID, t.Rating, COUNT(*) AS RatingCount
FROM PersonTable AS p
JOIN TransactionTable AS t
ON p.TransactionID = t.TransactionID
GROUP BY p.PersonID, t.Rating
) AS s
GROUP BY s.PersonID
现在我们知道每个人的最大计数是多少。
所需结果
为了得到结果，我们需要从子查询中选择具有最大计数的行。 请注意，如果某人有 2 个好和 2 个差评级(2 是该人员相同类型的最大评分数)，则将为该人显示两条记录。
SELECT s.PersonID, s.Rating
FROM (SELECT p.PersonID, t.Rating, COUNT(*) AS RatingCount
FROM PersonTable AS p
JOIN TransactionTable AS t
ON p.TransactionID = t.TransactionID
GROUP BY p.PersonID, t.Rating
) AS s
JOIN (SELECT s.PersonID, MAX(s.RatingCount) AS MaxRatingCount
FROM (SELECT p.PersonID, t.Rating, COUNT(*) AS RatingCount
FROM PersonTable AS p
JOIN TransactionTable AS t
ON p.TransactionID = t.TransactionID
GROUP BY p.PersonID, t.Rating
) AS s
GROUP BY s.PersonID
) AS m
ON s.PersonID = m.PersonID AND s.RatingCount = m.MaxRatingCount
如果您也想要实际的评分计数，则很容易选择。
这是一个相当复杂的SQL片段。 我不想尝试从头开始写。 事实上，我可能不会打扰;我会逐步开发它，或多或少如所示。 但是，由于我们在更大的表达式中使用子查询之前已经调试了子查询，因此我们可以对答案充满信心。
与条款
请注意，标准 SQL 提供了一个 WITH 子句，该子句以 SELECT 语句为前缀，命名子查询。 (它也可以用于递归查询，但我们在这里不需要它。
WITH RatingList AS
(SELECT p.PersonID, t.Rating, COUNT(*) AS RatingCount
FROM PersonTable AS p
JOIN TransactionTable AS t
ON p.TransactionID = t.TransactionID
GROUP BY p.PersonID, t.Rating
)
SELECT s.PersonID, s.Rating
FROM RatingList AS s
JOIN (SELECT s.PersonID, MAX(s.RatingCount) AS MaxRatingCount
FROM RatingList AS s
GROUP BY s.PersonID
) AS m
ON s.PersonID = m.PersonID AND s.RatingCount = m.MaxRatingCount
这更容易编写。 不幸的是，MySQL还不支持WITH子句。
上述 SQL 现已针对在 Mac OS X 10.7.4 上运行的 IBM Informix Dynamic Server 11.70.FC2 进行了测试。 该测试暴露了初步评论中诊断出的问题。 主答案的 SQL 工作正常，无需更改。
这是一个有点黑客的滥用，MySQL中的max聚合函数对varchars进行词法排序(以及对整数/浮点数进行预期的数字排序)：
SELECT
PersonID,
substring(max(concat(lpad(c, 20, '0'), Rating)), 21) AS MostFrequentRating
FROM (
SELECT PersonID, Rating, count(*) c
FROM PERSONTABLE INNER JOIN TRANSACTIONTABLE USING(TransactionID)
GROUP BY PersonID, Rating
) AS grouped_ratings
GROUP BY PersonID;
这给出了所需的：
+----------+--------------------+
| PersonID | MostFrequentRating |
+----------+--------------------+
| Adam | Good |
| Ben | Good |
| Caitlin | Average |
+----------+--------------------+
(注意，如果每个人有多个模式，它将选择字母条目最高的模式，所以 - 几乎随机 - 好与坏和坏与平均)
您应该能够通过检查以下内容来查看max正在运行的内容：
SELECT PersonID, Rating, count(*) c, concat(lpad(count(*), 20, '0'), Rating) as LexicalMaxMe
FROM PERSONTABLE INNER JOIN TRANSACTIONTABLE USING(TransactionID)
GROUP BY PersonID, Rating
ORDER BY PersonID, c DESC;
哪些输出：
+----------+---------+---+-----------------------------+
| PersonID | Rating | c | LexicalMaxMe |
+----------+---------+---+-----------------------------+
| Adam | Good | 2 | 00000000000000000002Good |
| Adam | Bad | 1 | 00000000000000000001Bad |
| Ben | Good | 2 | 00000000000000000002Good |
| Ben | Average | 1 | 00000000000000000001Average |
| Caitlin | Average | 2 | 00000000000000000002Average |
| Caitlin | Good | 1 | 00000000000000000001Good |
+----------+---------+---+-----------------------------+
对于使用 SQL Server Microsoft任何人：您可以创建自定义聚合函数来获取最常见的值。Ahmed Tarek Hasan的这篇博客文章的示例2描述了如何做到这一点：
http://developmentsimplyput.blogspot.nl/2013/03/creating-sql-custom-user-defined.html