- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我正在尝试编写一个查询,该查询将遍历一个表并将帐户上的任何信用应用于最早的余额。我想不出不使用游标的方法,我知道应该不惜一切代价避免使用游标,所以我来这里寻求帮助。
select * into #balances from [IDAT_AR_BALANCES] where amount > 0
select * into #credits from [IDAT_AR_BALANCES] where amount < 0
create index ba_ID on #balances (CLIENT_ID)
create index cr_ID on #credits (CLIENT_ID)
declare credit_cursor cursor for
select [CLIENT_ID], amount, cvtGUID from #credits
open credit_cursor
declare @client_id varchar(11)
declare @credit money
declare @balance money
declare @cvtGuidBalance uniqueidentifier
declare @cvtGuidCredit uniqueidentifier
fetch next from credit_cursor into @client_id, @credit, @cvtGuidCredit
while @@fetch_status = 0
begin
while(@credit < 0 and (select count(*) from #balances where @client_id = CLIENT_ID and amount <> 0) > 0)
begin
select top 1 @balance = amount, @cvtGuidBalance = cvtGuid from #balances where @client_id = CLIENT_ID and amount <> 0 order by AGING_DATE
set @credit = @balance + @credit
if(@credit > 0)
begin
update #balances set amount = @credit where cvtGuid = @cvtGuidBalance
set @credit = 0
end
else
begin
update #balances set amount = 0 where cvtGuid = @cvtGuidBalance
end
end
update #credits set amount = @credit where cvtGuid = @cvtGuidCredit
fetch next from credit_cursor into @client_id, @credit, @cvtGuidCredit
end
close credit_cursor
deallocate credit_cursor
delete #balances where AMOUNT = 0
delete #credits where AMOUNT = 0
truncate table [IDAT_AR_BALANCES]
insert [IDAT_AR_BALANCES] select * from #balances
insert [IDAT_AR_BALANCES] select * from #credits
drop table #balances
drop table #credits
before
cvtGuid client_id ammount AGING_DATE
xxxxxx 1 20.00 1/1/2011
xxxxxx 1 30.00 1/2/2011
xxxxxx 1 -10.00 1/3/2011
xxxxxx 1 5.00 1/4/2011
xxxxxx 2 20.00 1/1/2011
xxxxxx 2 15.00 1/2/2011
xxxxxx 2 -40.00 1/3/2011
xxxxxx 2 5.00 1/4/2011
xxxxxx 3 10.00 1/1/2011
xxxxxx 3 -20.00 1/2/2011
xxxxxx 3 5.00 1/3/2011
xxxxxx 3 -8.00 1/4/2011
after
cvtGuid client_id ammount AGING_DATE
xxxxxx 1 10.00 1/1/2011
xxxxxx 1 30.00 1/2/2011
xxxxxx 1 5.00 1/4/2011
xxxxxx 3 -5.00 1/2/2011
xxxxxx 3 -8.00 1/4/2011
最佳答案
可以在递归 CTE 的帮助下解决这个问题。
基本思想是这样的:
client_id
)。 amount
的符号和绝对值(即永远不会“夹断”相应的总数超过其当前值)。应该在 amount
中添加/减去相同的值. amount
所在的行已变为 0。cvtGuid
,为了演示目的声明为
int
)和一行(原始示例中的最后一行,其中Lieven 的脚本中缺少)。
/* preparing the demonstration data */
DECLARE @IDAT_AR_BALANCES TABLE (
cvtGuid int IDENTITY,
client_id INTEGER
, amount FLOAT
, date DATE
);
INSERT INTO @IDAT_AR_BALANCES
SELECT 1, 20.00, '1/1/2011'
UNION ALL SELECT 1, 30.00, '1/2/2011'
UNION ALL SELECT 1, -10.00, '1/3/2011'
UNION ALL SELECT 1, 5.00, '1/4/2011'
UNION ALL SELECT 2, 20.00, '1/1/2011'
UNION ALL SELECT 2, 15.00, '1/2/2011'
UNION ALL SELECT 2, -40.00, '1/3/2011'
UNION ALL SELECT 2, 5.00, '1/4/2011'
UNION ALL SELECT 3, 10.00, '1/1/2011'
UNION ALL SELECT 3, -20.00, '1/2/2011'
UNION ALL SELECT 3, 5.00, '1/3/2011'
UNION ALL SELECT 3, -8.00, '1/4/2011';
/* checking the original contents */
SELECT * FROM @IDAT_AR_BALANCES;
/* getting on with the job: */
WITH totals AS (
SELECT
/* 1) preparing the totals */
client_id,
total_pos = SUM(CASE WHEN amount > 0 THEN amount END),
total_neg = SUM(CASE WHEN amount < 0 THEN amount END)
FROM @IDAT_AR_BALANCES
GROUP BY client_id
),
refined AS (
/* 2) refining the original data with auxiliary columns:
* rownum - row numbers (unique within accounts);
* amount_to_discard_pos - the amount to discard `amount` completely if it's negative;
* amount_to_discard_neg - the amount to discard `amount` completely if it's positive
*/
SELECT
*,
rownum = ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY date),
amount_to_discard_pos = CAST(CASE WHEN amount < 0 THEN -amount ELSE 0 END AS float),
amount_to_discard_neg = CAST(CASE WHEN amount > 0 THEN -amount ELSE 0 END AS float)
FROM @IDAT_AR_BALANCES
),
prepared AS (
/* 3) preparing the final table (using a recursive CTE) */
SELECT
cvtGuid = CAST(NULL AS int),
client_id,
amount = CAST(NULL AS float),
date = CAST(NULL AS date),
amount_update = CAST(NULL AS float),
running_balance_pos = total_pos,
running_balance_neg = total_neg,
rownum = CAST(0 AS bigint)
FROM totals
UNION ALL
SELECT
n.cvtGuid,
n.client_id,
n.amount,
n.date,
amount_update = CAST(
CASE
WHEN n.amount_to_discard_pos < p.running_balance_pos
THEN n.amount_to_discard_pos
ELSE p.running_balance_pos
END
+
CASE
WHEN n.amount_to_discard_neg > p.running_balance_neg
THEN n.amount_to_discard_neg
ELSE p.running_balance_neg
END
AS float),
running_balance_pos = CAST(p.running_balance_pos -
CASE
WHEN n.amount_to_discard_pos < p.running_balance_pos
THEN n.amount_to_discard_pos
ELSE p.running_balance_pos
END
AS float),
running_balance_neg = CAST(p.running_balance_neg -
CASE
WHEN n.amount_to_discard_neg > p.running_balance_neg
THEN n.amount_to_discard_neg
ELSE p.running_balance_neg
END
AS float),
n.rownum
FROM refined n
INNER JOIN prepared p ON n.client_id = p.client_id AND n.rownum = p.rownum + 1
)
/* -- some junk that I've forgotten to clean up,
SELECT * -- which you might actually want to use
FROM prepared -- to view the final prepared result set
WHERE rownum > 0 -- before actually running the update
ORDER BY client_id, rownum
*/
/* performing the update */
UPDATE t
SET amount = t.amount + u.amount_update
FROM @IDAT_AR_BALANCES t INNER JOIN prepared u ON t.cvtGuid = u.cvtGuid
OPTION (MAXRECURSION 0);
/* checking the contents after UPDATE */
SELECT * FROM @IDAT_AR_BALANCES;
/* deleting the eliminated amounts */
DELETE FROM @IDAT_AR_BALANCES WHERE amount = 0;
/* checking the contents after DELETE */
SELECT * FROM @IDAT_AR_BALANCES;
amount
的帐户中删除所有行。先加起来为 0,然后更新其他行。这将提高整体性能,因为正如您所说,大多数数据的数量加起来为 0。
DELETE FROM @IDAT_AR_BALANCES
WHERE client_id IN (
SELECT client_id
FROM @IDAT_AR_BALANCES
GROUP BY client_id
HAVING SUM(amount) = 0
)
DELETE
更新之后还是需要的,因为更新可能会重置一些
amount
值为 0。如果我是你,我可能会考虑创建一个 FOR UPDATE 触发器,它会自动删除
amount = 0
所在的行。 .这样的解决方案并不总是可以接受的,但有时很好。这取决于您还可以对数据做什么。这也可能取决于它是否仅仅是您的项目,或者还有其他维护者(他们不喜欢“神奇地”和意外消失的行)。
关于sql - 优化 SQL 查询以移除游标,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/5809266/
SQL、PL-SQL 和 T-SQL 之间有什么区别? 谁能解释一下这三者之间的区别,并提供每一个的相关使用场景? 最佳答案 SQL 是一种对集合进行操作的查询语言。 它或多或少是标准化的,几乎所有关
这个问题已经有答案了: What is the difference between SQL, PL-SQL and T-SQL? (6 个回答) 已关闭 9 年前。 我对 SQL 的了解足以完成我的
我在数据库中有一个 USER 表。该表有一个 RegistrationDate 列,该列有一个默认约束为 GETDATE()。 使用 LINQ 时,我没有为 RegistrationDate 列提供任
我有一个可能属于以下类型的字符串 string expected result 15-th-rp 15 15/12-rp 12 15-12-th
很难说出这里问的是什么。这个问题模棱两可、含糊不清、不完整、过于宽泛或言辞激烈,无法以目前的形式合理回答。如需帮助澄清此问题以便可以重新打开,visit the help center . 9年前关闭
我有一个存储过程(称为 sprocGetArticles),它从文章表中返回文章列表。这个存储过程没有任何参数。 用户可以对每篇文章发表评论,我将这些评论存储在由文章 ID 链接的评论表中。 有什么方
我目前正在做一个 *cough*Oracle*cough* 数据库主题。讲师介绍embedded SQL作为让其他语言(例如 C、C++)与(Oracle)数据库交互的方式。 我自己做了一些数据库工作
SQL Server 中 SQL 语句的最大长度是多少?这个长度是否取决于 SQL Server 的版本? 例如,在 DECLARE @SQLStatement NVARCHAR(MAX) = N'S
这个问题已经有答案了: Simple way to transpose columns and rows in SQL? (9 个回答) 已关闭 8 年前。 CallType
预先感谢您对此提供的任何帮助。 假设我有一个查询,可以比较跨年的数据,从某个任意年份开始,永无止境(进入 future ),每年同一时期直到最后一个完整的月份(其特点是一月数据永远不会显示至 2 月
我在数据库中有一个 USER 表。该表有一个 RegistrationDate 列,该列的默认约束为 GETDATE()。 使用 LINQ 时,我没有为 RegistrationDate 列提供任何数
下面是我试图用来检查存储过程是否不存在然后创建过程的 sql。它会抛出一个错误:Incorrect syntax near the keyword 'PROCEDURE' IF NOT EXISTS
我有一个同事声称动态 SQL 在许多情况下比静态 SQL 执行得更快,所以我经常看到 DSQL 到处都是。除了明显的缺点,比如在运行之前无法检测到错误并且更难阅读,这是否准确?当我问他为什么一直使用
来自 lobodava 的动态 SQL 查询是: declare @sql nvarchar(4000) = N';with cteColumnts (ORDINAL_POSITION, CO
使用 SQL Server 中的存储过程执行动态 SQL 命令的现实优点和缺点是什么 EXEC (@SQL) 对比 EXEC SP_EXECUTESQL @SQL ? 最佳答案 sp_executes
我有这个有效的 SQL 查询: select sum(dbos.Points) as Points, dboseasons.Year from dbo.StatLines dbos i
我正在调试一些构建成功运行的 SQL 命令的代码。 然而,在查询结束时,查询结果似乎被写入了一个文本文件。 完整的查询如下 echo SELECT DATE,DATETABLE,DATE,APPDAT
我有一些创建表的 .sql 文件(MS SQL 数据库): 表_1.sql: IF OBJECT_ID (N'my_schema.table1', N'U') IS NOT NULL DROP TAB
我写了下面的 SQL 存储过程,它一直给我错误@pid = SELECT MAX(... 整个过程是: Alter PROCEDURE insert_partyco @pname varchar(20
我在 SQL Server 2005 中有包含两列 Fruit 和 Color 的表,如下所示 Fruit Colour Apple Red Orange
我是一名优秀的程序员,十分优秀!