mysql - 将四个不同的查询合并为一个

Question

假设我有以下四个查询:

SELECT id_cre as credito, 
    data_eff as data, 
    '0.00' as incasso, 
    importo_fatt as spesa 
from fatture 
WHERE data_eff < '2014-12-31' 
UNION
SELECT id_cre as credito, 
    data_prev as data, 
    '0.00' as incasso, 
    importo_fatt as spesa 
from fatture 
WHERE data_prev >= '2014-12-31' 
UNION 
SELECT id_cre as credito, 
    data_prev as data, 
    importo as incasso, 
    '0.00' as spesa 
from incassi_prev 
WHERE data_prev >= '2014-12-31' 
UNION 
SELECT id_cre as credito, 
    data_inc as data, 
    importo_inc as incasso, 
    '0.00' as spesa 
from incassi_row 
WHERE data_inc < '2014-12-31'

我用它们提取代码(credito)、日期(数据)和金额)，可以是收入(incasso)或支出(spesa)。

结果可能会创建重复的行(具有相同代码的行，但一次具有收入，另一次具有支出)。如何重写查询以将具有相同代码和日期的收入和支出放在同一行？我想用 mysql 来做到这一点，因为我已经需要在 javascript 中循环生成的数组来按行重写表(每个代码作为一行，每个月作为一列)。

此外，有没有办法做到同样的事情，但考虑到同一月/年中的两个不同日期(例如2014-12-20和2014-12- 31)

谢谢!

Answer 1

尝试

SELECT credito, data, SUM(incasso) AS incasso, SUM(speca) AS speca
  FROM
(
  SELECT id_cre AS credito, data_eff AS data, 0.0 AS incasso, importo_fatt AS spesa 
    FROM fatture 
   WHERE data_eff < '2014-12-31' 
   UNION
  SELECT id_cre AS credito, data_prev AS data, 0.0 AS incasso, importo_fatt AS spesa 
    FROM fatture 
   WHERE data_prev >= '2014-12-31' 
   UNION 
  SELECT id_cre AS credito, data_prev AS data, importo AS incasso, 0.0 AS spesa 
    FROM incassi_prev 
   WHERE data_prev >= '2014-12-31' 
   UNION 
  SELECT id_cre AS credito, data_inc AS data, importo_inc AS incasso, 0.0 AS spesa 
    FROM incassi_row 
   WHERE data_inc < '2014-12-31'
) q
GROUP BY credito, data