使用带有多个GROUP BY的count（*）的SQL SELECT请求

Romain H. 发表于 Dev

罗曼·H

我目前正在实习，必须创建一个丢失的视图，这使网站无法正常工作。

该视图必须包含多个信息，还必须包含每个夫妇状态/过程的行数，如下所示：

 +----------------+---------+----------+-----------+-----------+--------+
 | status         | process | project  | idprocess | idproject | number |
 +----------------+---------+----------+-----------+-----------+--------+
 | Not applicable | AP      | tms-data |        17 |         2 |    432 |
 | Not applicable | AP+51   | tms-data |        18 |         2 |    432 |
 | Not applicable | AR      | tms-data |        19 |         2 |    432 |
 | Realized       | AP      | tms-data |        17 |         2 |    432 |
 | Realized       | AP+51   | tms-data |        18 |         2 |    432 |
 | Realized       | AR      | tms-data |        19 |         2 |    432 |
 | Safety         | AP      | tms-data |        17 |         2 |    432 |
 | Safety         | AP+51   | tms-data |        18 |         2 |    432 |
 | Safety         | AR      | tms-data |        19 |         2 |    432 |
 | Ticket         | AP      | tms-data |        17 |         2 |    432 |
 | Ticket         | AP+51   | tms-data |        18 |         2 |    432 |
 | Ticket         | AR      | tms-data |        19 |         2 |    432 |
 | To be designed | AP      | tms-data |        17 |         2 |    432 |
 | To be designed | AP+51   | tms-data |        18 |         2 |    432 |
 | To be designed | AR      | tms-data |        19 |         2 |    432 |
 | Validated      | AP      | tms-data |        17 |         2 |    432 |
 | Validated      | AP+51   | tms-data |        18 |         2 |    432 |
 | Validated      | AR      | tms-data |        19 |         2 |    432 |
 +----------------+---------+----------+-----------+-----------+--------+
 18 rows in set (0.03 sec)

我正在使用MySQL。我不明白为什么这样计算，有什么想法吗？

这是我到目前为止一直在使用的脚本：

 SELECT alstom_status.name AS status,
        alstom_process.name AS process,
        alstom_project.name AS project,
        alstom_process.idprocess AS idprocess,
        alstom_project.idproject AS idproject,
        count(*) AS number
 FROM alstom_rule INNER JOIN alstom_status ON idstatus
                  INNER JOIN alstom_project ON idproject
                  INNER JOIN alstom_process ON idprocess
 GROUP BY alstom_status.name, alstom_process.name
 ORDER BY process;

托菲克·阿拉兹基（Towfik Alrazihi）

This works always for me !

SELECT alstom_status.name AS status,
           alstom_process.name AS process,
           alstom_project.name AS project,
           alstom_process.idprocess AS idprocess,
           alstom_project.idproject AS idproject,
           count(*) AS number
    FROM alstom_rule INNER JOIN alstom_status ON idstatus
                     INNER JOIN alstom_project ON idproject
                     INNER JOIN alstom_process ON idprocess
    GROUP BY status, process,project,idprocess, idproject;

您可以使用（按alstom_status.name，alstom_process.name，alstom_project.name，alstom_process.idprocess，alstom_project.idproject分组）

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。