MySQL查询:change table to another manifestations

Question

我的查询:

select Fimp_date, Fadpos, sum(Fplay_num) as Fplay_num from tbl_cnt_display_num_by_adpos where Fimp_date between date_format(now(),'%Y%m%d')-interval 2 day and date_format(now(),'%Y%m%d') group by Fadpos, Fimp_date;

这是结果:+------------+--------+------------+

| Fimp_日期 |法德波斯 | Fplay_num |

+------------+--------+------------+

| 20151020 | 0 | 1562156 |

| 20151020 | 1 | 19354|

| 20151020 | 101 | 101 352796 | 352796

| 20151020 | 102 | 102 232052 | 232052

| 20151020 | 103 | 103 42324 |

| 20151020 | 104 | 104 214 | 214

| 20151020 | 105 | 105 167904 |

| 20151020 | 500 | 500 49 | 49

+------------+--------+------------+

如何编写sql让结果转换为:

表示每天为一行，不同的Fadpos为不同的列，值为当天Fadpos的Fplay_num。

那么sql怎么写呢？

Answer 1

尝试以下操作...

SET @sql = NULL;

SELECT GROUP_CONCAT(DISTINCT CONCAT(
      'MAX(CASE WHEN `Fadpos` = ''',
      `Fadpos`,
      ''' THEN Fplay_num END) AS `',
      `Fadpos`, '`'
    )
  ) INTO @sql
FROM (SELECT Fadpos,
             SUM( Fplay_num ) AS Fplay_num
      FROM tbl_cnt_display_num_by_adpos
      WHERE Fimp_date BETWEEN DATE_FORMAT( NOW(), '%Y%m%d' ) - INTERVAL 2 DAY
                          AND DATE_FORMAT( NOW(), '%Y%m%d' )
      GROUP BY Fadpos,
               Fimp_date) AS AliasForSelect;

SET @sql = CONCAT( 'SELECT Fimp_date,
                           ',
                           @sql,
                           '
                    FROM tbl_cnt_display_num_by_adpos
                    GROUP BY Fimp_date;');

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

请参阅我的回答 How to match column names and values from joining and related tables?了解更多信息。

请注意，我无法测试此代码，因为我需要创建一个示例表并用数据填充它来执行此操作。如果您有可以执行此操作的脚本，请修改您的问题。如果做不到这一点，编辑您的问题以包含输出的文本副本而不是当前图像会很有帮助。

祝你好运!