我正在尝试创建一个SQL查询，该查询将按版本号（例如1.1，4.5.10等）排序结果。
这是我尝试过的：
SELECT * FROM Requirements
WHERE Requirements.Release NOT LIKE %Obsolete%
ORDER BY Requirements.ReqNum
现在，ReqNum字段是一个字符串字段，不幸的是，我不能将其改为浮点数或类似的东西，因为我有类似162.1.11的需求编号。
当我收到结果时，我会按照以下顺序订购：
1.1
1.10
1.11
1.3
如何编写一个按字典顺序排序的查询？
或者,
我该如何正确排序数据？
提前感谢您的参与！
为了获得最佳结果，请重构版本号存储，使每个部分都有自己的列：主版本、次版本、修订版本、构建版本。然后排序问题就变得非常简单。您还可以构建一个计算列，以轻松检索完整的字符串。
在PostgreSQL中，您可以做到：
SELECT * FROM Requirements
ORDER BY string_to_array(version, . )::int[];
这个 ::int[] 表示将字符串值转换为整数，然后进行比较。
SELECT * FROM Requirements
WHERE Requirements.Release NOT LIKE %Obsolete%
ORDER BY cast( / + replace(Requirements.ReqNum , . , / ) + / as hierarchyid);
@vuttipong-l的答案略有变化（T-SQL）
SELECT VersionNumber
FROM (
SELECT 6.1.3 VersionNumber UNION
SELECT 6.11.3 UNION
SELECT 6.2.3 UNION
SELECT 6.1.12
) AS q
ORDER BY cast( / + VersionNumber + / as hierarchyid)
Works in SQL Server starting with 2008, dots are OK in a string representation of a hierarchyid column, so we don t need to replace them with slashes.
A quote from the doc:
Comparison is performed by comparing the integer sequences separated by dots in dictionary order.
但有一个警告：版本段不能以零为前缀。
如果你在SQL Server领域...
DECLARE @string varchar(40)
SET @string = 1.2.3.4
SELECT PARSENAME(@string, 1), PARSENAME(@string, 2), PARSENAME(@string, 3), PARSENAME(@string, 4)
Results: 4, 3, 2, 1
有用于解析IP地址和其他带点项目，例如版本号。 （您也可以使用REPLACE（）将项目转换为点状符号...例如 1-2-3-4 -> 1.2.3.4）
如果你不像乔尔·科霍恩明智建议那样重新设计表格，那么你需要重新格式化版本号为一个按你要求排序的字符串，例如。
如果您的数据库管理系统具有此功能，可以使用函数或计算/虚拟列来完成此操作。然后，您可以在ORDER BY子句中使用该函数或列。
以下函数将获取一个版本号，并将每个级别格式化为3位数字：
用法：
select * from TableX order by dbo.fn_VersionPad(VersionCol1)
功能：
CREATE FUNCTION [dbo].[fn_VersionPad]
(
@version varchar(20)
)
RETURNS varchar(20)
AS
BEGIN
/*
Purpose: Pads multi-level Version Number sections to 3 digits
Example: 1.2.3.4
Returns: 001.002.003.004
*/
declare @verPad varchar(20)
declare @i int
declare @digits int
set @verPad =
set @i = len(@version)
set @digits = 0
while @i > 0
begin
if (substring(@version, @i, 1) = . )
begin
while (@digits < 3)
begin
-- Pad version level to 3 digits
set @verPad = 0 + @verPad
set @digits = @digits + 1
end
set @digits = -1
end
set @verPad = substring(@version, @i, 1) + @verPad
set @i = @i - 1
set @digits = @digits + 1
end
while (@digits < 3)
begin
-- Pad version level to 3 digits
set @verPad = 0 + @verPad
set @digits = @digits + 1
end
return @verPad
END
你可以用CHARINDEX / SUBSTR分解字符串（你已经知道了定界符：“。”）并按不同部分ORDER BY排序。 可以将其放入函数中或分开做。
它不会很漂亮，也不会很快：如果您需要快速查询，请跟随Tony或Joel。
不使用代码
Insert into @table
Select A1 union all
Select A3 union all
Select A5 union all
Select A15 union all
Select A11 union all
Select A10 union all
Select A2 union all
Select B2 union all
Select C2 union all
Select C22 union all
Select C221 union all
Select A7
Select cod from @table
Order by LEN(cod),cod
结果：
A1
A2
A3
A5
A7
B2
C2
A10
A11
A15
C22
C221
这很简单：
Declare @table table(id_ int identity(1,1), cod varchar(10))
Insert into @table
Select A1 union all
Select A3 union all
Select A5 union all
Select A15 union all
Select A11 union all
Select A10 union all
Select A2 union all
Select A7
Select cod from @table
Order by LEN(cod),cod
在PostgreSQL上，这再容易不过了：
SELECT ver_no FROM version ORDER BY string_to_array(ver_no, '.' , )::int[] 选择 ver_no FROM version 按 string_to_array(ver_no, '.' , )::int[] 排序
如果您正在使用Microsoft SQL Server，这将起作用：
create function fnGetVersion (@v AS varchar(50)) returns bigint as
begin
declare @n as bigint;
declare @i as int;
select @n = 0;
select @i = charindex( . ,@v);
while(@i > 0)
begin
select @n = @n * 1000;
select @n = @n + cast(substring(@v,1,@i-1) as bigint);
select @v = substring(@v,@i+1,len(@v)-@i);
select @i = charindex( . ,@v);
end
return @n * 1000 + cast(@v as bigint);
end
通过运行此命令进行测试：
select dbo.fnGetVersion( 1.2.3.4 )
这将返回一个完全可排序的数字1002003004。如果你需要9.0.1比2.1.2.3大，那么你需要稍微改变一下逻辑。在我的例子中，9.0.1将在2.1.2.3之前排序。
PostgreSQL功能
只需使用
select *
from sample_table
order by _sort_version(column_version);
CREATE FUNCTION _sort_version (
p_version text
)
RETURNS text AS
$body$
declare
v_tab text[];
begin
v_tab := string_to_array(p_version, . );
for i in 1 .. array_length(v_tab, 1) loop
v_tab[i] := lpad(v_tab[i], 4, 0 );
end loop;
return array_to_string(v_tab, . );
end;
$body$
LANGUAGE plpgsql
VOLATILE
CALLED ON NULL INPUT
SECURITY DEFINER
COST 1;
我曾经遇到同样的问题，不过我的问题是公寓编号，比如A1、A2、A3、A10、A11等，他们想要正确排序。如果将版本号拆分成单独的列不起作用，请尝试使用这个PL/SQL。它将类似A1或A10的字符串扩展为A0000001、A0000010等，以便进行排序。只需在ORDER BY子句中调用它即可。
select apt_num from apartment order by PAD(apt_num)
function pad(inString IN VARCHAR2)
return VARCHAR2
--This function pads the numbers in a alphanumeric string.
--It is particularly useful in sorting, things like "A1, A2, A10"
--which would sort like "A1, A10, A2" in a standard "ORDER BY name" clause
--but by calling "ORDER BY pkg_sort.pad(name)" it will sort as "A1, A2, A10" because this
--function will convert it to "A00000000000000000001, A00000000000000000002, A00000000000000000010"
--(but since this is in the order by clause, it will
--not be displayed.
--currently, the charTemplate variable pads the number to 20 digits, so anything up to 99999999999999999999
--will work correctly.
--to increase the size, just change the charTemplate variable. If the number is larger than 20 digits, it will just
--appear without padding.
is
outString VARCHAR2(255);
numBeginIndex NUMBER;
numLength NUMBER;
stringLength NUMBER;
i NUMBER;
thisChar VARCHAR2(6);
charTemplate VARCHAR2(20) := 00000000000000000000 ;
charTemplateLength NUMBER := 20;
BEGIN
outString := null;
numBeginIndex := -1;
numLength := 0;
stringLength := length(inString);
--loop through each character, get that character
FOR i IN 1..(stringLength) LOOP
thisChar := substr(inString, i, 1);
--if this character is a number
IF (FcnIsNumber(thisChar)) THEN
--if we haven t started a number yet
IF (numBeginIndex = -1) THEN
numBeginIndex := i;
numLength := 1;
--else if we re in a number, increase the length
ELSE
numLength := numLength + 1;
END IF;
--if this is the last character, we have to append the number
IF (i = stringLength) THEN
outString:= FcnConcatNumber(inString, outString, numBeginIndex, numLength, charTemplate, charTemplateLength);
END IF;
--else this is a character
ELSE
--if we were previously in a number, concat that and reset the numBeginIndex
IF (numBeginIndex != -1) THEN
outString:= FcnConcatNumber(inString, outString, numBeginIndex, numLength, charTemplate, charTemplateLength);
numBeginIndex := -1;
numLength := 0;
END IF;
--concat the character
outString := outString || thisChar;
END IF;
END LOOP;
RETURN outString;
--any exception, just return the original string
EXCEPTION WHEN OTHERS THEN
RETURN inString;
END;
这是一个提取字符串的示例查询。您应该能够在数据库的更新重构中使用它，或者将其直接用作查询。不过，我不确定它是否符合时间要求，这是需要注意并测试的问题。
SELECT SUBSTRING_INDEX("1.5.32", . ,1) AS MajorVersion,
SUBSTRING_INDEX(SUBSTRING_INDEX("1.5.32", . ,-2), . ,1) AS MinorVersion,
SUBSTRING_INDEX("1.5.32", . ,-1) AS Revision;
这将返回：
MajorVersion | MinorVersion | Revision
1 | 5 | 32
好的，如果高性能是一个问题，那么你唯一的选择是将你的价值观转化为数字。
但是，如果这是一个低使用率的查询，那么您只需拆分数字并按数字排序即可。
这个查询假设只有主版本和次版本号，且它们只包含数字。
SELECT
*
FROM
Requirements
WHERE
Requirements.Release NOT LIKE %Obsolete%
ORDER BY
CONVERT(int, RIGHT(REPLICATE( 0 , 10) + LEFT(Requirements.ReqNum, CHARINDEX( . , Requirements.ReqNum)-1), 10)),
CONVERT(int, SUBSTRING(Requirements.ReqNum, CHARINDEX( . , Requirements.ReqNum )+1, LEN(Requirements.ReqNum) - CHARINDEX( . , Requirements.ReqNum )))
对于所有只使用单个查询语句的纯粹主义者来说，假设使用Oracle数据库，一些instr/substr/decode/to_number奇技淫巧可以解决它。
SELECT *
FROM Requirements
WHERE Release NOT LIKE %Obsolete%
ORDER BY
to_number(
substr( reqnum, 1, instr( reqnum, . ) - 1 )
)
, to_number(
substr(
reqnum
, instr( reqnum, . ) + 1 -- start: after first occurance
, decode(
instr( reqnum, . , 1, 2 )
, 0, length( reqnum )
, instr( reqnum, . , 1, 2 ) - 1
) -- second occurance (or end)
- instr( reqnum, . , 1, 1) -- length: second occurance (or end) less first
)
)
, to_number(
decode(
instr( reqnum, . , 1, 2 )
, 0, null
, substr(
reqnum
, instr( reqnum, . , 1, 2 ) + 1 -- start: after second occurance
, decode(
instr( reqnum, . , 1, 3 )
, 0, length( reqnum )
, instr( reqnum, . , 1, 3 ) - 1
) -- third occurance (or end)
- instr( reqnum, . , 1, 2) -- length: third occurance (or end) less second
)
)
)
, to_number(
decode(
instr( reqnum, . , 1, 3 )
, 0, null
, substr(
reqnum
, instr( reqnum, . , 1, 3 ) + 1 -- start: after second occurance
, decode(
instr( reqnum, . , 1, 4 )
, 0, length( reqnum )
, instr( reqnum, . , 1, 4 ) - 1
) -- fourth occurance (or end)
- instr( reqnum, . , 1, 3) -- length: fourth occurance (or end) less third
)
)
)
;
我怀疑其中有很多注意事项，包括：
这是一个为PostgreSQL编写的比较函数，它可以比较任意字符串，使得数字序列按数字大小进行比较。换句话说，“ABC123” > “ABC2”，但是“AB123” < “ABC2”。它输出-1,0或+1，就像其他比较函数一样。
CREATE FUNCTION vercmp(a text, b text) RETURNS integer AS $$
DECLARE
ar text[];
br text[];
n integer := 1;
BEGIN
SELECT array_agg(y) INTO ar FROM (SELECT array_to_string(regexp_matches(a, E \d+|\D+|^$ , g ), ) y) x;
SELECT array_agg(y) INTO br FROM (SELECT array_to_string(regexp_matches(b, E \d+|\D+|^$ , g ), ) y) x;
WHILE n <= array_length(ar, 1) AND n <= array_length(br, 1) LOOP
IF ar[n] ~ E ^\d+$ AND br[n] ~ E ^\d+$ THEN
IF ar[n]::integer < br[n]::integer THEN
RETURN -1;
ELSIF ar[n]::integer > br[n]::integer THEN
RETURN 1;
END IF;
ELSE
IF ar[n] < br[n] THEN
RETURN -1;
ELSIF ar[n] > br[n] THEN
RETURN 1;
END IF;
END IF;
n := n + 1;
END LOOP;
IF n > array_length(ar, 1) AND n > array_length(br, 1) THEN
RETURN 0;
ELSIF n > array_length(ar, 1) THEN
RETURN 1;
ELSE
RETURN -1;
END IF;
END;
$$ IMMUTABLE LANGUAGE plpgsql;
然后你可以创建一个操作员类，以便可以使用比较函数和ORDER BY field USING <#来进行排序：
CREATE OR REPLACE FUNCTION vernum_lt(a text, b text) RETURNS boolean AS $$
BEGIN
RETURN vercmp(a, b) < 0;
END;
$$ IMMUTABLE LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION vernum_lte(a text, b text) RETURNS boolean AS $$
BEGIN
RETURN vercmp(a, b) <= 0;
END;
$$ IMMUTABLE LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION vernum_eq(a text, b text) RETURNS boolean AS $$
BEGIN
RETURN vercmp(a, b) = 0;
END;
$$ IMMUTABLE LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION vernum_gt(a text, b text) RETURNS boolean AS $$
BEGIN
RETURN vercmp(a, b) > 0;
END;
$$ IMMUTABLE LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION vernum_gte(a text, b text) RETURNS boolean AS $$
BEGIN
RETURN vercmp(a, b) >= 0;
END;
$$ IMMUTABLE LANGUAGE plpgsql;
CREATE OPERATOR <# ( PROCEDURE = vernum_lt, LEFTARG = text, RIGHTARG = text);
CREATE OPERATOR ># ( PROCEDURE = vernum_gt, LEFTARG = text, RIGHTARG = text);
CREATE OPERATOR =# ( PROCEDURE = vernum_lte, LEFTARG = text, RIGHTARG = text);
CREATE OPERATOR <=# ( PROCEDURE = vernum_lte, LEFTARG = text, RIGHTARG = text);
CREATE OPERATOR >=# ( PROCEDURE = vernum_gte, LEFTARG = text, RIGHTARG = text);
CREATE OPERATOR CLASS vernum_ops FOR TYPE varchar USING btree AS
OPERATOR 1 <# (text, text),
OPERATOR 2 <=# (text, text),
OPERATOR 3 =#(text, text),
OPERATOR 4 >=# (text, text),
OPERATOR 5 ># (text, text),
FUNCTION 1 vercmp(text, text)
;
修复了。
<pre>
00000001 1
00000001.00000001 1.1
00000001.00000001.00000001 1.1.1
00000001.00000002 1.2
00000001.00000009 1.9
00000001.00000010 1.10
00000001.00000011 1.11
00000001.00000012 1.12
00000002 2
00000002.00000001 2.1
00000002.00000001.00000001 2.1.1
00000002.00000002 2.2
00000002.00000009 2.9
00000002.00000010 2.10
00000002.00000011 2.11
00000002.00000012 2.12
select * from (select 000000001 as tCode, 1 as Code union
select 000000001.000000001 as tCode, 1.1 as Code union
select 000000001.000000001.000000001 as tCode, 1.1.1 as Code union
select 000000001.000000002 as tCode, 1.2 union
select 000000001.000000010 as tCode, 1.10 as Code union
select 000000001.000000011 as tCode, 1.11 as Code union
select 000000001.000000012 as tCode, 1.12 as Code union
select 000000001.000000009 as tCode, 1.9 as Code
union
select 00000002 as tCode, 2 as Code union
select 00000002.00000001 as tCode, 2.1 as Code union
select 00000002.00000001.00000001 as tCode, 2.1.1 as Code union
select 00000002.00000002 as tCode, 2.2 as Code union
select 00000002.00000010 as tCode, 2.10 as Code union
select 00000002.00000011 as tCode, 2.11 as Code union
select 00000002.00000012 as tCode, 2.12 as Code union
select 00000002.00000009 as tCode, 2.9 as Code ) as t
order by t.tCode
</pre>
<pre>
public static string GenerateToCodeOrder(this string code)
{
var splits = code.Split( . );
var codes = new List<string>();
foreach (var str in splits)
{
var newStr = "";
var zeroLength = 10 - str.Length;
for (int i = 1; i < zeroLength; i++)
{
newStr += "0";
}
newStr += str;
codes.Add(newStr);
}
return string.Join(".", codes);
}
</pre>
在M$ SQL中，我在某些数据的层次结构中遇到了问题...
select Convert(hierarchyid, / + 8.3.0000.1088 + / )
为了解决这个问题，我使用了pasename（依赖于'.'作为分隔符）...
Order by
convert(int, reverse (Parsename( reverse(tblSoftware.softwareVersion) , 1))),
convert(int, reverse (Parsename( reverse(tblSoftware.softwareVersion) , 2))),
convert(int, reverse (Parsename( reverse(tblSoftware.softwareVersion) , 3))),
convert(int, reverse (Parsename( reverse(tblSoftware.softwareVersion) , 4))),
convert(int, reverse (Parsename( reverse(tblSoftware.softwareVersion) , 5)))
If the column type for version is varchar the sorting is done as expected. This is beacuse varchar is not padded by spaces.
这是一个可以在 ORDER BY 中使用的 ORACLE 表达式：
select listagg(substr( 0000000000 || column_value,-9), . ) within group(order by rownum) from xmltable(replace(version, . , , ))
assuming your version column has only dot as separator (any number of levels).
(if not, up to you to change the replace by e.g. translate(version, .- , ,, ))