Sqllite sum () 在连接第二个表时返回错误的值

Question

>我正在尝试计算两个表中两列的总和，按日期分组并将两个表连接在一起

查询 1

select 
    Dates
    , ifnull( sum(Amount),0) As ETA 
from 
    Expence as t1 
where 
    t1.Dates IN (
        select distinct Dates As A from ( 
            select 
                Expence.Dates 
            from
                Expence 
            union all 
            select 
                Pass.Dates 
            from
                Pass 
                left join Expence 
                    on Pass.Dates=Expence.Dates
        )
    ) 
group by 
    dates

查询 2

select 
    Dates
    , ifnull( sum(Paid),0) As PTP
    , ifnull( sum(TotalAmount),0) As PTA 
from 
    pass As t2 
where 
    t2.Dates IN (
        select distinct 
            Dates As A 
        from ( 
            select 
                Expence.Dates 
            from 
                Expence 
            union all 
            select 
                Pass.Dates 
            from 
                Pass 
                left join Expence 
                    on Pass.Dates=Expence.Dates
        )
    ) 
group by 
    dates

这两个查询独立工作，但在连接在一起时返回不正确的答案。

select distinct 
    t.Dates
    ,PTP
    ,PTA
    ,ETA 
from (
    select 
        Dates
        , ifnull( sum(Paid),0) As PTP
        , ifnull( sum(TotalAmount),0) As PTA 
    from 
        pass As t2 
    where 
        t2.Dates IN (
            select distinct 
                Dates As A 
            from( 
                select 
                    Expence.Dates 
                from 
                    Expence 
                    union all 
                select 
                    Pass.Dates 
                from 
                    Pass 
                    left join Expence 
                        on Pass.Dates=Expence.Dates
            )
        ) 
    group by dates
) AS t
, (
    select 
        Dates
        ,ifnull( sum(Amount),0) As ETA 
    from 
        Expence as t1 
    where 
        t1.Dates IN (
            select distinct 
                Dates As A from ( 
                    select 
                        Expence.Dates 
                    from Expence 
                    union all
                        select 
                            Pass.Dates 
                        from 
                            Pass 
                            left join Expence 
                                on Pass.Dates=Expence.Dates
                )
        ) 
    group by dates
) AS P

Answer 1

你得到的结果不正确，因为你编写的查询是无意义的。

from子句是从已声明的两个派生表中提取的。因为您尚未声明任何连接逻辑，所以 sqlite 假定自然连接。这意味着对于引入的每一列（对于两个表），它都会检查每一行中的匹配项。

由于这些查询似乎彼此之间几乎没有关系，因此您通常不会在匹配记录方面遇到太多麻烦。

我对您的数据了解不多，因此除了"单独运行它们"之外，提供更好的建议将是困难的。我的假设是，您将希望将on t.Dates = P.Dates添加到连接语句的末尾。

Answer 2

select distinct tdate AS `DATE`,PTA AS `TOTAL AMOUNT` ,PTP AS `TOTAL PAID`,ETA AS `TOTAL EXPENCE`, PTA -PTP  AS `BALANCE` from((
select Dates As tdate,ifnull( sum(Paid),0) As PTP, ifnull( sum(TotalAmount),0) As PTA from pass As t2 where t2.Dates IN  (select distinct Dates As A from( select Expence.Dates from Expence union all select Pass.Dates from Pass left join Expence on Pass.Dates=Expence.Dates))
 group by dates)AS t left join
(
(select Dates as pdate,ifnull( sum(Amount),0) As ETA from Expence as t1 where t1.Dates IN  (select distinct Dates As A from( select Expence.Dates from Expence union all select Pass.Dates from Pass left join Expence on Pass.Dates=Expence.Dates))  group by dates 
)AS p)  ON   tdate=pdate) where tdate between '24-01-2019' AND '25-01-2019'