[SQL]有趣的數字及字串夾雜排序方法

2016-02-18

6927
0
[SQL]TSQL語法技巧
2016-02-17

有時我們會遇見有人在資料表設計中用單一欄位存放多重階層

例如 : A-1 A-2-1 A-11這樣存放

但用字串存放含數字的階層，在Order By該欄位時就會有問題了

如上面3筆資料我們希望是A-1,A-2,A-11排序。但是SQL在Order By該欄位後會跑出A-1,A-11,A-2這樣的結果

先建立範例資料表

CREATE TABLE [dbo].[TestTable](
	[GP] [char](1) NULL,
	[DT] [varchar](10) NULL
) ON [PRIMARY]

GO
Insert Into [TestTable] Values('A','A-1')
Insert Into [TestTable] Values('A','A-2')
Insert Into [TestTable] Values('A','A-11')
Insert Into [TestTable] Values('B','B-3')
Insert Into [TestTable] Values('B','B-2')
Insert Into [TestTable] Values('B','B-5')
Insert Into [TestTable] Values('C','C-1-1')
Insert Into [TestTable] Values('C','C-1-3')
Insert Into [TestTable] Values('C','C-1-11')
Insert Into [TestTable] Values('C','C-2-11')
GO

然後我們Select並Order By DT這一個欄位，結果會如下圖。A-11跑到A-2前面，明顯不是我要的結果

我想了很久，也許在每一組數字前補0會是個解法，例如A-11變成A-011，A-2變成A-002。這樣一來就可以根據我要的方式來排序。網路上google了很多類似的作法，找到一個個人覺得超強的做split語法。然後自己再修改一下就可以補0後再排序了，語法如下

Select * From TestTable Order By
(Select Stuff((select '-' + name from (SELECT right('000'+SUBSTRING(DT, Number, CHARINDEX('-', DT + '-', Number) - Number),3) as 'name'
FROM (SELECT ROW_NUMBER() OVER (ORDER BY [object_id]) FROM sys.all_objects) AS n(Number)
WHERE Number <= CONVERT(INT, LEN(DT)) AND SUBSTRING('-' + DT, Number, LEN('-')) = '-'
) a for xml path (''),type).value('.','VARCHAR(max)'),1,2,'0'))
GO

注意 : 我只是覺得該語法很很有趣所以用這一篇筆記一下，但這樣的作法在資料很多的狀況下效能會很慘，所以應該在資料庫設計初期就避掉這樣的問題才是正道

當然也可以建一個Function來專門做補0的動作，效能會比上面的方式好，Code如下。

Create FUNCTION [dbo].[fnGetNumberFromString2] (@str VARCHAR(255)) 
RETURNS VARCHAR(255) 
AS 
BEGIN
declare @strtmp varchar(255);
set @strtmp='';
while CHARINDEX('-',@str) > 0
	begin
		set @strtmp = @strtmp + right('000' + SUBSTRING(@str,1,CHARINDEX('-',@str,1)),4);
		set @str = SUBSTRING(@str,CHARINDEX('-',@str,1) + 1,len(@str)-len(SUBSTRING(@str,1,CHARINDEX('-',@str,1))));
	end
	set @strtmp=@strtmp+right('000'+@str,3);
	return @strtmp;
end

執行結果如下圖