巧用MySQL的临时变量之三:如何做一个类似excel表单元格合并要求更复杂的查询
一个查询报表要求如下图:
这个需求,看似与前一博文的差不多,但却复杂了很多,其中的坑,你不踩过一次,你死活都不知道原来如此。
原表的Excel行如何导入到MySQL表中,这里不讨论。
DROP TABLE my_test_table
;
CREATE TABLE my_test_table
(
auto_id
INT(4) NOT NULL AUTO_INCREMENT COMMENT ‘流水ID’,
dh
VARCHAR(50) NULL COMMENT ‘单号’,
amt_type
VARCHAR(20) NULL COMMENT ‘费用类型’,
amt
DECIMAL(13,2) NULL COMMENT ‘金额’,
PRIMARY KEY (auto_id
)
) ENGINE=INNODB AUTO_INCREMENT = 0 DEFAULT CHARSET=utf8 COMMENT=‘我的测试用表’;
INSERT INTO my_test_table(dh, amt_type, amt)
VALUES(‘9000’,‘A’,20.00), (‘9000’,‘B’,40.00), (‘9001’,‘A’,25.00), (‘9001’,‘B’,35.00), (‘9001’,‘C’,10.00), (‘9002’,‘D’,55.00);
数据准备好了。
查询结果的思路是先做第一个效果,不考虑什么合并问题。
– 先完成第一步
SELECT mt.dh 单号, mt.amt_type 费用类型, mt.amt 金额
FROM(
SELECT dh, COUNT(*) 行数
FROM my_test_table
GROUP BY dh
ORDER BY 行数 DESC
) m
LEFT JOIN my_test_table mt ON m.dh = mt.dh
ORDER BY m.行数 DESC, 单号, 费用类型;
感觉这第一步这样可以了。
第二步去掉一些行。
– 再改装第一步,结果傻眼了
SELECT – mt.dh 单号,
CASE WHEN @dh <> mt.dh THEN @dh := mt.dh
ELSE ‘’
END AS 单号,
mt.amt_type 费用类型, mt.amt 金额
FROM(
SELECT dh, COUNT(*) 行数
FROM my_test_table
GROUP BY dh
ORDER BY 行数 DESC
) m
LEFT JOIN my_test_table mt ON m.dh = mt.dh
LEFT JOIN (SELECT @dh := ‘’) p ON 1=1
ORDER BY m.行数 DESC, 单号, 费用类型;
结果傻眼了,怎么不按我的排序来显示的?怎么写才好呢?互动一下…待续。
原文再续,书接上一回。
上回说到排序是错误的,其实就是使用临时变量时要注意的地方,就是这些坑你应该知道,不然还是要踩一次,所谓的高手,都是通过无数次的踩坑踩出来的!
使用临时变量读取行间数据,你就不能在读的时候排序还在变化!因此排序好之后就不要再变。
– 正确的写法
– 正确的写法
SELECT
CASE WHEN @dh <> Q.单号 THEN @dh := Q.单号
ELSE ‘’
END AS 单号,
Q.费用类型, Q.金额
FROM(
SELECT mt.dh 单号, mt.amt_type 费用类型, mt.amt 金额
FROM(
SELECT dh, COUNT(*) 行数
FROM my_test_table
GROUP BY dh
ORDER BY 行数 DESC
) m
LEFT JOIN my_test_table mt ON m.dh = mt.dh
ORDER BY m.行数 DESC, 单号, 费用类型
) Q
LEFT JOIN (SELECT @dh := ‘’) p ON 1=1;
妙!高!实在是高!
总结:在MySQL中使用临时变量读取行间数据,不能在读的时候排序还在变化!否则排序会乱。