普适数据库order by语句

biswetbf  于 2021-07-26  发布在  Java
关注(0)|答案(2)|浏览(292)

我有以下sql语句:

  1. select *
  2. from
  3. (select
  4. id,
  5. left(id, 6) as Hauptnummer,
  6. convert(substring(id, 8), SQL_BIGINT) as Detailnummer
  7. from
  8. Proben
  9. where
  10. id like '%-%'
  11. and id like '2%'
  12. and length(id) > 7) as a
  13. order by
  14. Hauptnummer desc, Detailnummer

运行此语句时出现以下错误:
![odbc引擎接口]表达式错误
语句在没有 ORDER BY 条款。
我怎么写这个 ORDER BY 条款?

sr4lhrrt

sr4lhrrt1#

您正在尝试按子查询之外的字段排序。让我们试试同样的代码(下面是ms sql的语法):

  1. /* declare and create temp (in-memory) table */
  2. declare @proben table
  3. (
  4. id nvarchar(24)
  5. )
  6. /* insert come test data to table */
  7. insert @proben(id) values('2345-67890')
  8. insert @proben(id) values('54654645-6257890')
  9. insert @proben(id) values('2345-67890546541')
  10. insert @proben(id) values('4355343542345-67890')
  11. /* check of select of your script */
  12. select *
  13. from (
  14. select
  15. id,
  16. left(id, 6) as Hauptnummer,
  17. CAST(substring(id, 8, 0) AS BIGINT) as Detailnummer
  18. from
  19. @proben
  20. where
  21. id like '%-%'
  22. and id like '2%'
  23. and len(id) > 7
  24. order by
  25. Hauptnummer desc, Detailnummer
  26. ) as a
  27. order by
  28. a.Hauptnummer desc, a.Detailnummer
展开查看全部
brqmpdu1

brqmpdu12#

如果在sqlserver(t-sql)中使用的代码可以通过以下方法实现:substring函数接受字符转换,因此代码已按以下方式重新编写。子字符串函数的语法: SUBSTRING(string, start, length) 例如:

  1. select *
  2. from
  3. (select
  4. id,
  5. left(id, 6) as Hauptnummer,
  6. substring(convert (varchar(20), id ), 1, 2) as Detailnummer
  7. from
  8. Proben
  9. where
  10. id like '%-%'
  11. and id like '2%'
  12. and len(id) > 7) as a /* SQL server it is len instead length */
  13. order by
  14. Hauptnummer desc, Detailnummer

相关问题