php - MySQL 跨表查询连接

Question

我想创建一个由两个输入表组合而成的输出表，如下所示。表1和表2是MySQL表。输出是所需的表格 View 。

请帮助我对表 1 和表 2 (mysql) 的输出进行计费

Answer 1

您可以将条件聚合与UNION ALL结合使用来实现此目的:

SELECT t1.acc_no, t1.name,
       SUM(CASE WHEN t2.`date` = '2016-06-01' THEN amount END) AS '2016-06-01',
       SUM(CASE WHEN t2.`date` = '2016-06-02' THEN amount END) AS '2016-06-02',
       SUM(CASE WHEN t2.`date` = '2016-06-03' THEN amount END) AS '2016-06-03',
       SUM(amount) AS 'Total'
FROM table1 AS t1
LEFT JOIN table2 AS t2 ON t1.acc_no = t2.acc_no

UNION ALL

SELECT 'Total', null,
       SUM(CASE WHEN t2.`date` = '2016-06-01' THEN amount END) AS '2016-06-01',
       SUM(CASE WHEN t2.`date` = '2016-06-02' THEN amount END) AS '2016-06-02',
       SUM(CASE WHEN t2.`date` = '2016-06-03' THEN amount END) AS '2016-06-03',
       SUM(amount) AS 'Total'  
FROM table2