在更新语句中使用 SUM where criteria
我一直在尝试搜索此内容,但不确定我使用的术语是否正确。或者这可能只是不可能。但我想做的是更新表中构成小于设定值的总和值的所有记录。
所以这是一个例子:
ID type amount received_date processed_date
1 debit 10 7/1/2010 NULL
2 debit 10 7/2/2010 NULL
3 debit 10 7/3/2010 NULL
现在我想做的是更新总和小于 22 的所有记录。因此,当我执行时,id 1 和 2 的总和将等于 20,小于 22。但它也仅需要是processed_date 为空的记录。我还希望它能够正常工作,以便它从最旧的更新到最新的。
基本上这是我用伪代码编写它的方式:
UPDATE credits
SET date_processed = '8/1/2010'
WHERE SUM(amount) <= @total AND
credit_type = [debits]
但我知道这是行不通的。所以我希望一些SQL高手能有想法。
我确信我可以在游标中编写此操作,但我想知道是否有一种基于集合的方法来执行此操作。
编辑:我更新了下面的表格和简要说明,以更好地描述我的情况。
I have been trying to search for this and am not sure I am using the correct terms. Or it might just be that it's not possible. But what I am trying to do is update all records in a table that make up a sum value that is less than a set value.
So here is an example:
ID type amount received_date processed_date
1 debit 10 7/1/2010 NULL
2 debit 10 7/2/2010 NULL
3 debit 10 7/3/2010 NULL
Now what I want to do is update all records that are equal to a sum of less than 22. So when I would do the sum id 1 and 2 would equal 20 which is less than 22. But it also needs to be only records that have a null for processed_date. I also want it to work so that it updates from oldest to newest.
Basically here is how I would write it in pseudo code:
UPDATE credits
SET date_processed = '8/1/2010'
WHERE SUM(amount) <= @total AND
credit_type = [debits]
But I know that this doesn't work. So I'm hoping some SQL master might have ideas.
I'm sure I could write this within a cursor but I'm wondering if there is a set based way to perform this.
EDIT: I updated the table and brief description below to better portray my circumstance.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
SQL 表中的行表示无序列表的项目。因此,我们必须提供订单。在您的示例中,您暗示它应该处理按 Id 排序的行。
正如我在评论中提到的,依赖 Id 作为序列标记是不安全的。可能存在间隙,并且有人使用 IDENTITY_INSERT 将“稍后”的行插入到这些间隙中。相反,您应该使用日期时间列。如果该列是
received_date
,则只需在上述查询中用Id
替换received_date
即可。Rows in a SQL table, represent an unordered list of items. Thus, we have to provide an order. In your example, you hint that it should process the rows ordered by Id.
As I mentioned in comments, it is not safe to depend on Id being the marker for sequence. It is possible to have gaps and for someone to insert "later" rows into those gaps using IDENTITY_INSERT. Instead, you should use a datetime column. If that column is
received_date
, then simply substituteId
forreceived_date
in the above query.对于这种情况,您应该使用 HAVING 子句。
根据 w3schools 的说法,“HAVING 子句被添加到 SQL 中,因为 WHERE 关键字不能与聚合函数一起使用。”
上找到的很棒的教程
这是在 w3schools http://www.w3schools.com/SQL/sql_having 。 ASP
You should use the HAVING clause for this type of situations.
According to w3schools, "The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions."
Here is a great tutorial found on w3schools
http://www.w3schools.com/SQL/sql_having.asp