mysql - 选择在MySQL中列中出现次数最多的值

以下是样品表

Project | Reporter | Fixer  | Status
--------+----------+--------+--------
P1      | Fernando | Janith | closed
P1      | hasitha  | Nimna  | Fixed
p1      | Amal     | Nimna  | Fixed
P2      | Nimal    | Amal   | Fixed
P3      | Kamal    | Nimal  | Fixed
P4      | Andrew   | Amal   | Fixed

我想要的是项目名称，每个项目的状态计数，每个项目的状态为“固定”的计数，项目的状态为“已关闭”的计数以及固定最多项目数量的Fixer。

除了加粗部分，我可以使用以下查询来获取其他部分

SELECT Project
    ,count(Project) AS ProjectCount
    ,count(CASE STATUS
            WHEN 'Fixed'
                THEN 1
            ELSE NULL
            END) AS Fixed
    ,count(CASE STATUS
            WHEN 'Closed'
                THEN 1
            ELSE NULL
            END) AS Closed
FROM TABLE
GROUP BY Project;

我需要的输出

Project, ProjectCount, Fixed, Closed, Fixcer
P1,          3,          2,     1,     Nimna

最佳答案

这里的问题是，将选择出现在项目中最多但不处理联系的第一个修复程序。因此，您应该提供规则来处理联系。

SQL Fiddle Demo

SELECT Project
    ,count(Project) AS ProjectCount
    ,count(CASE STATUS
            WHEN 'Fixed'
                THEN 1
            ELSE NULL
            END) AS Fixed
    ,count(CASE STATUS
            WHEN 'Closed'
                THEN 1
            ELSE NULL
            END) AS Closed
    , (SELECT fixer
       FROM Table1 B
       WHERE A.`Project` = B.`Project`
       GROUP BY fixer
       ORDER BY COUNT(*) DESC
       LIMIT 1
      ) as Fixer
FROM TABLE1 A
GROUP BY Project;

输出值

| Project | ProjectCount | Fixed | Closed | Fixer |
|---------|--------------|-------|--------|-------|
|      P1 |            3 |     2 |      1 | Nimna |
|      P2 |            1 |     1 |      0 |  Amal |
|      P3 |            1 |     1 |      0 | Nimal |
|      P4 |            1 |     1 |      0 |  Amal |

关于mysql - 选择在MySQL中列中出现次数最多的值，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/35484166/