静态行转列简单一点。动态行转列就需要涉及到存储过程。
需要统计每个班下了多少套秋装和冬装,但由于有的学校可能还下了夏装,每个学校下的款式不一样,所以这个列是动态的
所以建一个视图
如图:
需要把这个行转为列
如下存储函数
BEGIN
SET @sql = NULL;
SET @ssq = NULL;
SELECT
GROUP_CONCAT(DISTINCT CONCAT('SUM(IF(sx.sku_id = \'',
sx.sku_id,
'\', sx.num, 0)) AS \'',
sx.sku_name,
'\' '))
INTO @sql FROM
forskuview sx WHERE sx.main_id=order_id;
SET @ssq= CONCAT(' SELECT sx.GradeName as "年级",sx.ClassName as "班级",',@sql,' from forskuview sx where sx.main_id=\'',order_id,'\' GROUP BY sx.GradeName,sx.ClassName order by (sx.GradeID+0),(sx.ClassName+0)');
PREPARE stmt FROM @ssq;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
运行结果