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

Question

表有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
2  5   T   G   G   M   M   M   P   P   L   L    C   A    G    Q    N    N    N    N    N    B
.....

Answer 1

对的，呵呵写错了！谢谢指正！
表有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
.....

Answer 2

CREATE TABLE TB([ID] INT, [CA] INT, [D1] VARCHAR(1), [D2] VARCHAR(1), [D3] VARCHAR(1), [D4] VARCHAR(1), [D5] VARCHAR(1), [D6] VARCHAR(1), [D7] VARCHAR(1), [D8] VARCHAR(1), [D9] VARCHAR(1), [D10] VARCHAR(1), [D11] VARCHAR(1), [D12] VARCHAR(1), [D13] VARCHAR(1),
                  [D14] VARCHAR(1), [D15] VARCHAR(1), [D16] VARCHAR(1), [D17] VARCHAR(1), [D18] VARCHAR(1), [D19] VARCHAR(1), [D20] VARCHAR(1))
INSERT TB 
SELECT 1, 4, 'A', 'B', 'B', 'C', 'C', 'B', 'B', 'B', 'B', 'A', 'A', 'A', 'E', 'E', 'G', 'G', 'F', 'F', 'T', 'W' UNION ALL 
SELECT 2, 6, 'G', 'G', 'R', 'R', 'R', 'R', 'R', 'R', 'A', 'A', 'C', 'C', 'T', 'T', 'F', 'F', 'F', 'F', 'H', 'H' UNION ALL 
SELECT 3, 5, 'T', 'G', 'G', 'M', 'M', 'M', 'P', 'P', 'L', 'L', 'C', 'A', 'G', 'Q', 'N', 'N', 'N', 'N', 'N', 'B'DECLARE @STR VARCHAR(MAX)
SET @STR=''SELECT @STR=@STR+' UNION ALL SELECT ID,ASCII('+NAME+') AS CODE,'+RTRIM(COLID)+' AS COLID FROM TB ' -- SELECT NAME
FROM SYSCOLUMNS
WHERE ID=OBJECT_ID('TB') AND NAME NOT IN ('ID','CA')
ORDER BY COLIDSET @STR=STUFF(@STR, 1, 10, '')EXEC('
;WITH CTE AS
(
'+@STR+'
),
CTE2 AS
(
SELECT *,(SELECT COUNT(*) FROM CTE WHERE ID=T.ID AND CODE=T.CODE AND COLID<=T.COLID) AS CNT FROM CTE AS T
)SELECT ID,MAX(C) AS MAXNUM
FROM (
SELECT ID,GRP,CODE,COUNT(*) AS C
FROM (
SELECT *,COLID-CNT AS GRP FROM CTE2
) T
GROUP BY ID,GRP,CODE
) T
GROUP BY ID')DROP TABLE TB
/*
ID          MAXNUM
----------- -----------
1           4
2           6
3           5
*/

Answer 3

MSSQL多列取最大或者最小值 --整理帖

Answer 4

if OBJECT_ID('tb') is not null 
drop table tb 
go
CREATE TABLE TB([ID] INT, [CA] INT, [D1] VARCHAR(1), [D2] VARCHAR(1), [D3] VARCHAR(1), [D4] VARCHAR(1), [D5] VARCHAR(1), [D6] VARCHAR(1), [D7] VARCHAR(1), [D8] VARCHAR(1), [D9] VARCHAR(1), [D10] VARCHAR(1), [D11] VARCHAR(1), [D12] VARCHAR(1), [D13] VARCHAR(1),
                  [D14] VARCHAR(1), [D15] VARCHAR(1), [D16] VARCHAR(1), [D17] VARCHAR(1), [D18] VARCHAR(1), [D19] VARCHAR(1), [D20] VARCHAR(1))
INSERT TB 
SELECT 1, 4, 'A', 'B', 'B', 'C', 'C', 'B', 'B', 'B', 'B', 'A', 'A', 'A', 'E', 'E', 'G', 'G', 'F', 'F', 'T', 'W' UNION ALL 
SELECT 2, 6, 'G', 'G', 'R', 'R', 'R', 'R', 'R', 'R', 'A', 'A', 'C', 'C', 'T', 'T', 'F', 'F', 'F', 'F', 'H', 'H' UNION ALL 
SELECT 3, 5, 'T', 'G', 'G', 'M', 'M', 'M', 'P', 'P', 'L', 'L', 'C', 'A', 'G', 'Q', 'N', 'N', 'N', 'N', 'N', 'B'DECLARE @STR VARCHAR(MAX)
SET @STR=''SELECT @STR=@STR+' UNION ALL' +char(10)+'SELECT ID,ASCII('+NAME+') AS CODE,'+RTRIM(COLID)+' AS COLID FROM TB '+char(10)  -- SELECT NAME
FROM SYSCOLUMNS
WHERE ID=OBJECT_ID('TB') AND NAME NOT IN ('ID','CA')
ORDER BY COLID
   
SET @STR=STUFF(@STR, 1, 10, '')
--print @str
EXEC('
;WITH CTE AS
(
 select rn=colid-ROW_NUMBER()over(partition by id,code order by colid ),* from  ('+@STR+') k
),
CTE2 AS 
(
select id,code,rn,max(colid)-min(colid)+1 as maxnum from cte group by id,code,rn
)
select id,max(maxnum) as  maxnum from cte2 group by id ')
/*
id          maxnum
----------- -----------
1           4
2           6
3           5*/

Answer 5

/*------------------------------------------------------------------
--  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)------------------------------------------------------------------*/
--> 生成测试数据表:tbIF 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
GOUPDATE 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
*/

Answer 6

--> 生成测试数据表:tbIF 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]
--drop table #
SELECT ID,A,B=0
INTO #
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
DECLARE @I INT,@J VARCHAR(20)        
UPDATE # SET B = @I,@I = CASE WHEN @J = A THEN @I +1 ELSE 1 END,@J = A 
UPDATE A set 
a.[CA]=B.CA
FROM tb A 
JOIN (SELECT ID,MAX(B) CA FROM # GROUP BY ID) B
ON A.[ID]=B.[ID]

SELECT * FROM tb这样应该会比较快。

Answer 7

试下这个,思路:
第一步和楼上类似,把表先变成这种形式:(基本上时间都是消耗在这一步)
  ID FID C_VALUE
  1   1    'A'
  1   2    'B'
  1   3    'B'
....
第二步用求连续区域的方法得出结果.
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
WITH T1(ID,FID,C_VALUE)
AS
(
SELECT ID,FID =1,D1 FROM tb UNION SELECT ID,FID =2,D2 FROM tb UNION SELECT ID,FID =3,D3 FROM tb UNION SELECT ID,FID =4,D4 FROM tb UNION SELECT ID,FID =5,D5 FROM tb UNION SELECT ID,FID =6,D6 FROM tb UNION SELECT ID,FID =7,D7 FROM tb UNION SELECT ID,FID =8,D8 FROM tb UNION SELECT ID,FID =9,D9 FROM tb UNION SELECT ID,FID =10,D10 FROM tb UNION SELECT ID,FID =11,D11 FROM tb UNION SELECT ID,FID =12,D12 FROM tb UNION SELECT ID,FID =13,D13 FROM tb UNION SELECT ID,FID =14,D14 FROM tb UNION SELECT ID,FID =15,D15 FROM tb UNION SELECT ID,FID =16,D16 FROM tb UNION SELECT ID,FID =17,D17 FROM tb UNION SELECT ID,FID =18,D18 FROM tb UNION SELECT ID,FID =19,D19 FROM tb UNION SELECT ID,FID =20,D20 FROM Tb
),
T2 AS(
SELECT ID,C_VALUE,Grp,Cnt = COUNT(FID) FROM
(SELECT ID,FID,C_VALUE,Grp = FID - ROW_NUMBER() OVER (ORDER BY ID,C_VALUE) FROM T1) B
GROUP By ID,C_VALUE,Grp
)
UPDATE Tb SET Tb.CA = Tmp.Cnt FROM Tb INNER JOIN
(select ID,MAX(Cnt) from t2 GROUP BY ID) Tmp ON Tb.ID = Tmp.ID

Answer 8

上面最后一行少了点东西,更正一下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
WITH T1(ID,FID,C_VALUE)
AS
(
SELECT ID,FID =1,D1 FROM tb UNION SELECT ID,FID =2,D2 FROM tb UNION SELECT ID,FID =3,D3 FROM tb UNION SELECT ID,FID =4,D4 FROM tb UNION SELECT ID,FID =5,D5 FROM tb UNION SELECT ID,FID =6,D6 FROM tb UNION SELECT ID,FID =7,D7 FROM tb UNION SELECT ID,FID =8,D8 FROM tb UNION SELECT ID,FID =9,D9 FROM tb UNION SELECT ID,FID =10,D10 FROM tb UNION SELECT ID,FID =11,D11 FROM tb UNION SELECT ID,FID =12,D12 FROM tb UNION SELECT ID,FID =13,D13 FROM tb UNION SELECT ID,FID =14,D14 FROM tb UNION SELECT ID,FID =15,D15 FROM tb UNION SELECT ID,FID =16,D16 FROM tb UNION SELECT ID,FID =17,D17 FROM tb UNION SELECT ID,FID =18,D18 FROM tb UNION SELECT ID,FID =19,D19 FROM tb UNION SELECT ID,FID =20,D20 FROM Tb
),
T2 AS(
SELECT ID,C_VALUE,Grp,Cnt = COUNT(FID) FROM
(SELECT ID,FID,C_VALUE,Grp = FID - ROW_NUMBER() OVER (ORDER BY ID,C_VALUE) FROM T1) B
GROUP By ID,C_VALUE,Grp
)
UPDATE Tb SET Tb.CA = Tmp.Cnt FROM Tb INNER JOIN
(select ID,Cnt = MAX(Cnt) from t2 GROUP BY ID) Tmp ON Tb.ID = Tmp.ID

Answer 9

假设你知道有多少个D字段而且是连续的,把他在下面给@DCOUNT赋值DECLARE @DCOUNT INT,@LOOP INT
DECLARE @SQL VARCHAR(MAX)
SET @DCOUNT = 100,@LOOP = 1
WHILE @LOOP <= @DCOUNT
BEGIN
  IF @LOOP = 1
    SET @SQL = 'SELECT ID,FID =1,D1 FROM tb '
  ELSE
    SET @SQL = @SQL + 'UNION SELECT ID,FID =' + CAST(@LOOP AS VARCHAR(4)) + ',D' + 
      CAST(@LOOP AS VARCHAR(4)) + ' FROM tb '
  SET @LOOP = @LOOP + 1
ENDSET @SQL = ';WITH T1(ID,FID,C_VALUE)
AS
(' + @SQL +
'),
T2 AS(
SELECT ID,C_VALUE,Grp,Cnt = COUNT(FID) FROM
(SELECT ID,FID,C_VALUE,Grp = FID - ROW_NUMBER() OVER (ORDER BY ID,C_VALUE) FROM T1) B
GROUP By ID,C_VALUE,Grp
)
UPDATE Tb SET Tb.CA = Tmp.Cnt FROM Tb INNER JOIN
(select ID,Cnt = MAX(Cnt) from t2 GROUP BY ID) Tmp ON Tb.ID = Tmp.ID'EXEC (@SQL)