mysql - 动态场(总和)

我想动态进行查询，如TH2014 TH2015，...。

以下是我的查询

SELECT tgr.no_rptka
     , tgr.get_name_jabatan
     , SUM(CASE WHEN tgr.get_year_rptka = YEAR(tmr.date_rptka) THEN tgr.jumlah END) TH2014
     , SUM(CASE WHEN tgr.get_year_rptka = YEAR(tmr.date_rptka)+1 THEN tgr.jumlah END) TH2015
  FROM tbl_master_rptka tmr
     , tbl_get_rptka tgr
 WHERE tmr.no_rptka = tgr.no_rptka
 GROUP
    BY tgr.get_name_jabatan
     , tgr.no_rptka
 ORDER
    BY tgr.no_rptka,id_get ASC

我怎样才能做到这一点？

最佳答案

    SET @SQL = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(case when (tgr.get_year_rptka) = ''',
      dt,
      ''' then tgr.jumlah else 0 end) AS `',
      dt, '`'
    )
  ) INTO @SQL
FROM
(
  SELECT get_year_rptka AS dt
  FROM tbl_get_rptka tgr
  ORDER BY tgr.get_year_rptka
) d;

SET @SQL = CONCAT('SELECT tgr.no_rptka,tgr.get_name_jabatan, ', @SQL, '
            from tbl_master_rptka tmr
            inner join tbl_get_rptka tgr
              on tmr.no_rptka=tgr.no_rptka

            GROUP BY   tgr.get_name_jabatan, tgr.no_rptka
            ORDER by   tgr.no_rptka,tgr.get_name_jabatan,   id_get ASC;');

PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

关于mysql - 动态场(总和)，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/35128137/