Sql Cursor两次给出相同的结果，如何解决？ [英] Sql Cursor giving same result twice,how to resolve?

问题描述

请检查我的以下程序。预期输出为14行。我在运行内部查询（内部游标）时得到它。但是当我运行整个过程时，它会产生28行，即从1到14再次设置1到14个相同的结果集。预期的14行组显示两次，低于其他.Pls建议更正... thanku

Please check my below procedure. Expected output is 14 rows. I get it when I run the inner query(inside cursor). But when I run the entire procedure,it gives 28 rows,i.e one set 1 to 14 again same result set from 1 to 14. Expected set of 14 rows is shown twice one below other.Pls suggest the correction...thanku

<pre>USE [Opening1]
GO
/****** Object:  StoredProcedure [dbo].[HSR] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Alter PROC [dbo].[Sample]
AS
BEGIN
Declare @Showroomcode varchar(50),
@Startdate date,
@Stockno int, 
@Servicetag varchar(50),
@TransType int,
@Qty int,
@OP int
 
BEGIN
DECLARE Sam Cursor
STATIC FOR 
select a.showroomcode,a.startdate from 
(select distinct showroomcode,startdate from IMTable) a 
left join (select distinct Showroomcode,Startdate from HSR_OP_Bal ) b
on a.showroomcode=b.showroomcode
and   a.StartDate=b.StartDate
where b.showroomcode is null and a.Startdate='2015-09-01'
order by a.Startdate

OPEN Sam 
IF @@CURSOR_ROWS > 0
BEGIN 
FETCH NEXT FROM Sam  INTO @Showroomcode,@Startdate
WHILE @@FETCH_STATUS = 0
begin

Select Showroomcode,Stockno,Servicetag,Sum(Qty)as Qty
from
(
	( Select H.showroomcode,H.Stockno,H.Servicetag,H.OP as Qty from HSR_OP_Bal H,
	   (Select showroomcode ,Stockno,Startdate from IMTable) I
	   
	where H.Showroomcode=I.Showroomcode and H.Stockno=I.Stockno 
	and I.Startdate='2015-09-01' ) 

union all 

	(  select a.Showroomcode,a.Stockno,a.Servicetag,
		(case when TransType in (1100,1300) then Qty*1
			else  Qty* -1
		  end ) as Qty  from ConsIMEItrndata a, 
  
(Select  Showroomcode,Stockno,Startdate from IMTable) B
	 
	where A.Showroomcode=B.Showroomcode and A.Stockno=B.Stockno
	and B.Startdate ='2015-09-01'  and a.Transdate <'2015-09-01' ) )T
	
Group by Showroomcode,Stockno,Servicetag
FETCH NEXT FROM Sam  INTO @Showroomcode,@Startdate
 end
  end


    CLOSE Sam 
	DEALLOCATE Sam 
  end
  end

推荐答案

我们没有您的数据，也没有关于您的表的知识。因此，不可能说出语句的逻辑错误。



但是，你可以做的是运行游标语句并查看重复的内容。例如，请使用语句

We don't have your data nor the knowledge about your tables. Because of this it's impossible t say what is logically wrong with the statements.

However, what you can do is to run the cursor statement and see what is duplicated. For example take the statement

Select Showroomcode,Stockno,Servicetag,Sum(Qty)as Qty
from
(
	( Select H.showroomcode,H.Stockno,H.Servicetag,H.OP as Qty from HSR_OP_Bal H,
	   (Select showroomcode ,Stockno,Startdate from IMTable) I
	   
	where H.Showroomcode=I.Showroomcode and H.Stockno=I.Stockno 
	and I.Startdate='2015-09-01' ) 
 
union all 
 
	(  select a.Showroomcode,a.Stockno,a.Servicetag,
		(case when TransType in (1100,1300) then Qty*1
			else  Qty* -1
		  end ) as Qty  from ConsIMEItrndata a, 
  
(Select  Showroomcode,Stockno,Startdate from IMTable) B
	 
	where A.Showroomcode=B.Showroomcode and A.Stockno=B.Stockno
	and B.Startdate ='2015-09-01'  and a.Transdate <'2015-09-01' ) )T
	
Group by Showroomcode,Stockno,Servicetag

并在SSMS中运行它。您可以使用星号替换列，以便更轻松地查看重复的数据以及每行的不同之处。这有助于您查看导致重复的原因。如果从UNION的两侧返回相同的数据，则可能的原因是UNION结构。

and run it in SSMS. You can replace the columns with an asterisk so you would more easily see what data is repeated and what is different on each row. This helps you to see what causes the duplication. On probable cause is the UNION structure if the same data is returned from both sides of the UNION.

这篇关于Sql Cursor两次给出相同的结果，如何解决？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！