android - 数据从特定表的日期而不是 sqlite 中的所有表填充

Question

我有三个表，即贸易、预付款和贷款，当我在贷款中输入一些值时，它不会显示，直到我在特定日期/月份输入一些贸易值。

谁能告诉我如何解决这个问题？

SELECT strftime('%m-%Y', tradedate) as 'month', 
(SELECT IFNULL (SUM(FTL.tradebalanceamount),0 ) 
   FROM farmertradelabel as FTL 
   WHERE FTL.mobileno = '9486032141'  AND strftime('%m-%Y', FTL.tradedate) = strftime('%m-%Y', farmertradelabel.tradedate)
   ORDER BY strftime('%m-%Y', FTL.tradedate) DESC) AS tradeamount,
(SELECT IFNULL (SUM(advanceamount),0)  
   FROM advancelabel 
   WHERE advancelabel.mobileno = '9486032141' AND strftime('%m-%Y', advancelabel.advancedate) = strftime('%m-%Y', farmertradelabel.tradedate)
   ORDER BY strftime('%m-%Y', advancelabel.advancedate) DESC) AS advanceamount,
(SELECT IFNULL (SUM(loantotalamount),0) 
  FROM loanlabel 
  WHERE loanlabel.mobileno = '9486032141' AND strftime('%m-%Y', loanlabel.loandate) = strftime('%m-%Y', farmertradelabel.tradedate)
  ORDER BY strftime('%m-%Y', loanlabel.loandate) DESC) AS loanamount   
FROM farmertradelabel

Answer 1

试试下面的方法。

我所做的是创建一个包含唯一月份的虚拟表。该月的 3 个表之一中有信息。然后金额绑定(bind)到那个月。

我还删除了 SUM 子句中的 ORDER BY，因为它们不会改变结果。您的 IFNULL 也可以更改，具体取决于您期望它如何工作。

这种方法可以节省您为每 3 个字段(9 个 SUM)写出 3 批 SUM。如有错别字，请修改。

注意 month 在某些 SQL 中是关键字。因此理想情况下，应该使用更好的列名。

SELECT month, 
(SELECT IFNULL (SUM(FTL.tradebalanceamount),0 ) 
   FROM farmertradelabel as FTL 
   WHERE FTL.mobileno = '9486032141'  AND strftime('%m-%Y', FTL.tradedate) = month) AS tradeamount,
(SELECT IFNULL (SUM(advanceamount),0)  
   FROM advancelabel 
   WHERE advancelabel.mobileno = '9486032141' AND strftime('%m-%Y', advancelabel.advancedate) = month) AS advanceamount,
(SELECT IFNULL (SUM(loantotalamount),0) 
  FROM loanlabel 
  WHERE loanlabel.mobileno = '9486032141' AND strftime('%m-%Y', loanlabel.loandate) = month) AS loanamount   

FROM
 (SELECT strftime('%m-%Y', tradedate) as 'month' FROM farmertradelabel
 UNION 
   SELECT  strftime('%m-%Y', advancedate) as 'month' FROM advancelabel
 UNION 
   SELECT strftime('%m-%Y', loandate) as 'month' FROM loanlabel
 ) as dateTable