如题,写了个纠结的游标 ,呵呵呵CREATE TABLE TB(
销售员 VARCHAR(20),
客户 VARCHAR(20),
奖金 INT
)
INSERT INTO TB(销售员,客户,奖金)
SELECT '销售a','客户a',12
UNION ALL
SELECT '销售a','客户b',11
UNION ALL
SELECT '销售a','客户c',19
UNION ALL
SELECT '销售b','客户a',12
UNION ALL
SELECT '销售b','客户b',10
UNION ALL
SELECT '销售c','客户c',12SELECT *
FROM TB
--------------查询---
SELECT *
FROM (SELECT TB.*,(ROW_NUMBER()OVER(PARTITION BY 销售员 ORDER BY 奖金 DESC)) NB
FROM TB)A
WHERE NB=1--游标实现
DECLARE @NUMBER VARCHAR(10)
DECLARE SL CURSOR FOR
SELECT A.NB
FROM (SELECT TB.*,(ROW_NUMBER()OVER(PARTITION BY 销售员 ORDER BY 奖金 DESC)) NB
FROM TB)A
OPEN SL
FETCH NEXT FROM SL
INTO @NUMBER
WHILE @@FETCH_STATUS=0
BEGIN
IF @NUMBER=1
BEGIN
SELECT *
FROM (SELECT TB.*,(ROW_NUMBER()OVER(PARTITION BY 销售员 ORDER BY 奖金 DESC)) NB
FROM TB)A
WHERE NB=@NUMBER
---------在这里退出---否则它会执行三次查询 因为有三个:NB=1,只让它执行一次
END
ELSE
BEGIN
FETCH NEXT FROM SL
INTO @NUMBER
END
END
CLOSE SL
DEALLOCATE SL---------
DROP TABLE TB-----------------
销售员 客户 奖金 NB
-------------------- -------------------- ----------- --------------------
销售a 客户c 19 1
销售b 客户a 12 1
销售c 客户c 12 1(3 行受影响)销售员 客户 奖金 NB
-------------------- -------------------- ----------- --------------------
销售a 客户c 19 1
销售b 客户a 12 1
销售c 客户c 12 1(3 行受影响)
销售员 VARCHAR(20),
客户 VARCHAR(20),
奖金 INT
)
INSERT INTO TB(销售员,客户,奖金)
SELECT '销售a','客户a',12
UNION ALL
SELECT '销售a','客户b',11
UNION ALL
SELECT '销售a','客户c',19
UNION ALL
SELECT '销售b','客户a',12
UNION ALL
SELECT '销售b','客户b',10
UNION ALL
SELECT '销售c','客户c',12SELECT *
FROM TB
--------------查询---
SELECT *
FROM (SELECT TB.*,(ROW_NUMBER()OVER(PARTITION BY 销售员 ORDER BY 奖金 DESC)) NB
FROM TB)A
WHERE NB=1--游标实现
DECLARE @NUMBER VARCHAR(10)
DECLARE SL CURSOR FOR
SELECT A.NB
FROM (SELECT TB.*,(ROW_NUMBER()OVER(PARTITION BY 销售员 ORDER BY 奖金 DESC)) NB
FROM TB)A
OPEN SL
FETCH NEXT FROM SL
INTO @NUMBER
WHILE @@FETCH_STATUS=0
BEGIN
IF @NUMBER=1
BEGIN
SELECT *
FROM (SELECT TB.*,(ROW_NUMBER()OVER(PARTITION BY 销售员 ORDER BY 奖金 DESC)) NB
FROM TB)A
WHERE NB=@NUMBER
---------在这里退出---否则它会执行三次查询 因为有三个:NB=1,只让它执行一次
END
ELSE
BEGIN
FETCH NEXT FROM SL
INTO @NUMBER
END
END
CLOSE SL
DEALLOCATE SL---------
DROP TABLE TB-----------------
销售员 客户 奖金 NB
-------------------- -------------------- ----------- --------------------
销售a 客户c 19 1
销售b 客户a 12 1
销售c 客户c 12 1(3 行受影响)销售员 客户 奖金 NB
-------------------- -------------------- ----------- --------------------
销售a 客户c 19 1
销售b 客户a 12 1
销售c 客户c 12 1(3 行受影响)
DEALLOCATE SL关闭和释放游标,就这样啊
你还要什么?
BEGIN
IF @NUMBER=1
BEGIN
SELECT *
FROM (SELECT TB.*,(ROW_NUMBER()OVER(PARTITION BY 销售员 ORDER BY 奖金 DESC)) NB
FROM TB)A
WHERE NB=@NUMBER
---------在这里退出---否则它会执行三次查询 因为有三个:NB=1,只让它执行一次
END
ELSE
BEGIN
FETCH NEXT FROM SL
INTO @NUMBER
END
END我想请问下怎么在上面那里退出,就是退出整个游标..这真是个多余的游标啊...俺去吃饭了,回来看答案了...
嘿嘿
DECLARE SL CURSOR FOR
SELECT DISTINCT A.NB
FROM (SELECT TB.*,(ROW_NUMBER()OVER(PARTITION BY 销售员 ORDER BY 奖金 DESC)) NB
FROM TB)A
SELECT A.NB
FROM (SELECT TB.*,(ROW_NUMBER()OVER(PARTITION BY 销售员 ORDER BY 奖金 DESC)) NB
FROM TB)A
非重复的用top 1 ...
销售员 VARCHAR(20),
客户 VARCHAR(20),
奖金 INT
)
INSERT INTO TB(销售员,客户,奖金)
SELECT '销售a','客户a',12
UNION ALL
SELECT '销售a','客户b',11
UNION ALL
SELECT '销售a','客户c',19
UNION ALL
SELECT '销售b','客户a',12
UNION ALL
SELECT '销售b','客户b',10
UNION ALL
SELECT '销售c','客户c',12select * into #tb from tb where 1<>1
--游标实现
DECLARE @sales varchar(20),@cus varchar(20),@NUMBER int
DECLARE SL CURSOR FOR SELECT * FROM TB
OPEN SL
FETCH NEXT FROM SL
INTO @sales,@cus,@NUMBER
WHILE @@FETCH_STATUS=0
BEGIN
IF not exists(select 1 from #tb where 销售员=@sales and 奖金>@NUMBER)
begin
delete #tb where 销售员=@sales
insert into #tb select @sales,@cus,@NUMBER
end
FETCH NEXT FROM SL
INTO @sales,@cus,@NUMBER
END
CLOSE SL
DEALLOCATE SL---------
select * from #tb
DROP TABLE tb,#tb/*
销售员 客户 奖金
-------------------- -------------------- -----------
销售a 客户c 19
销售b 客户a 12
销售c 客户c 12(3 行受影响)
我没用过GOTO,那有关于这的例子不...
销售员 VARCHAR(20),
客户 VARCHAR(20),
奖金 INT
)
INSERT INTO TB(销售员,客户,奖金)
SELECT '销售a','客户a',12
UNION ALL
SELECT '销售a','客户b',11
UNION ALL
SELECT '销售a','客户c',19
UNION ALL
SELECT '销售b','客户a',12
UNION ALL
SELECT '销售b','客户b',10
UNION ALL
SELECT '销售c','客户c',12DECLARE @NUMBER VARCHAR(10)
DECLARE SL CURSOR FOR
SELECT A.NB
FROM (SELECT TB.*,(ROW_NUMBER()OVER(PARTITION BY 销售员 ORDER BY 奖金 DESC)) NB
FROM TB)A
OPEN SL
FETCH NEXT FROM SL
INTO @NUMBER
WHILE @@FETCH_STATUS=0
BEGIN
IF @NUMBER=1
BEGIN
SELECT *
FROM (SELECT TB.*,(ROW_NUMBER()OVER(PARTITION BY 销售员 ORDER BY 奖金 DESC)) NB
FROM TB)A
WHERE NB=@NUMBER
goto lable
END
ELSE
BEGIN
FETCH NEXT FROM SL
INTO @NUMBER
END
ENDlable:CLOSE SL
DEALLOCATE SL
/*
(6 行受影响)
销售员 客户 奖金 NB
-------------------- -------------------- ----------- --------------------
销售a 客户c 19 1
销售b 客户a 12 1
销售c 客户c 12 1(3 行受影响)
SSP色狼哥,你是我的偶像呀...
SELECT *
FROM (SELECT TB.*,(ROW_NUMBER()OVER(PARTITION BY 销售员 ORDER BY 奖金 DESC)) NB
FROM TB)A
WHERE NB=1