2020-08-12 sql对银行订单汇总并在汇总行下面显示明细信息（两种拙劣的解决方法）

现有一张订单表（样例数据，生产环境下，表结构要复杂许多），下图这个样子：

现在想要把其中的银行做汇总，并且把该银行的所有订单都显示在下面。就是这个样子了：

做法我想了两种，一种就是使用union

先查出来所有的公司，然后每个公司做成一个小表，然后把他们union起来

代码如下：

SELECT
	'0' AS dingdan_num,
	yinhang AS namee,
	SUM( jine ) 
FROM
	dingdan 
WHERE
	yinhang = '汉口银行' UNION ALL
SELECT
	dingdan_num,
	gongsi AS namee,
	SUM( jine ) 
FROM
	dingdan 
WHERE
	yinhang = '汉口银行' 
GROUP BY
	yinhang,
	dingdan_num UNION ALL
SELECT
	'0' AS dingdan_num,
	yinhang AS namee,
	SUM( jine ) 
FROM
	dingdan 
WHERE
	yinhang = '建设银行' UNION ALL
SELECT
	dingdan_num,
	gongsi AS namee,
	SUM( jine ) 
FROM
	dingdan 
WHERE
	yinhang = '建设银行' 
GROUP BY
	yinhang,
	dingdan_num;

第二种方法：

第一步将dingdan表中的数据放入临时表中，需要将id*2，

CREATE TEMPORARY TABLE IF NOT EXISTS tem_dingdan SELECT
id * 2 AS id,
yinhang,
gongsi,
jine,
dingdan_num 
FROM
	dingdan;

第二步，将银行列去重单独抽取出来，将id（group by 银行之后的最小id-1），银行名称，银行名称，汇总金额，订单号等信息插如到临时订单表中

INSERT INTO tem_dingdan SELECT
min( id * 2 ) - 1,
yinhang,
yinhang AS gongsi,
sum( jine ) AS jine,
'0' AS dingdan_num 
FROM
	dingdan 
GROUP BY
	yinhang;

第三步：

将数据抽取出来,并删除临时表

SELECT
	dingdan_num,
	gongsi,
	jine 
FROM
	tem_dingdan 
ORDER BY
	id;
	
	
DROP TABLE
IF
	EXISTS tem_dingdan;

当然也可以将其封装成函数，或者存储过程