我有個問題。例如，我在資料庫中有這個表：
row column visible
-----------------------
1 1 no
1 2 no
1 3 no
1 4 no
2 1 yes
2 2 yes
2 3 yes
2 4 yes
我想遍歷這個表并驗證是否所有行都具有可見性“否”。如果所有行的可見性為 no，我希望我的新表是：
row column visible
-----------------------
1 1 yes
1 2 yes
1 3 yes
1 4 yes
我正在考慮這樣的存盤程序中的游標：
create procedure someProcedure
as
declare @visible varchar(5)
declare @column int
declare @position int
declare scan cursor for
select column, position, visible
from table
where row between (select min(row) from table)
and (select max(row) from table)
order by row, column
但我不知道該怎么做，我對 sql 很陌生，謝謝
uj5u.com熱心網友回復：
游標速度慢且效率低下，很少需要。您不需要為此使用游標，您可以使用可更新的 CTE來更新yes行。
CREATE OR ALTER PROCEDURE someProcedure
AS
WITH cte AS (
SELECT *,
AnyVisible = COUNT(CASE WHEN Visible = 'yes' THEN 1 END) OVER (PARTITION BY row)
FROM [table] t
)
DELETE FROM cte
WHERE AnyVisible = 0;
WITH cte AS (
SELECT *,
rn = DENSE_RANK() OVER (ORDER BY row)
FROM [table] t
)
UPDATE cte
SET row = rn;
GO
uj5u.com熱心網友回復：
我不是 100% 確定您要實作的目標，但這里有一個用于游標的小模板。
create procedure someProcedure as
declare @column int
declare @position int
declare @visible varchar(5)
declare scan cursor for
select column, position, visible
from table
where row between (select min(row) from table)
and (select max(row) from table)
order by row, column
--Open the cursor
open scan
-- fill variables with first position of cursor
fetch next from scan into @column, @position, @visible
-- start looping unit end
while(@@FETCH_STATUS = 0)
begin
/*
Do things here
*/
-- move to next position in cursor
fetch next from scan into @column, @position, @visible
end
close scan
deallocate scan
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/311527.html
標籤：sql sql-server 查询语句
上一篇：對新創建的列使用CASEWHEN