字段内数字排序

[Yole]

问1：把a 变成b （ b为a的数字升序排列）
a b
839 389
355 355
360 036
360 036

问2：把a 变成b （把a中字符串用逗号分开后升序排列然后去重再组合成b）
a b
839,355,360,360 389,355, 036
819,355,360,360 189,355, 036
839,345,362,360 389,345,236, 036

[發糞塗牆]

没有分隔符不好处理，要是有的话，可以先转成行排序后再转成列，但是现在看来可能只能用case when或者游标之类的来匹配或者实现转列再转行

[向东流]

写了个函数 select a,f(a)

CREATE FUNCTION f(@s VARCHAR (100))
RETURNS  VARCHAR(100)
AS
BEGIN
DECLARE @t TABLE ( c VARCHAR(1),o INT)
DECLARE @r VARCHAR(100)
DECLARE @iLen INT
DECLARE @i INT
DECLARE @o INT
SET @r=''
SET @iLen=LEN(@s)
SET @i=1
SET @o=1
WHILE @i<=@iLen
BEGIN
	IF SUBSTRING(@s,@i,1)=','
		BEGIN
		INSERT INTO @t VALUES('Z',@o)
		SET @o=@o+1
		END
	ELSE
		BEGIN
		INSERT INTO @t VALUES(SUBSTRING(@s,@i,1),@o)
		END
	SET @i=@i+1
END
SELECT @r=@r+c FROM @t ORDER BY o,c
RETURN  @r
END

[chen357313771]

问一：

IF OBJECT_ID('tempdb..#TempA','U') IS NOT NULL DROP TABLE #TempA
CREATE TABLE #TempA(Id INT IDENTITY(1,1),Txt VARCHAR(100))

INSERT INTO #TempA(Txt)
VALUES( '839'),('355'),('360'),('360')

IF OBJECT_ID('tempdb..#TempB','U') IS NOT NULL DROP TABLE #TempB
CREATE TABLE #TempB
(	
	Rn INT IDENTITY(1,1)
	,ID	INT
	,Num	CHAR(1)
)

DECLARE @Cnt INT=(SELECT MAX(Id) FROM #TempA)
DECLARE @Txt VARCHAR(100)='',@Num CHAR(1)=''

WHILE @Cnt>0
BEGIN
	SELECT @Txt=Txt FROM #TempA WHERE Id=@Cnt
	WHILE LEN(@Txt)>0
		BEGIN
			INSERT INTO #TempB(ID,Num) VALUES(@Cnt,left(@Txt,1))
			SET @Txt=STUFF(@Txt,1,1,'')
		END
	SET @Cnt=@Cnt-1
END

IF OBJECT_ID('tempdb..#TempC','U') IS NOT NULL DROP TABLE #TempC
SELECT ROW_NUMBER() OVER(PARTITION BY id ORDER BY ID,Num) AS ID1 ,ID,Num 
INTO #TempC
FROM #TempB

SELECT DISTINCT A.ID
  ,REPLACE(T.Num,',','') AS EndTxt
FROM #TempC AS A
CROSS APPLY (
	 SELECT Num=STUFF((SELECT ','+B.Num FROM #TempC AS B WHERE A.ID=B.ID FOR XML PATH('')),1,1,'')
) AS T
GROUP BY A.ID,A.Num,t.Num
ORDER BY A.ID