我有这样的桌子
从这里我想显示一个表格
所以,我试过自我加入
SELECT a.Name, a.Acc, a.Time, a.Amount as dep_amt, new.Amount as wid_amt, (a.Amount-new.Amount) as profit
FROM all_data as a, all_data as new
where a.Time = new.Time
and a.name = new.name
and a.acc =new.acc
and a.x= "dep”
and new.x="wit"
但是,我不能使用 Group by(error contains nonaggregated column) 和这个 sql 结果一个包含多行的表。怎么做???
这看起来像条件聚合,而不是自联接:
select Name, Acc, Time,
sum(case when x = 'dep' then amount else 0 end) as dep_amt,
sum(case when x = 'wit' then amount else 0 end) as wit_amt,
sum(case when x = 'dep' then amount
when x = 'wit' then - amount
else 0
end) as profit
from t
group by Name, Acc, Time;
这是一个 db<>fiddle。
自联接不起作用,因为每个组中有多个“dep”和“wit”行。在每个组中,您会得到这些行的笛卡尔积,从而导致总计算中出现错误。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句