mysql - 如何在mysql中显示每一行的计数？

Question

结果我有下面这张表

   SELECT
    doctors.`name`,
    COUNT(`doctor-barge-naghs`.`code-naghs`) AS 'countEachDoctor'
    FROM
    doctors
    INNER JOIN `doctor-barge-naghs` ON `doctor-barge-naghs`.`code-doctor` = doctors.id
    GROUP BY doctors.`name`

我想计算 SUM 'countEachDoctor' 字段并将其显示在每一行旁边。

我做了这个

SELECT t1.*,(SELECT SUM(t1.countEachDoctor))
FROM(

SELECT
doctors.`name`,
COUNT(`doctor-barge-naghs`.`code-naghs`) AS 'countEachDoctor'
FROM
doctors
INNER JOIN `doctor-barge-naghs` ON `doctor-barge-naghs`.`code-doctor` = doctors.id
GROUP BY doctors.`name`) AS t1

这是我想要的，但不幸的是，它只显示一条记录，我需要所有记录。

标签： mysql

Answer 1

如果您不使用 mysql 8，则可以这样实现：

   SELECT
    doctors.`name`,
    COUNT(`doctor-barge-naghs`.`code-naghs`) AS 'countEachDoctor',
    (SELECT SUM(t.countEachDoctor)
    FROM (
      SELECT
       COUNT(`doctor-barge-naghs`.`code-naghs`) AS 'countEachDoctor'
      FROM doctors
      INNER JOIN `doctor-barge-naghs` ON `doctor-barge-naghs`.`code-doctor` = doctors.id
      GROUP BY doctors.`name`) t) AS sumCount
    FROM
    doctors
    INNER JOIN `doctor-barge-naghs` ON `doctor-barge-naghs`.`code-doctor` = doctors.id
    GROUP BY doctors.`name`