将交叉表查询从ms-access转换为mysql [英] Convert a cross tab query from ms-access to mysql

问题描述

我使用ms-Access生成查询，我想在MYSQL中使用它，但是MYSQL不支持某些ms-Access函数:

I use ms-Access to generate a query and I want to use it in MYSQL, but some ms-Access functions are not supported by MYSQL:

我想转换为MYSQL的ms-Access查询是:

My ms-Access query that I want to convert to MYSQL is :

 TRANSFORM Count(s1.s1_ID) AS Comps1_ID
SELECT s1.s1_ID, s1.Name
FROM s1 INNER JOIN eq ON s1.s1_ID = eq.s1_ID
GROUP BY s1.s1_ID, s1.Name
PIVOT eq.Type;

有我的桌子

有数据:

这是我的输出:

推荐答案

这是您需要在mysql中使用动态sql进行的方式

Here is the way you need to do in mysql using dynamic sql

set @sql = null;
select
  group_concat(distinct
    concat(
      'sum(case when eq.type = ''',
      eq.type,
      ''' then 1 else 0 end) AS ',
     concat('`',eq.type,'`')
    )
  ) into @sql
from eq ;

set @sql = concat('select s1.s1_id, s1.name, ', @sql, ' from s1 
                  join eq  on eq.s1_id = s1.s1_id
                  group by s1.s1_id
');

prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;

演示

Demo

这篇关于将交叉表查询从ms-access转换为mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！