mysql - "Crosstab"仅查询显示最新结果-6ren

mysql - "Crosstab"仅查询显示最新结果

转载作者：行者123 更新时间：2023-11-29 15:30:24

我在 MySQL 中设置了一个查询，以返回结果，其中月份作为列标题，培训师姓名作为行标题。我正在尝试按月确定培训师的 NPS 分数。当我运行查询时，它仅显示上个月有调查结果的结果。如何修复查询以显示他们每个月的调查分数结果？

基本表结构:

CREATE TABLE nh_survey (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  trainer TINYINT(1),
  trainer_nps TINYINT(1),
  class_Date DATE
);
INSERT INTO nh_survey
  (trainer,trainer_nps,class_Date)
VALUES
  ('''1''',' ''10''',' ''2019-10-21'''),
  ('''1''',' ''10''',' ''2019-10-21'''),
  ('''1''',' ''10''',' ''2019-10-21'''),
  ('''1''',' ''10''',' ''2019-10-21'''),
  ('''1''',' ''10''',' ''2019-10-25'''),
  ('''1''',' ''10''',' ''2019-10-21'''),
  ('''1''',' ''10''',' ''2019-10-21'''),
  ('''3''',' ''10''',' ''2019-10-14'''),
  ('''3''',' ''10''',' ''2019-10-14'''),
  ('''3''',' ''10''',' ''2019-10-14'''),
  ('''3''',' ''10''',' ''2019-10-14'''),
  ('''3''',' ''10''',' ''2019-10-14'''),
  ('''15''',' ''10''',' ''2019-10-07'''),
  ('''15''',' ''10''',' ''2019-10-07'''),
  ('''15''',' ''10''',' ''2019-10-07'''),
  ('''15''',' ''10''',' ''2019-10-07'''),
  ('''15''',' ''10''',' ''2019-10-07'''),
  ('''15''',' ''7''',' ''2019-10-07'''),
  ('''16''',' ''10''',' ''2019-10-07'''),
  ('''15''',' ''10''',' ''2019-10-07'''),
  ('''15''',' ''10''',' ''2019-10-07'''),
  ('''15''',' ''9''',' ''2019-10-07'''),
  ('''15''',' ''10''',' ''2019-10-07'''),
  ('''15''',' ''10''',' ''2019-10-07'''),
  ('''15''',' ''10''',' ''2019-10-07'''),
  ('''19''',' ''10''',' ''2019-09-30'''),
  ('''19''',' ''10''',' ''2019-09-30'''),
  ('''19''',' ''10''',' ''2019-09-30'''),
  ('''19''',' ''10''',' ''2019-09-30'''),
  ('''19''',' ''10''',' ''2019-09-30'''),
  ('''19''',' ''10''',' ''2019-09-30'''),
  ('''19''',' ''10''',' ''2019-09-30'''),
  ('''19''',' ''10''',' ''2019-09-30'''),
  ('''19''',' ''10''',' ''2019-10-04'''),
  ('''19''',' ''10''',' ''2019-09-30'''),
  ('''19''',' ''10''',' ''2019-09-30'''),
  ('''20''',' ''10''',' ''2019-09-23'''),
  ('''20''',' ''10''',' ''2019-09-23'''),
  ('''20''',' ''8''',' ''2019-09-23'''),
  ('''20''',' ''10''',' ''2019-09-23'''),
  ('''20''',' ''10''',' ''2019-09-23'''),
  ('''20''',' ''10''',' ''2019-09-23'''),
  ('''20''',' ''6''',' ''2019-09-23'''),
  ('''20''',' ''10''',' ''2019-09-23'''),
  ('''15''',' ''10''',' ''2019-09-26'''),
  ('''15''',' ''10''',' ''2019-08-16'''),
  ('''21''',' ''10''',' ''2019-09-16'''),
  ('''21''',' ''10''',' ''2019-09-16'''),
  ('''21''',' ''10''',' ''2019-09-20'''),
  ('''21''',' ''10''',' ''1992-10-07'''),
  ('''21''',' ''10''',' ''2019-09-16'''),
  ('''21''',' ''10''',' ''2019-09-16'''),
  ('''21''',' ''10''',' ''2019-09-16'''),
  ('''21''',' ''10''',' ''2019-09-16'''),
  ('''21''',' ''10''',' ''2019-09-16'''),
  ('''21''',' ''10''',' ''2019-09-16'''),
  ('''21''',' ''10''',' ''2019-09-16'''),
  ('''21''',' ''10''',' ''2019-09-16'''),
  ('''21''',' ''10''',' ''2019-09-20'''),
  ('''19''',' ''10''',' ''2019-09-09'''),
  ('''19''',' ''10''',' ''2019-09-09'''),
  ('''19''',' ''10''',' ''2019-09-09'''),
  ('''19''',' ''10''',' ''2019-09-10'''),
  ('''19''',' ''10''',' ''2019-09-09'''),
  ('''19''',' ''10''',' ''2019-09-09'''),
  ('''19''',' ''10''',' ''2019-09-09'''),
  ('''19''',' ''10''',' ''2019-09-09'''),
  ('''19''',' ''10''',' ''2019-09-09'''),
  ('''19''',' ''10''',' ''2019-09-09'''),
  ('''19''',' ''10''',' ''2019-09-09'''),
  ('''19''',' ''10''',' ''2019-09-09'''),
  ('''20''',' ''10''',' ''2019-09-03'''),
  ('''20''',' ''10''',' ''2019-09-03'''),
  ('''20''',' ''10''',' ''2019-09-03'''),
  ('''20''',' ''10''',' ''2019-09-03'''),
  ('''20''',' ''10''',' ''2019-09-03'''),
  ('''20''',' ''10''',' ''2019-09-03'''),
  ('''20''',' ''10''',' ''2019-09-03'''),
  ('''20''',' ''10''',' ''2019-09-03'''),
  ('''16''',' ''10''',' ''2019-08-19'''),
  ('''20''',' ''10''',' ''2019-08-26'''),
  ('''20''',' ''10''',' ''2019-08-26'''),
  ('''20''',' ''10''',' ''2019-08-26'''),
  ('''20''',' ''10''',' ''2019-08-26'''),
  ('''20''',' ''10''',' ''2019-08-26'''),
  ('''20''',' ''10''',' ''2019-08-26'''),
  ('''15''',' ''10''',' ''2019-07-22'''),
  ('''16''',' ''10''',' ''2019-08-19'''),
  ('''15''',' ''8''',' ''2019-08-23'''),
  ('''16''',' ''10''',' ''2019-08-19'''),
  ('''15''',' ''10''',' ''2019-08-19'''),
  ('''15''',' ''10''',' ''2019-08-19'''),
  ('''15''',' ''10''',' ''2019-08-19'''),
  ('''15''',' ''10''',' ''2019-08-19'''),
  ('''15''',' ''10''',' ''2019-08-19'''),
  ('''15''',' ''10''',' ''2019-08-19'''),
  ('''15''',' ''10''',' ''2019-08-19'''),
  ('''15''',' ''10''',' ''2019-08-19'''),
  ('''1''',' ''10''',' ''2019-08-05'''),
  ('''1''',' ''10''',' ''2019-08-05''');

CREATE TABLE trainers (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  revname VARCHAR(255)
);
INSERT INTO trainers
  (id,revname)
VALUES
  (1,'Trainer 1'),
  (2,'Trainer 2'),
  (3,'Trainer 3'),
  (4,'Trainer 4'),
  (6,'Trainer 5'),
  (7,'Trainer 6'),
  (8,'Trainer 7'),
  (9,'Trainer 8'),
  (10,'Trainer 9'),
  (11,'Trainer 10'),
  (12,'Trainer 11'),
  (13,'Trainer 12'),
  (15,'Trainer 13'),
  (16,'Trainer 14'),
  (18,'Trainer 15'),
  (19,'Trainer 16'),
  (20,'Trainer 17'),
  (21,'Trainer 18'),
  (22,'Trainer 19'),
  (23,'Trainer 20'),
  (24,'Trainer 21');

这是查询:

select t1.revname AS 'Trainer',
 IF( MONTH( t2.class_date) =12, round(sum(t2.npscore)/count(t2.npscore)*100, 1 ),0)  AS 'Dec',
 IF( MONTH( t2.class_date) =1, round(sum(t2.npscore)/count(t2.npscore)*100, 1 ),0)  AS 'Jan',
 IF( MONTH( t2.class_date) =2, round(sum(t2.npscore)/count(t2.npscore)*100, 1 ),0)  AS 'Feb',
 IF( MONTH( t2.class_date) =3, round(sum(t2.npscore)/count(t2.npscore)*100, 1 ),0)  AS 'Mar',
 IF( MONTH( t2.class_date) =4, round(sum(t2.npscore)/count(t2.npscore)*100, 1 ),0)  AS 'Apr',
 IF( MONTH( t2.class_date) =5, round(sum(t2.npscore)/count(t2.npscore)*100, 1 ),0)  AS 'May',
 IF( MONTH( t2.class_date) =6, round(sum(t2.npscore)/count(t2.npscore)*100, 1 ),0)  AS 'Jun',
 IF( MONTH( t2.class_date) =7, round(sum(t2.npscore)/count(t2.npscore)*100, 1 ),0)  AS 'Jul',
 IF( MONTH( t2.class_date) =8, round(sum(t2.npscore)/count(t2.npscore)*100, 1 ),0)  AS 'Aug',
 IF( MONTH( t2.class_date) =9, round(sum(t2.npscore)/count(t2.npscore)*100, 1 ),0)  AS 'Sep',
 IF( MONTH( t2.class_date) =10, round(sum(t2.npscore)/count(t2.npscore)*100, 1 ),0)  AS 'Oct',
 IF( MONTH( t2.class_date) =11, round(sum(t2.npscore)/count(t2.npscore)*100, 1 ),0)  AS 'Nov'
from
(select id, revname
from trainers
where id in(1,2,3,15,16,18,19,20,21)) t1
LEFT JOIN
(SELECT 
    trainer, 
    class_date,     
        CASE
                WHEN trainer_nps IN (9 , 10) THEN 1
                WHEN trainer_nps IN (7 , 8) THEN 0
                ELSE - 1
            END AS npscore            
    FROM
        nh_survey
        Where class_date between '2018-01-28' and '2019-11-30') t2 on t1.id = t2.trainer
group by t1.revname
order by t1.id ASC

如果我仅按培训师进行分组，它只会向我提供培训师有调查结果的最后一个月。如果我将 class_date 添加到分组中，它会为每个训练器提供 12 行。

最佳答案

我必须稍微调整一下你的数据。有了这个数据

  CREATE TABLE nh_survey (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    trainer TINYINT(1),
    trainer_nps TINYINT(1),
    class_Date DATE
  );
  INSERT INTO nh_survey
    (trainer,trainer_nps,class_Date)
  VALUES
    ('1','10','2019-10-21'),
    ('1',' 10','2019-10-21'),
    ('1','10','2019-10-21'),
    ('1','10','2019-10-21'),
    ('1','10','2019-10-25'),
    ('1','10','2019-10-21'),
    ('1','10','2019-10-21'),
    ('3','10','2019-10-14'),
    ('3','10','2019-10-14'),
    ('3','10','2019-10-14'),
    ('3','10','2019-10-14'),
    ('3','10','2019-10-14'),
    ('15','10','2019-10-07'),
    ('15','10','2019-10-07'),
    ('15','10','2019-10-07'),
    ('15','10','2019-10-07'),
    ('15','10','2019-10-07'),
    ('15','7','2019-10-07'),
    ('16','10','2019-10-07'),
    ('15','10','2019-10-07'),
    ('15','10','2019-10-07'),
    ('15','9','2019-10-07'),
    ('15','10','2019-10-07'),
    ('15','10','2019-10-07'),
    ('15','10','2019-10-07'),
    ('19','10','2019-09-30'),
    ('19','10','2019-09-30'),
    ('19','10','2019-09-30'),
    ('19','10','2019-09-30'),
    ('19','10','2019-09-30'),
    ('19','10','2019-09-30'),
    ('19','10','2019-09-30'),
    ('19','10','2019-09-30'),
    ('19','10','2019-10-04'),
    ('19','10','2019-09-30'),
    ('19','10','2019-09-30'),
    ('20','10','2019-09-23'),
    ('20','10','2019-09-23'),
    ('20','8','2019-09-23'),
    ('20','10','2019-09-23'),
    ('20','10','2019-09-23'),
    ('20','10','2019-09-23'),
    ('20','6','2019-09-23'),
    ('20','10','2019-09-23'),
    ('15','10','2019-09-26'),
    ('15','10','2019-08-16'),
    ('21','10','2019-09-16'),
    ('21','10','2019-09-16'),
    ('21','10','2019-09-20'),
    ('21','10','1992-10-07'),
    ('21','10','2019-09-16'),
    ('21','10','2019-09-16'),
    ('21','10','2019-09-16'),
    ('21','10','2019-09-16'),
    ('21','10','2019-09-16'),
    ('21','10','2019-09-16'),
    ('21','10','2019-09-16'),
    ('21','10','2019-09-16'),
    ('21','10','2019-09-20'),
    ('19','10','2019-09-09'),
    ('19','10','2019-09-09'),
    ('19','10','2019-09-09'),
    ('19','10','2019-09-10'),
    ('19','10','2019-09-09'),
    ('19','10','2019-09-09'),
    ('19','10','2019-09-09'),
    ('19','10','2019-09-09'),
    ('19','10','2019-09-09'),
    ('19','10','2019-09-09'),
    ('19','10','2019-09-09'),
    ('19','10','2019-09-09'),
    ('20','10','2019-09-03'),
    ('20','10','2019-09-03'),
    ('20','10','2019-09-03'),
    ('20','10','2019-09-03'),
    ('20','10','2019-09-03'),
    ('20','10','2019-09-03'),
    ('20','10','2019-09-03'),
    ('20','10','2019-09-03'),
    ('16','10','2019-08-19'),
    ('20','10','2019-08-26'),
    ('20','10','2019-08-26'),
    ('20','10','2019-08-26'),
    ('20','10','2019-08-26'),
    ('20','10','2019-08-26'),
    ('20','10','2019-08-26'),
    ('15','10','2019-07-22'),
    ('16','10','2019-08-19'),
    ('15','8','2019-08-23'),
    ('16','10','2019-08-19'),
    ('15','10','2019-08-19'),
    ('15','10','2019-08-19'),
    ('15','10','2019-08-19'),
    ('15','10','2019-08-19'),
    ('15','10','2019-08-19'),
    ('15','10','2019-08-19'),
    ('15','10','2019-08-19'),
    ('15','10','2019-08-19'),
    ('1','10','2019-08-05'),
    ('1','10','2019-08-05');

  CREATE TABLE trainers (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    revname VARCHAR(255)
  );
  INSERT INTO trainers
    (id,revname)
  VALUES
    (1,'Trainer 1'),
    (2,'Trainer 2'),
    (3,'Trainer 3'),
    (4,'Trainer 4'),
    (6,'Trainer 5'),
    (7,'Trainer 6'),
    (8,'Trainer 7'),
    (9,'Trainer 8'),
    (10,'Trainer 9'),
    (11,'Trainer 10'),
    (12,'Trainer 11'),
    (13,'Trainer 12'),
    (15,'Trainer 13'),
    (16,'Trainer 14'),
    (18,'Trainer 15'),
    (19,'Trainer 16'),
    (20,'Trainer 17'),
    (21,'Trainer 18'),
    (22,'Trainer 19'),
    (23,'Trainer 20'),
    (24,'Trainer 21');

这个选择语句

  SELECT MAX(Trainer) Trainer
   ,SUM(IF( monthnb =1, score, 0 ))  AS 'Jan'
   ,SUM(IF( monthnb =2, score,0 ))  AS 'Feb'
   ,SUM(IF( monthnb =3, score,0 ))  AS 'Mar'
   ,SUM(IF( monthnb =4, score, 0 ))  AS 'Apr'
   ,SUM(IF( monthnb =5, score,0 ))  AS 'May'
   ,SUM(IF( monthnb =6, score,0 ))  AS 'Jun'
   ,SUM(IF( monthnb =7, score, 0 ))  AS 'Jul'
   ,SUM(IF( monthnb =8, score,0 ))  AS 'Aug'
   ,SUM( IF( monthnb =9, score,0 ))  AS 'Sep'
   ,SUM(IF( monthnb =10, score, 0 ))  AS 'Oct'
   ,SUM(IF( monthnb =11, score,0 ))  AS 'Nov'
   ,SUM(IF( monthnb =12, score,0 ))  AS 'Dec'
  FROM
  (
    SELECT id,revname AS 'Trainer'
  ,MONTH(class_date)  monthnb
  ,round(sum(npscore)/count(npscore)*100) score
  FROM
  (select id, revname
  from trainers
  where id in(1,2,3,15,16,18,19,20,21)) t1
  LEFT JOIN
  (SELECT 
      trainer, 
      class_date,     
          CASE
                  WHEN trainer_nps IN (9 , 10) THEN 1
                  WHEN trainer_nps IN (7 , 8) THEN 0
                  ELSE - 1
              END AS npscore            
      FROM
          nh_survey
          Where class_date between '2018-01-28' and '2019-11-30') t2 on t1.id = t2.trainer    
   GROUP BY id,revname, MONTH(class_date) 
   ) tr1
GROUP BY id;

将给出以下结果

Trainer      | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec:----------- | --: | --: | --: | --: | --: | --: | --: | --: | --: | --: | --: | --:Trainer 1    |   0 |   0 |   0 |   0 |   0 |   0 |   0 | 100 |   0 | 100 |   0 |   0Trainer 3    |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 | 100 |   0 |   0Trainer 13   |   0 |   0 |   0 |   0 |   0 |   0 | 100 |  90 | 100 |  92 |   0 |   0Trainer 14   |   0 |   0 |   0 |   0 |   0 |   0 |   0 | 100 |   0 | 100 |   0 |   0Trainer 16   |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 | 100 | 100 |   0 |   0Trainer 17   |   0 |   0 |   0 |   0 |   0 |   0 |   0 | 100 |  81 |   0 |   0 |   0Trainer 18   |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 | 100 |   0 |   0 |   0Trainer 2    |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0Trainer 15   |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0

参见 dbfiddle 示例 https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=ac5c062b818171cf47ddb16bec81ff99

关于mysql - "Crosstab"仅查询显示最新结果，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/58773490/

文章推荐： MySQL Workbench 逆向工程与导出数据

文章推荐： python - 从 MySQL 到 CSV 格式

文章推荐： android - 在命令行中关闭android应用程序

文章推荐： python - 我在运行 'sudo apt autoremove python' 时删除了数据库

python - 如何对 pandas crosstab 中的行求和并制作一个新的 crosstab？
我有来自 Excel 工作表的数据，我在 Pandas 交叉表中进行了汇总。我想通过对相关行求和来进一步对数据进行分类。这是我的交叉表: class_of_orbit Ellipti
Mysql Crosstab 验证存在性
我是 stackoverflow 的新手，我在尝试做这个主题时遇到了一些问题。报告。表格示例 Group Client AtributeGroup1 Client1 Atribute1Gro
MySQL CrossTab 并删除重复项
我在 MySQL 中有一个表，其中记录了学生注册的每一年的信息。我想生成一个查询，显示 yyyy 出生的 nnn 名学生，学年结束为 aaaa。我很快就完成了该查询，但令我困惑的是数据库中有一些重复的
mysql - "Crosstab"仅查询显示最新结果
我在 MySQL 中设置了一个查询，以返回结果，其中月份作为列标题，培训师姓名作为行标题。我正在尝试按月确定培训师的 NPS 分数。当我运行查询时，它仅显示上个月有调查结果的结果。如何修复查询以显示他
sql - crosstab() 查询移动列
我在 postgres 中使用交叉表函数。基本 SQL 是: select distinct o_location, co_name, o_date,
MySQL CrossTab 结果包含非不同列
两部分问题: 在 MySql 中生成交叉表结果时，Distinct 调用似乎不起作用...或者我遗漏了其他内容？我在几列中得到相同的 ClassName 。即“MDC(简介)”。 : SQLFiddl
php - 如何在html和php中制作PIVOT(Crosstab)
如果有人能解决这个问题，那对我来说将是一个很大的帮助。我有一个 MySql 数据库表，其中包含以下记录: student_id subject_id scores s01
Java Crosstab - 准备语句查询
我有一个带有静态参数的典型交叉表查询。它适用于 createStatement。我想使用 preparestatement 来查询。 String query = "SELECT * FROM cro
sas - 相当于SAS中的Access Crosstab Query？
这是我的输入: 身份证颜色 1个绿色 1 红色 1个橙子 1个绿色 1 红色 2红 2红 2 蓝色 3绿色 3红这是我在输出中想要的 - 每种颜色的 ID 记录计数: ID绿红橙蓝 1 2 2 1
python pandas crosstab 和积方阵
我正在寻求帮助，使用类似于 sumproduct 的函数从现有数据帧创建子数据帧。我想使用列标题将此表转换为一个小总和乘积: dan ste bob t1 0 2 0 t2 2
postgresql - 排序查询 Crosstab Postgres
我正在尝试在 Postgres 中旋转一个表。我的表“示例”有很多列(代码、部门、项目、年份、期间、值、预购)，而不是每行有 1 个概念、1 年、1 个值，我想要按年显示值的概念。从此； Item
sql - 过滤对具有多个值的 crosstab() 查询结果的意外影响
我有一个 crosstab()查询类似于我上一个问题中的查询: Unexpected effect of filtering on result from crosstab() query 常见的情况
sql - 过滤对 crosstab() 查询结果的意外影响
我有一个 crosstab() 查询，如下所示: SELECT * FROM crosstab( 'SELECT row_name, extra1, extra2..., another_table
sql - 连接来自另一个查询的 crosstab() 查询的返回类型
我不能在这里发布我的原始查询，它太复杂了。所以我用一个示例查询来解释我的问题: 工作枢轴查询: Select pivot.* from public.crosstab($$ select 'na' a
postgresql - 用于可变列数查询的 Postgres CrossTab
上下文我正在为一个站点运行车辆路径问题的变体。布局是这样的。您有取货点、卡车和正在取货的元素。真正的诀窍是客户一天的轮类次数不同，因此这是可定制的。所以我会在运行时知道这一点，但不会提前。我正在尝试
postgresql - 为什么这个 crosstab() 查询返回重复键？
我有下表sample_events: Column | Type --------+----- title | text date | date 具有值: title | dat
sql - crosstab postgres - 行到列
在我的项目中，数据模型发生了变化，我必须执行一个新的请求来获取信息，但在过去看起来所有数据都在同一行中，但现在部分数据在另一个表中，它返回我的专栏。所以我的请求返回: 我认为这很简单，但我真的不明白
mysql - SQL Crosstab 月行作为列？
早上好，我尝试使用 mysql/php 创建报告失败，我希望将行作为类似于 Access Crosstab 或 Excel Pivot 的列。我有销售人员和月销售额，我想在整个页面上显示，月份作为列标
python - pandas.crosstab 切片加上总计
我目前真的在为数据框而苦苦挣扎。通过运行代码:(res_sum = 数据框的名称) summary_table = pd.crosstab(index=[res_sum["Type"],res_sum
python - pandas.crosstab 中缺少数据
我正在用 pandas 制作一些交叉表: a = np.array(['foo', 'foo', 'foo', 'bar', 'bar', 'foo', 'foo'], dtype=object) b

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

mysql - "Crosstab"仅查询显示最新结果