统计每条记录中的最大连续值

原贴地址：http://topic.csdn.net/u/20100410/15/c03f52b8-1ce5-4b85-afed-a15e5450ca6e.html?42506

 

表有ID CA D1 D2 D3 D4 D5 D6 D7 D8 D9 D10 D11 D12 D13 D14 D15 D16 D17 D18 D19 D20 等22个字段
现统计D1-D20（由任意字母组成）最大的连值写如CA字段中。
第一条记录的最大连值为4，是从D6-D9都为相同的字母B;
第二条记录的最大连值为6，是从D3-D8都为相同的字母R;
第三条记录的最大连值为5，是从D15-D19都为相同的字母N;
如表所示
ID CA D1 D2 D3 D4 D5 D6 D7 D8 D9 D10 D11 D12 D13 D14 D15 D16 D17 D18 D19 D20  
1 4 A B B C C B B B B A A A E E G G F F T W  
2 6 G G R R R R R R A A C C T T F F F F H H
3 5 T G G M M M P P L L C A G Q N N N N N B

 

/*------------------------------------------------------------------
--  Author : htl258(Tony)
--  Date   : 2010-04-10 17:38:30
--  Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
    Jul  9 2008 14:43:34
    Copyright (c) 1988-2008 Microsoft Corporation
    Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

------------------------------------------------------------------*/
--> 生成测试数据表:tb

IF OBJECT_ID('[tb]') IS NOT NULL
    DROP TABLE [tb]
GO
CREATE TABLE [tb]([ID] INT,[CA] INT,[D1] NVARCHAR(10),[D2] NVARCHAR(10),[D3] NVARCHAR(10),[D4] NVARCHAR(10),[D5] NVARCHAR(10),[D6] NVARCHAR(10),[D7] NVARCHAR(10),[D8] NVARCHAR(10),[D9] NVARCHAR(10),[D10] NVARCHAR(10),[D11] NVARCHAR(10),[D12] NVARCHAR(10),[D13] NVARCHAR(10),[D14] NVARCHAR(10),[D15] NVARCHAR(10),[D16] NVARCHAR(10),[D17] NVARCHAR(10),[D18] NVARCHAR(10),[D19] NVARCHAR(10),[D20] NVARCHAR(10))
INSERT [tb]
SELECT 1,NULL,'A','B','B','C','C','B','B','B','B','A','A','A','E','E','G','G','F','F','T','W' UNION ALL
SELECT 2,NULL,'G','G','R','R','R','R','R','R','A','A','C','C','T','T','F','F','F','F','H','H' UNION ALL
SELECT 3,NULL,'T','G','G','M','M','M','P','P','L','L','C','A','G','Q','N','N','N','N','N','B'
GO
--SELECT * FROM [tb]

-->SQL查询如下:
IF OBJECT_ID('[FN_GETNUM]') IS NOT NULL
    DROP FUNCTION [FN_GETNUM]
GO
CREATE FUNCTION FN_GETNUM(@ID INT)
RETURNS INT
AS
BEGIN
    DECLARE @T TABLE(A VARCHAR(20),B INT)
    INSERT @T(A)
    SELECT A
    FROM TB
        UNPIVOT(A FOR B IN(
            [D1],[D2],[D3],[D4],[D5],[D6],[D7],[D8],[D9],[D10],[D11],
            [D12],[D13],[D14],[D15],[D16],[D17],[D18],[D19],[D20]
            )) B
     WHERE ID=@ID
    DECLARE @I INT,@J VARCHAR(20)
    UPDATE @T SET
        B = @I,
        @I = CASE WHEN @J = A THEN @I +1 ELSE 1 END,
        @J = A
    RETURN(SELECT MAX(B) FROM @T)
END
GO

UPDATE TB SET [CA]= DBO.FN_GETNUM([ID])

SELECT * FROM TB
/*
ID    CA    D1    D2    D3    D4    D5    D6    D7    D8    D9    D10    D11    D12    D13    D14    D15    D16    D17    D18    D19    D20
1    4    A    B    B    C    C    B    B    B    B    A    A    A    E    E    G    G    F    F    T    W
2    6    G    G    R    R    R    R    R    R    A    A    C    C    T    T    F    F    F    F    H    H
3    5    T    G    G    M    M    M    P    P    L    L    C    A    G    Q    N    N    N    N    N    B
*/