Mysql按照字段值做分组行转列查询

今天做个后台服务，有个需求是批量生成一批表的数据，如果用BulkInsert会提升很大一截提交效率，但是如果用循环构造提交的Datable，则算法开销太高，所以用这种查询批量查出符合格式的DataTable结果集。

*思想就是将查询列做CASE判断值，然后放在 SELECT段中作为别名列显示字段值，然后再根据ID分组。

下面是内层的原始查询效果:

下面是行转列后查询效果如图:

下面是上面两张图的最终查询语句:

 SELECT f_gameID,SUM(score) AS totNum,

     SUM(CASE f_sub WHEN '' THEN score ELSE  END) AS f_subject01,SUM(CASE f_sub WHEN '' THEN score ELSE  END) AS f_subject02,

     SUM(CASE f_sub WHEN '' THEN score ELSE  END) AS f_subject03,SUM(CASE f_sub WHEN '' THEN score ELSE  END) AS f_subject04,

     SUM(CASE f_sub WHEN '' THEN score ELSE  END) AS f_subject05,SUM(CASE f_sub WHEN '' THEN score ELSE  END) AS f_subject06,

     SUM(CASE f_sub WHEN '' THEN score ELSE  END) AS f_subject07,SUM(CASE f_sub WHEN '' THEN score ELSE  END) AS f_subject08,

     SUM(CASE f_sub WHEN '' THEN score ELSE  END) AS f_subject09

 FROM

 (

     SELECT u.f_gameID,u.f_sub,COUNT(u.f_sub) AS score

     FROM t_mfg_GameUserAnswers AS u

     LEFT JOIN t_mfg_Class AS c ON u.f_gameId = c.f_gameId

     WHERE u.f_startTime > '2015-07-20'

     AND u.f_endTime < '2015-07-24'

     -- AND c.f_schoolId =

     GROUP BY u.f_gameID ,u.f_sub

 ) AS a

 GROUP BY f_gameID;

巴特西

Mysql按照字段值做分组行转列查询

*思想就是将查询列做CASE判断值，然后放在 SELECT段中作为别名列显示字段值，然后再根据ID分组。

最新文章

热门文章