从特定表的日期而不是sqlite中的所有表填充数据 [英] Data gets populated from a particular table's date instead of all table in sqlite

问题描述

我有三个表，分别是交易，预付款和贷款，在其中输入一些贷款值时，直到我输入该特定日期/月份的一些交易值时，该表才会显示.

I have three table namely trade, advance, and loan in which when I entered some values in loan it doesn't get displayed until I enter some values in trade for that particular date/month.

那么有人可以告诉我如何克服这个问题吗?

So can anyone tell me how I can overcome this issue?

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

推荐答案

尝试以下操作.

我所做的是创建了一个虚拟表，其中包含唯一的月份.该月的三个表之一中有哪些信息.然后，总和将绑定到该月.

What I have done is created a virtual table, containing unique months. To which there is info in one of the 3 tables for that month. Then the sums are bound to that month.

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

I have also removed the ORDER BY in the SUM clauses, as they don't change the result. Your IFNULL could also be changed, depending on how you expect it to work.

此方法可节省您为每3个字段写出3个SUM(9个SUM).如果有错别字，请相应解决.

This method saves you writing out 3 lots of SUMs for each 3 fields (9 SUMs). If there are any typos, please fix accordingly.

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

NOTE that month is a keyword, in some SQL. So ideally, a better column name should be used.

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

这篇关于从特定表的日期而不是sqlite中的所有表填充数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！