帮助复杂的UPDATE查询 [英] Help with a complex UPDATE query
问题描述
嗯，我觉得它很复杂 - 你可能不会:)
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）。
甚至有可能实现我的目标我想做什么？
提前感谢您提供的任何帮助！
-
慢一点你好
50,000）。
我现在正在做的是SELECT * FROM CustTransactions WHERE
CustSelected =正确按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）。
甚至有可能实现我的目标我想做什么？
提前感谢您提供的任何帮助！
-
慢一点你好
您好，
请考虑以下示例数据：
INSERT INTO CustTransactions VALUES（1， 1000,0）
INSERT INTO CustTransactions VALUES（2,1000,1）
INSERT INTO CustTransactions VALUES（2,2500,1）
INSERT INTO CustTransactions VALUES（1,1000,1）
INSERT INTO CustTransactions VALUES（1,1000,1）
INSERT INTO CustTransactions VALUES（3,30000,1）
INSERT INTO CustTransactions VALUES（3,17000,1）
预期结果是什么（SELECT * FROM
的输出CustTransactions）？
还要考虑这个样本数据：
INSERT INTO CustTransactions VAL UES（1,10000,0）
INSERT INTO CustTransactions VALUES（2,20000,1）
INSERT INTO CustTransactions VALUES（2,25000,0）
INSERT INTO CustTransactions VALUES（2,2500,0）
这种情况下的预期结果是什么？
Razvan
比你写的慢：
好吧，我认为它很复杂 - 你可能不会：）< br $>
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记录的总和不大于指定的金额（比如说）
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 both
indexed (non unique).
What I would like to do is, for all of the records in descending order
of "AmountSpent" where "CustSelected = TRUE", set CustSelected to FALSE
such 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 WHERE
CustSelected = TRUE ORDER BY AmountSpent;", programatically looping
through all the records until AmountSpent 50000, then continuine to
loop through the remainder of the records setting CustSelected = FALSE.
This does exactly what I want but is slow and inefficient. I am sure it
could be done in a single SQL statement with subqueries, but I lack the
knowledge and experience to figure out how.
The closest I can get is:-
UPDATE CustTransactions SET CustSelected = FALSE
WHERE (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 amount
spent remain "selected", not the top "X" customers whose total spend
is $50,000. I really need to replace the "SELECT TOP 50000" with some
form 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解决方案50,000).
What I''m doing at the moment is a "SELECT * FROM CustTransactions WHERE
CustSelected = TRUE ORDER BY AmountSpent;", programatically looping
through all the records until AmountSpent 50000, then continuine to
loop through the remainder of the records setting CustSelected = FALSE.
This does exactly what I want but is slow and inefficient. I am sure it
could be done in a single SQL statement with subqueries, but I lack the
knowledge and experience to figure out how.
The closest I can get is:-
UPDATE CustTransactions SET CustSelected = FALSE
WHERE (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 amount
spent remain "selected", not the top "X" customers whose total spend
is
50,000. I really need to replace the "SELECT TOP 50000" with some
form 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
Hi,
Consider the following sample data:
INSERT INTO CustTransactions VALUES (1, 1000, 0)
INSERT INTO CustTransactions VALUES (2, 1000, 1)
INSERT INTO CustTransactions VALUES (2, 2500, 1)
INSERT INTO CustTransactions VALUES (1, 1000, 1)
INSERT INTO CustTransactions VALUES (1, 1000, 1)
INSERT INTO CustTransactions VALUES (3, 30000, 1)
INSERT INTO CustTransactions VALUES (3, 17000, 1)
What is the expected result (the output of SELECT * FROM
CustTransactions) ?
Also consider this sample data:
INSERT INTO CustTransactions VALUES (1, 10000, 0)
INSERT INTO CustTransactions VALUES (2, 20000, 1)
INSERT INTO CustTransactions VALUES (2, 25000, 0)
INSERT INTO CustTransactions VALUES (2, 2500, 0)
What is the expected result in this case ?
Razvan
Slower Than You wrote: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 both
indexed (non unique).
What I would like to do is, for all of the records in descending order
of "AmountSpent" where "CustSelected = TRUE", set CustSelected to FALSE
such that the sum of all the AmountSpent records with CustSelected =
TRUE is no greater than a specified amount (say
这篇关于帮助复杂的UPDATE查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
