现有一张订单表(样例数据,生产环境下,表结构要复杂许多),下图这个样子:
现在想要把其中的银行做汇总,并且把该银行的所有订单都显示在下面。就是这个样子了:
做法我想了两种,一种就是使用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;
当然也可以将其封装成函数,或者存储过程