如何在存储过程中使用group by子句 [英] How to use group by having clause in stored procedure

问题描述

大家好，

我有CircleId如下：1,2,3，... 10,11,12,13,14等等。

我有FileType如下：DBBH，NBH，DBH，BBH

我在我的存储过程中完全感到困惑。

我的存储过程中有两个条件如下：

第一条件：如果我发现circleID = 14的记录数大于等于4则不允许保存记录。

第二条件：如果记录是已经存在返回零。

Hi Everyone,
I have CircleId as follows: 1,2,3,...10,11,12,13,14 and so on.
I have FileType as Follows: DBBH,NBH,DBH,BBH
I am totally confused in my Stored procedure.
There are two Condition in my Stored procedure as follows:
First Condition:if i found the number of records greater than equal to 4 for circleID = 14 then it will not allowed to save the record.
Second Condition:if records is already exists return zero.

ALTER PROCEDURE [dbo].[K2_CHECKENTRYINFILELOG] 
@FILENAME VARCHAR(150),
@FILEPATH VARCHAR(MAX),
@FILETYPE int,
@FILEDATE datetime,
@CIRCLEID INT,
@TOTALROWS int,
@SUCCESSCOUNT int,
@FAILURECOUNT int,
@PROCESSED bit,
@ERROR varchar(MAX),
@VENDORID int,
@CREATEDBY INT = 1,
@CREATEDON DATETIME,
@DELETED BIT=0,
@FILELOGID bigINT OUT

AS	
BEGIN	
declare @Count int	
select @Count = count(*) from K2FILELOG where FILETYPE = @FILETYPE and	
                                              FILEDATE = @FILEDATE and	
                                              CIRCLEID = @CIRCLEID and	
                                              Processed = 1   	
IF (@Count = 1)	
BEGIN	
set @FILELOGID = -1	
return;	
  select FILETYPE,FILEDATE,CIRCLEID from K2FILELOG with (nolock) GROUP BY CIRCLEID
   having count(*) >= 4
/* where FILEPATH
          [FILENAME]
          FILETYPE = @FILETYPE and	
          FILEDATE = @FILEDATE and	
          CIRCLEID = @CIRCLEID and
          TOTALROWS =@TOTALROWS and
          SUCCESSCOUNT = @SUCCESSCOUNT and
          FAILURECOUNT = @FAILURECOUNT and
          PROCESSED=@PROCESSED and
          ERROR = @ERROR and
          VENDORID = @VENDORID and
          CREATEDBY =  @CREATEDBY and
          CREATEDON =@CREATEDON and
          DELETED = @DELETED	*/
END	

ELSE IF	
@CIRCLEID >= 14 	
BEGIN	
SET @FILELOGID  = -1	
RETURN ;	
END	
ELSE	
BEGIN	
	INSERT INTO K2FILELOG
	   (FILEPATH, [FILENAME],FILETYPE,FILEDATE,CIRCLEID,TOTALROWS,SUCCESSCOUNT,FAILURECOUNT,PROCESSED,ERROR,VENDORID,CREATEDBY, CREATEDON, DELETED)
values (@FILEPATH ,@FILENAME,@FILETYPE,@FILEDATE,@CIRCLEID,@TOTALROWS,@SUCCESSCOUNT,@FAILURECOUNT,@PROCESSED,@ERROR ,@VENDORID,@CREATEDBY,@CREATEDON,@DELETED);	
 set @FILELOGID = @@identity;	
END	
	
END	

推荐答案

请参阅此链接以了解如何使用Group by和having子句



http://www.dotnet-tricks.com/Tutorial/sqlserver/HQV8310312-Definition,-Use-of-Group-by-and-Having-Clause.html [ ^ ]

refer this link for how to use Group by and having clause

http://www.dotnet-tricks.com/Tutorial/sqlserver/HQV8310312-Definition,-Use-of-Group-by-and-Having-Clause.html[^]

这篇关于如何在存储过程中使用group by子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！