SQL Server查询增量 [英] Sql server query for increment

问题描述

为什么我的这个查询不能正常工作，它不能递增，请告诉我我哪里出错了
我想增加count_mem 2,3等等，但是不起作用
regCode是存储varchar值的数据库字段名称.

Why my this query is not working properly its not increment please tell me where i go wrong
i want to increment count_mem 2,3 and so on but its not working
regCode is DataBase field name which store varchar value

DECLARE @regCode varchar(50)
DECLARE @count_mem int
SET @count_mem=0

    IF(@regCode=regCode AND @count_mem=1)AND(@regCode=regCode AND @count_mem=1)
    BEGIN
        UPDATE Registration_Master SET count_mem=@count_mem+1
        WHERE sponcorid=@sponcorid
    END

SELECT regCode,sponcorid,side,count_mem FROM Registration_Master

推荐答案

在函数下方创建

Create below Function

CREATE FUNCTION dbo.fn_USER_IncrementAlphaNumericString
(
@STRING VARCHAR(255)
)
/*****************************************************************
** Name : Sequential Alpha Numeric String Incrementer
**
** Description : This function will increment the given alpha
**	 numeric string in sequential order up to 255
**	 characters. (The length can be easily modified)
**

**
*****************************************************************/
RETURNS varchar(255) AS
BEGIN
DECLARE @REV_STRING VARCHAR(255),
@POSITION INT,
@CURR_CHAR CHAR(1),
@CURR_CHAR_TYPE VARCHAR(20),
@NEW_CHAR CHAR(1),
@PREV_CHAR CHAR(1),
@PREV_CHAR_TYPE VARCHAR(20),
@NEW_STRING VARCHAR(255),
@INCR_CHAR BIT,
@DEBUG VARCHAR(4000),
@CR CHAR(1)

--SET INITIAL DEFAULTS FOR VARIABLES
SET @INCR_CHAR = 1
SET @POSITION = 1
SET @REV_STRING = REVERSE(@STRING)
SET @NEW_STRING = ''
SET @DEBUG = ''
SET @CR = CHAR(10)

SET @DEBUG = @DEBUG + '---- STRING: ' + @STRING + ' ----' + @CR + @CR

--LOOP THROUGH ALL CHARACTERS IN REVERSE ORDER
--REVERSE ORDER SO THAT WHEN A CHARACTER IS AT ITS
--MAX VALUE AND THAT VALUE NEEDS TO BE RESET AND THE
--INCREMENT CARRIED TO THE NEXT CHARACTER ITS SIMPLER
WHILE @POSITION <= LEN(@STRING) BEGIN

SET @DEBUG = @DEBUG + '---- POSITION: ' + CAST(@POSITION AS VARCHAR(50)) + ' --(' + CAST(@INCR_CHAR AS CHAR(1)) + ')--' + @CR

--GET THE CURRENT POSITION CHARACTER
SET @CURR_CHAR = SUBSTRING(@REV_STRING, @POSITION, 1)
SET @NEW_CHAR = @CURR_CHAR

--DETERMING CHARACTER TYPE
IF @CURR_CHAR LIKE '[0-9]' SET @CURR_CHAR_TYPE = 'NUMERIC'
ELSE IF @CURR_CHAR LIKE '[A-Z]' AND ASCII(@CURR_CHAR) >= 97 AND ASCII(@CURR_CHAR) <= 122 SET @CURR_CHAR_TYPE = 'LOWER ALPHA'
ELSE IF @CURR_CHAR LIKE '[A-Z]' AND ASCII(@CURR_CHAR) >= 65 AND ASCII(@CURR_CHAR) <= 90 SET @CURR_CHAR_TYPE = 'UPPER ALPHA'
ELSE SET @CURR_CHAR_TYPE = 'SYMBOL'

SET @DEBUG = @DEBUG + 'CURRENT_CHAR: ' + @CURR_CHAR + @CR
SET @DEBUG = @DEBUG + 'CURRENT_CHAR_TYPE: ' + @CURR_CHAR_TYPE + @CR

--CHECK INCREMENT BIT TO SEE IF WE NEED TO INCREMENT THIS CHARACTER
IF @INCR_CHAR = 1 BEGIN
--INCREMENT IS NEEDED

IF @CURR_CHAR_TYPE = 'NUMERIC' BEGIN
--PERFORM NUMERICAL INCREMENT TO THIS CHARACTER
IF @CURR_CHAR = '9' BEGIN
--CHARACTER IS AT ITS MAX SEQUENCABLE VALUE
--RESET THE SEQUENCE AND CARRY THE INCREMENT
--TO THE NEXT CHARACTER
SET @NEW_CHAR = '0'
END ELSE BEGIN
--CONVERT THE CHARACTER TO A NUMBER AND ADD 1 THEN CONVERT BACK TO A CHARACTER
SET @NEW_CHAR = CAST(CAST(@CURR_CHAR AS INT) + 1 AS CHAR(1))
--INCREMENT BIT IS SET TO 0 BECAUSE INCREMENT WAS COMPLETED
SET @INCR_CHAR = 0
END
--ADD NEW CHARACTER TO STRING BUILT IN REVERSE ORDER SO THAT
--THE FINAL RECONSTRUCTION IS IN THE ORIGINAL ORDER
SET @NEW_STRING = @NEW_CHAR + @NEW_STRING
END

ELSE IF @CURR_CHAR_TYPE = 'LOWER ALPHA' BEGIN
--PERFORM ALPHABETIC INCREMENT TO THIS CHARACTER
IF @CURR_CHAR = 'z' BEGIN
--CHARACTER IS AT ITS MAX SEQUENCABLE VALUE
--RESET THE SEQUENCE AND CARRY THE INCREMENT
--TO THE NEXT CHARACTER
SET @NEW_CHAR = 'a'
END ELSE BEGIN
--CONVERT THE CHARACTER TO AN ASCII NUMBER AND ADD 1 THEN CONVERT BACK TO A CHARACTER
SET @NEW_CHAR = CHAR(ASCII(@CURR_CHAR) + 1)
--INCREMENT BIT IS SET TO 0 BECAUSE INCREMENT WAS COMPLETED
SET @INCR_CHAR = 0
END
--ADD NEW CHARACTER TO STRING BUILT IN REVERSE ORDER SO THAT
--THE FINAL RECONSTRUCTION IS IN THE ORIGINAL ORDER
SET @NEW_STRING = @NEW_CHAR + @NEW_STRING
END

ELSE IF @CURR_CHAR_TYPE = 'UPPER ALPHA' BEGIN
--PERFORM ALPHABETIC INCREMENT TO THIS CHARACTER
IF @CURR_CHAR = 'Z' BEGIN
--CHARACTER IS AT ITS MAX SEQUENCABLE VALUE
--RESET THE SEQUENCE AND CARRY THE INCREMENT
--TO THE NEXT CHARACTER
SET @NEW_CHAR = 'A'
END ELSE BEGIN
--CONVERT THE CHARACTER TO AN ASCII NUMBER AND ADD 1 THEN CONVERT BACK TO A CHARACTER
SET @NEW_CHAR = CHAR(ASCII(@CURR_CHAR) + 1)
--INCREMENT BIT IS SET TO 0 BECAUSE INCREMENT WAS COMPLETED
SET @INCR_CHAR = 0
END
--ADD NEW CHARACTER TO STRING BUILT IN REVERSE ORDER SO THAT
--THE FINAL RECONSTRUCTION IS IN THE ORIGINAL ORDER
SET @NEW_STRING = @NEW_CHAR + @NEW_STRING
END

ELSE BEGIN
--IF INCREMENT IS NEEDED BUT CHARACTER TYPE IS NOT
--ABLE TO BE INCREMENTED SUCH AS A SYMBOL THEN WE
--WILL PASS ON THE CURRENT CHARCTER AND THE NEXT
--CHARACTER WILL ATTEMPT TO GET INCREMENTED (CARRY THE INCREMENT)
SET @NEW_STRING = @CURR_CHAR + @NEW_STRING
END
END ELSE BEGIN
--NO INCREMENT WAS NEEDED SO THE CURRENT CHARACTER IS PASSED ON
SET @NEW_STRING = @CURR_CHAR + @NEW_STRING
END

SET @DEBUG = @DEBUG + 'NEW_CHAR: ' + @NEW_CHAR + @CR
SET @DEBUG = @DEBUG + 'NEW_STRING: ' + @NEW_STRING + @CR
SET @DEBUG = @DEBUG + 'POST_INCR INCR_CHAR: ' + CAST(@INCR_CHAR AS CHAR(1)) + @CR

--ADD ADDITIONAL CHARACTER IF NEEDED
IF @POSITION = LEN(@STRING) AND @INCR_CHAR = 1 BEGIN
--WE ARE AT THE LAST POSITION AND AN INCREMENT STILL NEEDS TO BE
--PERFORMED, SO WE MUST ADD AN ADDITIONAL CHARACTER TO THE STRING SINCE
--ALL OTHER POSITIONS WERE NOT ABLE TO BE INCREMENTED OR WERE AT THEIR
--MAXIMUM SEQUENCE
IF @CURR_CHAR_TYPE = 'NUMERIC' SET @NEW_STRING = '1' + @NEW_STRING
ELSE IF @CURR_CHAR_TYPE = 'LOWER ALPHA' SET @NEW_STRING = 'a' + @NEW_STRING
ELSE IF @CURR_CHAR_TYPE = 'UPPER ALPHA' SET @NEW_STRING = 'A' + @NEW_STRING
ELSE SET @NEW_STRING = 'a' + @NEW_STRING

--IF THE NUMERIC OR ALPHA CHARACTER TYPES ARE NOT MATCHED WE DEFAULT TO
--ADDING A LOWER CASE ALPHA CHARACTER
END

SET @DEBUG = @DEBUG + 'POST_ADD NEW_STRING: ' + @NEW_STRING + @CR

--SET THE PREVIOUS CHARACTER AND TYPE FOR THE NEXT ITERATION OF THE LOOP
SET @PREV_CHAR = @CURR_CHAR
SET @PREV_CHAR_TYPE = @CURR_CHAR_TYPE

--INCREMENT THE POSITION SO THE NEXT ITERATION MOVES TO THE NEXT CHARACTER
SET @POSITION = @POSITION + 1
END

RETURN @NEW_STRING
END
GO

示例代码

Sample Code

select dbo.fn_USER_IncrementAlphaNumericString('P4UPMA00075341')

输出为
P4UPMA00075342

Output is
P4UPMA00075342

这篇关于SQL Server查询增量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！