我的MYSQL数据库中有两个表，我需要在视图中合并它们。
在两个表上，我都必须进行一些数学运算以获得正确的结果，并且必须在同一天订购它们。
第一个表类似于下面的表，称为chiusure：
+----+------------+--------+--------+---------+------+----+
| id | data | totale | sconti | annulli | resi | sf |
+----+------------+--------+--------+---------+------+----+
| 1 | 2016-03-01 | 153.82 | 1.07 | 0.00 | 0.00 | 34 |
| 2 | 2016-03-02 | 241.58 | 0.01 | 0.00 | 0.00 | 32 |
| 3 | 2016-03-03 | 0.00 | 0.01 | 0.00 | 0.00 | 0 |
| 4 | 2016-03-04 | 0.00 | 0.00 | 0.00 | 0.00 | 0 |
| 5 | 2016-03-05 | 0.00 | 0.00 | 0.00 | 0.00 | 0 |
+----+------------+--------+--------+---------+------+----+
第二张表与下面的表类似，称为
emergenza：+----+------------+----------+--------+
| id | data | ora | totale |
+----+------------+----------+--------+
| 1 | 2016-03-04 | 09:30:00 | 2.20 |
| 2 | 2016-03-04 | 09:40:00 | 9.00 |
| 3 | 2016-03-04 | 09:50:00 | 5.00 |
|....|............|..........|........|
| 27 | 2016-03-05 | 09:14:00 | 4.40 |
| 28 | 2016-03-05 | 09:27:00 | 5.00 |
| 29 | 2016-03-05 | 09:33:00 | 2.20 |
|....|............|..........|........|
+----+------------+----------+--------+
我在这里发现困难的是，在
emergenza表中有多个具有相同日期的行。在另一个视图（view_emergenza）中，我按日期将它们分组：SELECT
data,
sum(totale) AS chiusura,
count(id) AS sf
FROM emergenza
GROUP BY DAY(data);
结果是：
+------------+----------+----+
| data | chiusura | sf |
+------------+----------+----+
| 2016-03-04 | 178.90 | 26 |
| 2016-03-05 | 330.55 | 52 |
| 2016-03-06 | 333.55 | 46 |
| 2016-03-07 | 272.40 | 31 |
| 2016-03-08 | 169.40 | 28 |
| 2016-03-09 | 223.40 | 20 |
| 2016-03-10 | 206.00 | 19 |
| 2016-03-11 | 157.50 | 22 |
+------------+----------+----+
此外，在对两个表求和之前，我需要执行一些数学运算。在一个视图（
view_chiusure）中，我执行了这种数学运算以获得所需的结果：SELECT data, (totale - annulli - resi) AS chiusura, sf
FROM chiusure
结果是：
+------------+----------+----+
| data | chiusura | sf |
+------------+----------+----+
| 2016-03-01 | 153.82 | 34 |
| 2016-03-02 | 241.58 | 32 |
| 2016-03-03 | 0.00 | 0 |
| 2016-03-04 | 0.00 | 0 |
| 2016-03-05 | 0.00 | 0 |
+------------+----------+----+
此时，我想将两个视图合并到一个唯一的视图中：
GROUP BY DAY（数据）AS数据
（chiusure.totale-chiusure.annulli-chiusure.resi）+ [sum（emergenza.totale）GROUP BY紧急情况。
chiusure.sf + [count（emergenza.id）GROUP BYemergeza.data WHEREemnza.data = chiusure.data]
更新
我尝试过这种方式：
SELECT
C.data,
C.chiusura + (SELECT E.chiusura FROM view_emergenza E WHERE E.data = C.data ) AS chiusura,
C.sf + (SELECT E.sf FROM view_emergenza E WHERE E.data = C.data ) as sf
FROM view_chiusure C
但似乎
view_chiusure.chiusura和view_chiusure.sf被完全忽略了+------------+----------+------+
| data | chiusura | sf |
+------------+----------+------+
| 2016-03-01 | NULL | NULL |
| 2016-03-02 | NULL | NULL |
| 2016-03-03 | NULL | NULL |
| 2016-03-04 | 178.90 | 26 |
| 2016-03-05 | 330.55 | 52 |
| 2016-03-06 | 333.55 | 46 |
| 2016-03-07 | 272.40 | 31 |
| 2016-03-08 | 169.40 | 28 |
| 2016-03-09 | 223.40 | 20 |
| 2016-03-10 | 206.00 | 19 |
+------------+----------+------+
结果应该是
+------------+----------+------+
| data | chiusura | sf |
+------------+----------+------+
| 2016-03-01 | 153.82 | 34 |
| 2016-03-02 | 241.58 | 42 |
| 2016-03-03 | 0.00 | 0 |
| 2016-03-04 | 178.90 | 26 |
| 2016-03-05 | 330.55 | 52 |
| 2016-03-06 | 333.55 | 46 |
| 2016-03-07 | 272.40 | 31 |
| 2016-03-08 | 169.40 | 28 |
| 2016-03-09 | 223.40 | 20 |
| 2016-03-10 | 206.00 | 19 |
+------------+----------+------+
是否可以合并和汇总两个表？如果是，我该怎么办？
最佳答案
您必须首先创建视图view_emergenza和view_chiusure。
然后，您必须创建一个仅获取日期的视图：
CREATE VIEW vu_data AS
(
SELECT data FROM view_chiusure
UNION
SELECT data FROM view_emergenza
)
然后创建一个视图以获取不同的日期：
CREATE VIEW vu_distinct_data AS
SELECT DISTINCT data
FROM vu_data
现在，您可以创建另一个视图以加入基本视图，如下所示：
CREATE VIEW vu_join AS
SELECT
(SELECT C.chiusura FROM view_chiusura C WHERE C.data=D.data)+
(SELECT E.chiusura FROM view_emergenza E WHERE E.data=D.data) AS chiusura,
(SELECT C.sf FROM view_chiusura C WHERE C.data=D.data)+
(SELECT E.sf FROM view_emergenza E WHERE E.data=D.data) AS sf,
D.data
FROM vu_distinct_data D
现在，您可以轻松使用
vu_join：SELECT *
FROM vu_join
ORDER BY data