我很难消除特定表达式中具有零值的行,这里的帮助很大
这是我的两个简单桌子
create table tbl1
(id int, account varchar(50), stock varchar(50), price int, vol int);
insert into tbl1 values
(1, 'a1', 's1', 10, 5),
(2, 'a1', 's2', 5, 5),
(3, 'a2', 's1', 15, 3),
(4, 'a2', 's2', 20, 2),
(5, 'a2', 's2', 20, 2);
create table tbl2
(id int, tid int, price int, vol int);
insert into tbl2 values
(1, 1, 5, 3),
(2, 1, 5, 1),
(3, 1, 15, 1),
(4, 2, 5, 3),
(5, 2, 6, 2);
我的选择如下,它给了我所需的东西,但也给了我
(t1.vol - ifnull(Sum(t2.vol), 0))
返回零的行select
t1.id,account,stock,
(t1.vol - ifnull(Sum(t2.vol), 0)) vol
from tbl1 t1
left join tbl2 t2 on t1.id=t2.tid
group by t1.id
有人可以帮我摆脱这些零值吗?
我尝试了
having (t1.vol - ifnull(Sum(t2.vol), 0)) <> 0
==>它说vol是无效列我试过
where (t1.vol - ifnull(Sum(t2.vol), 0)) <> 0
==>它说无效使用组功能这是我上面查询得到的输出
ID ACCOUNT STOCK VOL
1 a1 s1 0
2 a1 s2 0
3 a2 s1 3
4 a2 s2 2
5 a2 s2 2
解:
select
t1.id,account,stock,
(t1.vol - ifnull(Sum(t2.vol), 0)) vol
from tbl1 t1
left join tbl2 t2 on t1.id=t2.tid
group by t1.id
having vol <> 0
最佳答案
您可以如下修改查询
select t1.id,
t1.account,
t1.stock,
(t1.vol - coalesce(tab.vol_total,0)) as vol
from tbl1 t1
left join
(
select tid,Sum(vol) as vol_total
from tbl2
group by tid
) tab
on t1.id=tab.tid
where (t1.vol - coalesce(tab.vol_total,0)) > 0
关于mysql - 如何从多表条件和查询中仅选择非零值,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/26683499/