巧用MySQL的临时变量之二：如何做一个类似excel表单元格合并的效果

巧用MySQL的临时变量之二：如何做一个类似excel表单元格合并的效果？

财务一些变态的数据需求有时会让你很为难！数据导出后后期加工一下好像很简单，但是成千上万行数据时，人工处理就有点烦了，请看这样一个需求如何做？
一个单号有一个收入，可能有多个费用类型的成本。需要单号、收入显示在左边，多个成本显示在右边，要好像excel表的单元格合并，即左边相同单号在第二行显示为空。

加粉丝看
MySQL技巧系列之《未经证实的葵花宝典》：一篇可以当饭吃的MySQL文章,值得收藏

好吧，为了你，我连呼吸都反复练习。先创建二个测试表及添加一些数据。
DROP TABLE my_ic_table;
CREATE TABLE my_ic_table (
dh_id INT(4) NOT NULL COMMENT ‘单号ID’,
dh VARCHAR(50) NULL COMMENT ‘单号’,
ic_amt DECIMAL(13,2) NULL COMMENT ‘收入’,
PRIMARY KEY (dh_id)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT=‘测试用单号收入表’;

DROP TABLE my_pm_table;
CREATE TABLE my_pm_table (
auto_id INT(4) NOT NULL AUTO_INCREMENT COMMENT ‘明细表的流水ID’,
dh_id INT(4) NULL COMMENT ‘单号ID’,
amt_type VARCHAR(20) NULL COMMENT ‘费用类型’,
pm_amt DECIMAL(13,2) NULL COMMENT ‘成本’,
PRIMARY KEY (auto_id)
) ENGINE=INNODB AUTO_INCREMENT = 0 DEFAULT CHARSET=utf8 COMMENT=‘测试用单号成本表’;

INSERT INTO my_ic_table(dh_id, dh, ic_amt)
VALUES(1,‘9000’,100.00), (2,‘9001’,150.00);

INSERT INTO my_pm_table(dh_id, amt_type, pm_amt)
VALUES(1,‘A’,20.00), (1,‘B’,40.00), (2,‘A’,25.00), (2,‘B’,35.00), (2,‘C’,10.00);


数据准备好了，看看如何写脚本生成那个excel表？
SELECT
CASE WHEN @dh <> 单号 THEN @dh := IF(@rownum := 0, 单号, 单号) # 绝招！这样就可以顺便改变 @rownum 标志
ELSE @dh := IF(@rownum := 1, 单号, 单号)
END AS 单号,
IF(@rownum = 0, 收入, ‘’) 收入,
IFNULL(SUM(pm.pm_amt), 0) 成本, pm.amt_type 费用类型
FROM(
SELECT dh_id, dh 单号, ic_amt 收入
FROM my_ic_table
WHERE dh IN (‘9000’,‘9001’)
) Q
LEFT JOIN my_pm_table pm ON Q.dh_id = pm.dh_id
LEFT JOIN (SELECT @rownum := 0, @dh := ‘’) p ON 1=1
GROUP BY 单号, 费用类型
ORDER BY 单号, 费用类型;
#写得非常工整的缩进脚本，粘贴到了这里之后缩进也没了？

执行后效果如下图

妙！高！实在是高！

总结：灵活应用MySQL的临时变量，可以使脚本写出很多相当复杂的查询结果。本例中用临时变量@dh记录它的变化，从而控制另一个临时变量@rownum的 0 或1变化，最后通过 IF(@rownum = 0, 收入, ‘’) 收入 来决定单元格要不要显示0。