编辑:我正在使用 MySQL，我发现了另一篇有相同问题的帖子，但它在 Postgres 中；我需要 MySQL。

Get most common value for each value of another column in SQL

我在广泛搜索本网站和其他网站后提出了这个问题，但没有找到符合我预期的结果。

我有一个人员表(recordid、personid、transactionid)和一个交易表(transactionid、rating)。我需要一个 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

如果您能给我任何帮助，我将不胜感激。谢谢。

个性化:

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

交易表:

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

每个 JOIN 都需要一个 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。我不想尝试从头开始写。确实，我可能不会打扰；我会逐步开发它，或多或少如图所示。但是因为我们在更大的表达式中使用它们之前已经调试了子查询，所以我们可以对答案充满信心。

WITH 子句

请注意，标准 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 - SQL:返回每个人最常见的值，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/12446368/