如何从两个不同的表(A,B)中选择列，其中sum(列表A)小于columntableB

I can select price where sum(tax) less than price, I am executing the Query but I am getting an error

我可以在sum(tax)小于price的地方选择price，我正在执行查询，但是我得到了一个错误

select    b.price
from tableA a
inner join tableB b on b.idprice = a.idprice where sum(tax)<price
group by a.idprice,b.price

2 个解决方案

#1

I think your original approach will work by using a having clause rather than a where:

我认为你最初的方法将通过使用“拥有”条款而不是“在哪里”来实现:

select b.price
from tableA a inner join
     tableB b
     on b.idprice = a.idprice
group by a.idprice, b.price
having sum(tax) < b.price;

#2

so something like this:

所以这样的:

SELECT t.*,s.*
FROM TableA t
INNER JOIN(SELECT idprice,sum(tax) as sum_tax
           FROM TableB
           GROUP BY idprice) s
ON(t.idprice = s.idprice and t.price < s.sum_tax)

Or if price table should be summed too :

或者如果价格表也应该总结一下:

SELECT t.idprice,sum(t.price),max(s.sum_tax)
FROM TableA t
INNER JOIN(SELECT idprice,sum(tax) as sum_tax
           FROM TableB
           GROUP BY idprice) s
ON(t.idprice = s.idprice)
GROUP BY t.idprice
having sum(t.price) < max(s.sum_tax)

#1