普遍数据库按语句排序

Question

我有以下SQL语句：

select * 
from
(select  
id,
left(id, 6) as Hauptnummer,
convert(substring(id, 8), SQL_BIGINT) as Detailnummer
from 
Proben 
where 
id like '%-%'  
and id like '2%' 
and length(id) > 7) as a 
order by  
Hauptnummer desc, Detailnummer

当我运行此语句时，出现以下错误：

！[ODBC 引擎接口] 表达式错误

在没有ORDER BY子句的情况下正确执行语句。

如何编写此ORDER BY子句？

Answer 1

您正在尝试按子查询之外的字段进行排序。 让我们尝试相同的代码(下面是MS SQL的语法(：

/* declare and create temp (in-memory) table */
declare @proben table
(
id nvarchar(24)
)
/* insert come test data to table */
insert @proben(id) values('2345-67890')
insert @proben(id) values('54654645-6257890')
insert @proben(id) values('2345-67890546541')
insert @proben(id) values('4355343542345-67890')
/* check of select of your script */
select *
from (
select
id,
left(id, 6) as Hauptnummer,
CAST(substring(id, 8, 0) AS BIGINT) as Detailnummer
from
@proben
where
id like '%-%'
and id like '2%'
and len(id) > 7
order by
Hauptnummer desc, Detailnummer
) as a
order by
a.Hauptnummer desc, a.Detailnummer

Answer 2

如果在 SQL-Server(T-SQL( 中使用的代码，则可以使用以下方法实现： 子字符串函数接受字符转换，因此代码已按以下方式重写。子字符串函数的语法：SUBSTRING(string, start, length)

例如：

select * 
from
(select  
id,
left(id, 6) as Hauptnummer, 
substring(convert (varchar(20), id ), 1, 2) as Detailnummer 
from 
Proben 
where 
id like '%-%'  
and id like '2%'  
and len(id) > 7) as a  /* SQL server it is len instead length */
order by  
Hauptnummer desc, Detailnummer