按虚拟列过滤？

发布于 2024-10-08 20:13:42 字数 534 浏览 1 评论 0原文

我有以下数据库结构：

[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

原文

I have the following database structure :

[Order]
OrderId
Total

[Payment]
OrderId
Amount

Every Order can have X payment rows. I want to get only the list of orders where the sum of all the payments are < than the order Total.

I have the following SQL but I will return all the orders paid and unpaid.

   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

I have tried to add Where (Paid < o.Total) but it does not work, any idea?
BTM I'm using SQL CE 3.5

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

顾铮苏瑾 2024-10-15 20:13:42

您正在寻找的是 HAVING 子句。

不要使用“Where (COALESCE(SUM(p.Amount),0) < o.Total)”，而是尝试“HAVING (COALESCE(SUM(p.Amount),0) < o.Total)”

查看 < a href="http://msdn.microsoft.com/en-US/library/ms180199%28v=SQL.90%29.aspx" rel="nofollow">关于 HAVING 的 MSDN 参考。

回复收藏 0 原文

眼泪淡了忧伤 2024-10-15 20:13:42

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

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

回复收藏 0 原文

~没有更多了~