Mysql 列转行 实例

 数据结构

抽奖箱表

CREATE TABLE `yoshop_shang_activity_box` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `activity_id` int(11) NOT NULL COMMENT '活动id',
  `number` int(11) NOT NULL COMMENT '活动下的箱子编号',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=112 DEFAULT CHARSET=utf8 COMMENT='抽奖箱表';

抽奖箱-奖品-中间表

CREATE TABLE `yoshop_shang_box_goods` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `box_id` int(11) NOT NULL COMMENT '抽奖箱id',
  `category_id` int(11) NOT NULL COMMENT '商品分类id',
  `goods_id` int(11) NOT NULL COMMENT '商品id',
  `total` int(11) NOT NULL COMMENT '对应商品在抽奖箱内的总数',
  `stock_num` int(11) NOT NULL COMMENT '对应商品在抽奖箱内的余数',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=332 DEFAULT CHARSET=utf8 COMMENT='抽奖箱-奖品-中间表';

 SQL语句

SELECT
	a.activity_id,
	a.number,
	b.box_id,
	b.category_id,
	b.goods_id,
	b.total,
	b.stock_num,
	MAX(
		CASE
		WHEN category_id = 1 THEN
			total
		END
	) AS A_total,
	MAX(
		CASE
		WHEN category_id = 1 THEN
			stock_num
		END
	) AS A_stock_num,
	MAX(
		CASE
		WHEN category_id = 2 THEN
			total
		END
	) AS B_total,
	MAX(
		CASE
		WHEN category_id = 2 THEN
			stock_num
		END
	) AS B_stock_num,
	MAX(
		CASE
		WHEN category_id = 3 THEN
			total
		END
	) AS C_total,
	MAX(
		CASE
		WHEN category_id = 3 THEN
			stock_num
		END
	) AS C_stock_num
FROM
	yoshop_shang_activity_box a
LEFT JOIN yoshop_shang_box_goods b ON b.box_id = a.id
WHERE
	a.activity_id = 4
GROUP BY
	box_id

效果