SQL：返回每个人的最常见值

Question

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

获取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

您能给我的任何帮助将非常有必要。谢谢。

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

Answer 1

初步评论

请学习使用显式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正常运行，无需更改。