是否按虚拟列筛选？

Question

我有以下数据库结构：

[Order]
OrderId
Total

[Payment]
OrderId
Amount

每个订单可以有X个付款行。我只想得到所有付款的总和小于订单总数的订单列表。

我有以下SQL，但我会返回所有订单支付和未支付。

   SELECT o.OrderId, 
          o.UserId, 
          o.Total, 
          o.DateCreated, 
          COALESCE(SUM(p.Amount),0) AS Paid
     FROM [Order] o
LEFT JOIN Payment p ON p.OrderId = o.OrderId
 GROUP BY o.OrderId, o.Total, o.UserId, o.DateCreated

我尝试添加Where (Paid < o.Total)，但它不起作用，有什么想法吗？BTM我使用的是SQL CE 3.5

Answer 1

您要查找的是HAVING子句。

不是"Where (COALESCE(SUM(p.Amount),0) < o.Total)"，而是"HAVING (COALESCE(SUM(p.Amount),0) < o.Total)“

查看MSDN Reference on HAVING。

Answer 2

Select Order.* From Order
Left Join
(Select OrderID, Sum(Amount) As t From Payment Group By OrderID) As s On s.OrderID = Oder.OrderID
Where IsNULL(s.t,0) < Order.Total