MySQL或PHP将行转换为列 [英] MySQL or PHP Transform rows to columns

问题描述

我有这种桌子:

月份是根据用户输入动态生成的.这个月也可以重复.但是，我想将月份值转换为具有金额值的列，但是当在mysql中动态尝试时，它不允许我执行，因为它只能返回不同的值.

the month are dynamically generated based on user input. The month can also be repeated. However, I want to transpose the month values to columns with a value of the amount but when trying it dynamically in mysql, it does not permit me because it can only return distinct values.

我还尝试从下面的代码中删除与众不同的代码，但是它不起作用.有什么想法吗?

I also tried removing distinct from my code below but it is not working. Any thoughts?

CREATE DEFINER=`root`@`localhost` PROCEDURE `test`()
BEGIN
SET group_concat_max_len=2048;
SET @sql = NULL;

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(month = ''',
      month,
      ''', amount, NULL)) AS ',
      month
    )
  ) INTO @sql
FROM tmp_results;

SET @sql = CONCAT('SELECT r.account, 
                           r.region, ', 
                           @sql, '
                   FROM tmp_results r
                   LEFT JOIN accounts AS a
                   on r.account_id = a.id
                   GROUP BY r.account');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END

试图在laravel 5中使用该集合，但仍然不执行任何操作，如果您对php有答复，我也欢迎它.我要花几天时间才能解决这个问题.

Tried to use the collection in laravel 5 and still does nothing, If you have answer for php, I welcome it also. I'm banging my head for days to solve this.

推荐答案

如果您也想为年份使用单独的列，则必须将年份(根据列date计算)添加到动态sql代码中:

If you want to have seperate columns for your years too, you have to add the year (calculated from your column date) to your dynamic sql code:

CREATE DEFINER=`root`@`localhost` PROCEDURE `test`()
BEGIN
  SET group_concat_max_len=2048;
  SET @sql = NULL;

  SELECT GROUP_CONCAT(DISTINCT CONCAT(
      'MAX(IF(month = ''',
      month,
      ''' and year(date) = ',
      year(date),
      ', amount, NULL)) AS `',
      month,
      '_',
      year(date),
      '`'
    )
    order by date
  ) INTO @sql
  FROM tmp_results;

  if coalesce(@sql,'') != '' then
    set @sql = concat(', ', @sql);
  end if; 

  SET @sql = CONCAT(
    'SELECT r.account, 
     r.region ',  
     coalesce(@sql,''),
    ' FROM tmp_results r
     LEFT JOIN accounts AS a
     on r.account_id = a.id
     GROUP BY r.account, r.region');

  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END

这些列将被命名为January_2017，并且我添加了order by date，否则它们通常是无序的.

The columns will be named like January_2017, and I added an order by date, otherwise they would usually be unordered.

我添加了group by r.region，否则，如果在服务器上启用了only_full_group_by(从MySQL 5.7开始的默认值)，它将不起作用.

I added a group by r.region, otherwise it will not work if only_full_group_by is enabled on your server (which is the default value starting with MySQL 5.7).

然后我为空表添加了一个测试(否则将导致错误).如果不需要它，而仅将我的代码的一部分复制到您的代码中，请注意与代码相比，在SET @sql = CONCAT('SELECT r.account, r.region '中的r.region之后缺少逗号，您可能需要再次添加.

And I added a test for empty tables (which would otherwise result in an error). If you don't need it and copy only parts of my code into yours, be aware of the missing comma after r.region in SET @sql = CONCAT('SELECT r.account, r.region ' compared to your code, you might have to add it again.

由于每个月的代码长度约为80，因此您可能必须增加group_concat_max_len以适合最大的查询.

Since the code for each month has a length of about 80, you might have to increase group_concat_max_len to fit your largest possible query.

这篇关于MySQL或PHP将行转换为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！