帮助复杂的UPDATE查询

本文介绍了帮助复杂的UPDATE查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 嗯，我觉得它很复杂 - 你可能不会:) TableDef： CREATE TABLE CustTransactions（ TransactionKey int IDENTITY（1,1）NOT NULL， CustomerID int， AmountSpent float， CustSelected位默认为0） ; TransactionKey是主键，CustomerID和AmountSpent都是 索引（非唯一）。 我想做的是，对于所有按降序排列的记录 的AmountSpent其中CustSelected = TRUE，将CustSelected设置为FALSE ，使得CustSelected = TRUE的所有AmountSpent记录的总和不大于指定的金额（比如说） $ 50,000 $。 我现在正在做的是SELECT * FROM CustTransactions WHERE CustSelected = TRUE ORDER by AmountSpent; ，编程循环 通过所有记录直到AmountSpent 50000，然后继续到循环通过剩余的记录设置CustSelected = FALSE。 这正是我想要的，但速度慢，效率低。我相信它可以在带有子查询的单个SQL语句中完成，但我缺乏知识和经验来确定如何。 我能得到的最接近的是： - UPDATE CustTransactions SET CustSelected = FALSE WHERE（CustSelected = TRUE） AND TransactionKey NOT IN （从CustTransactions选择TOP 50000 TransactionKey WHERE （（（CustTransactions.CustSelected）= TRUE）） ORDER BY AmountSpect DESC，TransactionKey ASC）; 然而，这个mereley确保只剩下前五万客户的金额 花费剩余选择，而不是顶部X总支出为的客户为$ 50,000。我真的需要更换SELECT TOP 50000。用一些 形式的SELECT TOP（X行直到sum（AmountSpent）= 50000）。 甚至有可能实现我的目标我想做什么？ 提前感谢您提供的任何帮助！ - 慢一点你好 解决方案 Well, I think it''s complex anyway -- you might not :) TableDef:CREATE TABLE CustTransactions (TransactionKey int IDENTITY(1,1) NOT NULL,CustomerID int,AmountSpent float,CustSelected bit default 0); TransactionKey is the primary key, CustomerID and AmountSpent are bothindexed (non unique). What I would like to do is, for all of the records in descending orderof "AmountSpent" where "CustSelected = TRUE", set CustSelected to FALSEsuch that the sum of all the AmountSpent records with CustSelected =TRUE is no greater than a specified amount (say $50,000). What I''m doing at the moment is a "SELECT * FROM CustTransactions WHERECustSelected = TRUE ORDER BY AmountSpent;", programatically loopingthrough all the records until AmountSpent 50000, then continuine toloop through the remainder of the records setting CustSelected = FALSE.This does exactly what I want but is slow and inefficient. I am sure itcould be done in a single SQL statement with subqueries, but I lack theknowledge and experience to figure out how. The closest I can get is:- UPDATE CustTransactions SET CustSelected = FALSEWHERE (CustSelected = TRUE)AND TransactionKey NOT IN(SELECT TOP 50000 TransactionKey FROM CustTransactions WHERE(((CustTransactions.CustSelected)=TRUE))ORDER BY AmountSpect DESC, TransactionKey ASC); However, this mereley ensures only the top 50,000 customers by amountspent remain "selected", not the top "X" customers whose total spendis $50,000. I really need to replace the "SELECT TOP 50000" with someform of "SELECT TOP (X rows until sum(AmountSpent) =50000)". Is it even possible to achieve what I''m trying to do? Thanks in advance for any assistance offered!--SlowerThanYou 解决方案 这篇关于帮助复杂的UPDATE查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！