对SQL记录进行排序

Question

我需要按字母顺序排列我的SQL存储过程记录集（按照CustomerName），但也要保持RecordID的顺序（意思是RecordID 2在RecordID 3之上，相对于ParentID & ID字段） 。有什么建议么？

ID   ParentID   RecordID  CustomerName 
53445347  5344  2     Witt - 47 
534453475345 53445347 3     Witt - 45 
534453475346 53445347 3     Witt - 46 
53445351  5344  2     Sjob - 51 
534453515348 53445351 3     Sjob - 48 
534453515349 53445351 3     Sjob - 49 
534453515350 53445351 3     Sjob - 50 
53445357  5344  2     Hal - 57 
534453575355 53445357 3     Hal - 55 
534453575356 53445357 3     Hal - 56 
534453577252 53445357 3     Hal - 52 
53446852  5344  2     Boston, - 52 
534468526850 53446852 3     Boston, - 50 
534468526851 53446852 3     Boston, - 51 
534468527205 53446852 3     Boston, - 05 
53447884  5344  2     Agri - 84 
534478847883 53447884 3     Agri - 83 
534478847957 53447884 3     Agri - 57 

预期的输出会是这样：

53447884  5344  2     Agri - 84 
534478847883 53447884 3     Agri - 83 
534478847957 53447884 3     Agri - 57 
53446852  5344  2     Boston, - 52 
534468526850 53446852 3     Boston, - 50 
534468526851 53446852 3     Boston, - 51 
534468527205 53446852 3     Boston, - 05 
53445357  5344  2     Hal - 57 
534453575355 53445357 3     Hal - 55 
534453575356 53445357 3     Hal - 56 
534453577252 53445357 3     Hal - 52 
53445351  5344  2     Sjob - 51 
534453515348 53445351 3     Sjob - 48 
534453515349 53445351 3     Sjob - 49 
534453515350 53445351 3     Sjob - 50 
53445347  5344  2     Witt - 47 
534453475345 53445347 3     Witt - 45 
534453475346 53445347 3     Witt - 46 

Answer 1

如果所有客户名称为您的样品中形成：

ORDER BY SUBSTRING(CustomerName, 0, CHARINDEX('-', CustomerName)), RecordID 

顺便说一句，如果您有任何机会改变你的模型，将CustomerName的alpha和数字部分分成两个字段，你的生活会更好，世界将会是一个整体。

如果能有客户名称，而不-

而是尝试

ORDER BY CASE WHEN CHARINDEX('-', CustomerName) = 0 
       THEN CustomerName 
       ELSE SUBSTRING(CustomerName, 0, CHARINDEX('-', CustomerName)) 
      END, 
      RecordID