时间戳

首页 > 解决方案 > 如何显示记录数

mysql - 如何显示记录数

问题描述

我有以下数据

   ReasonId   Team   Division Location
     2          A                 L1
     3          B        D1       L2
     2          A        D2       L1 
     2          A        D3       L3

我想为每个团队、部门和位置显示按ReasonId分组的计数。在某些情况下，除法可能为 null。

我正在尝试这样的事情，

  SELECT 
    COUNT(*) AS TotalRequests, Reason, team
FROM
    reports
GROUP BY Reason , team
UNION SELECT 
    COUNT(*) AS TotalRequests, Reason, location
FROM
    reports
GROUP BY Reason , location 
UNION SELECT 
    COUNT(*) AS TotalRequests, Reason, division
FROM
    reports
WHERE
    ISNULL(division) = 0
GROUP BY Reason , division
;

我得到的上述输出是，

   TotalRequests    Reason  team
        1             2 
        3             2      A
        1             3      B
        1             3     D1
        1             2     D2
        1             2     D3
        2             2     L1
        1             3     L2
        1             2     L3

有没有可能得到一个看起来像这样的输出，

ReasonId   Team  TotalByTeam   Location TotalByLocation  Division TotalByDivision
  2         A       3             L1          2                          0
  2         A       3             L3          1              D2          1
  2         A       3             L3          1              D3          1
  3         B       1             L2          1              D1          1

我正在使用mysql 8.0.17 这是一个示例模式和相同的 dbfiddle

CREATE TABLE `reports` (
  `Reason` int(11) DEFAULT NULL,
  `Team` text,
  `Division` text,
  `Location` text
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



    INSERT INTO reports (Reason,Team,Division,Location) values (2,  'A',null,'L1');
    INSERT INTO reports (Reason,Team,Division,Location) values (3,  'A','D1','L2');
    INSERT INTO reports (Reason,Team,Division,Location) values (2,  'A','D2','L1');
    INSERT INTO reports (Reason,Team,Division,Location) values (2,  'A','D3','L3');

标签： mysqlsql

解决方案

您应该为此使用分析函数COUNT(...) OVER (...)。从 8.0 版开始，它们在 MySQL 中可用。

select
  reasonid,
  team,
  count(team) over (partition by team) as total_by_team,
  location,
  count(location) over (partition by location) as total_by_location,
  division,
  count(division) over (partition by division) as total_by_division
from reports;

演示：https ://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=79891554331e8222041ec34eea3fc4ee

推荐阅读