logo标签列表

指定多个排序规则

sql-servert-sqlsql-server-2008-r2
4
我正在使用SQL Server 2008 R2，我有一个复杂的排序问题，但是我找不到解决方案。
为了更好地解释，我在下面发布了一个样例查询。在这个查询中，我们试图显示位置层次结构，但是虽然父/子关系被正确排序，但它们在它们的关系内部不是按字母顺序排序的。正如您所看到的，"东海岸"和"西海岸"都是顶级位置，因为它们的父位置（f_locationparent）等于（0）。然而，我希望"东海岸"在"西海岸"之前显示。显然，我不能简单地按f_locationname，然后按f_lineage排序，因为这样关系就不能按正确的顺序显示。重要提示：顶级位置的父位置始终为（0），因为它们没有父项。
f_locationid    f_locationparent    f_locationname  f_level f_lineage
-------------------------------------------------------------------------
4               0                   West Coast      0       0_4
5               4                   Los Angeles     1       0_4_5
6               5                   Del Rey         2       0_4_5_6
7               5                   Reseda          2       0_4_5_7
8               5                   Crenshaw        2       0_4_5_8
9               0                   East Coast      0       0_9
10              9                   New York City   1       0_9_10
1               10                  Queens          2       0_9_10_1
2               10                  Bronx           2       0_9_10_2
3               10                  Manhattan       2       0_9_10_3

以下是当前的查询：
;WITH   cte_locationlineage AS 
 (
 SELECT  a.f_locationid, a.f_locationparent, a.f_locationname, 0 AS f_level,
   CONVERT(varchar(30), '0_' + convert(varchar(10), f_locationid)) f_lineage
 FROM    tb__templocations a 
 WHERE   f_locationparent = '0' 
 UNION ALL
    SELECT  a.f_locationid,
           a.f_locationparent,
           a.f_locationname,
           c.f_level + 1,
           CONVERT(varchar(30), f_lineage + '_' 
                   + convert(varchar(10), a.f_locationid))
   FROM    cte_locationlineage c
   JOIN    tb__templocations a
       ON a.f_locationparent = c.f_locationID
 )
 SELECT *
 FROM   cte_locationlineage c
 ORDER BY f_lineage

正如您所看到的，它是基于血统排序的，这是位置ID（f_locationID）的组合。不幸的是，正如您所看到的，位置ID并不总是按字母顺序排列。

这里是一个SQL Fiddle，您可以看到它是如何工作的。

最终，使用相同的数据，这是我想要看到的结果查询，在父级下的关系中，项目按字母顺序排序。因此，对于“东海岸”祖父和“纽约市”父母，列在其下面的子项是按字母顺序排列的。

f_locationid    f_locationparent    f_locationname  f_level f_lineage
-------------------------------------------------------------------------
9               0                   East Coast      0       0_9
10              9                   New York City   1       0_9_10
2               10                  Bronx           2       0_9_10_2
3               10                  Manhattan       2       0_9_10_3    
1               10                  Queens          2       0_9_10_1
4               0                   West Coast      0       0_4
5               4                   Los Angeles     1       0_4_5
8               5                   Crenshaw        2       0_4_5_8  
6               5                   Del Rey         2       0_4_5_6 
7               5                   Reseda          2       0_4_5_7
1
如果您按照完整路径对项目进行排序，您可以轻松获得所需的顺序。请查看我的第二个答案。 - CrimsonKing
3个回答
10
您可以使用 ROW_NUMBER() 来帮助：
;WITH   cte_locationlineage AS 
 (
 SELECT  a.f_locationid, a.f_locationparent, a.f_locationname, 0 AS f_level,
       CONVERT(varchar(30), '0_' + convert(varchar(10), f_locationid)) f_lineage,
       CAST(ROW_NUMBER() OVER(ORDER BY f_locationname) as decimal(8,4)) as ordering
 FROM    tb__templocations a 
 WHERE   f_locationparent = '0' 
 UNION ALL
    SELECT  a.f_locationid,
         a.f_locationparent,
         a.f_locationname,
         c.f_level + 1,
         CONVERT(varchar(30), f_lineage + '_' + convert(varchar(10), a.f_locationid)),
         cast(c.ordering + (CAST(ROW_NUMBER() OVER(ORDER BY a.f_locationname) 
           as decimal(8,4))/POWER(10,c.f_level + 1)) as decimal(8,4))
   FROM    cte_locationlineage c
   JOIN    tb__templocations a
       ON a.f_locationparent = c.f_locationID
 )
 SELECT *
 FROM   cte_locationlineage c
 ORDER BY c.ordering

这种方式是将你的级别和位置名称结合起来，以便在列表中排序。

值得注意的是，如果您的表非常大，这可能不太实际。当你遇到越来越大的数据集时，ROW_NUMBER() 的速度会变得相当慢。

编辑：一个问题是如果您在一个级别中有超过九行，那么上面的示例就会出现问题。您必须增加数量级以反映足够的“空格”来容纳信息。例如，这适用于每个级别最多有99行的情况：

;WITH   cte_locationlineage AS 
 (
 SELECT  a.f_locationid, a.f_locationparent, a.f_locationname, 0 AS f_level,
         CONVERT(varchar(30), '0_' + convert(varchar(10), f_locationid)) f_lineage,
         CAST(ROW_NUMBER() OVER(ORDER BY f_locationname) as decimal(12,8)) as ordering
 FROM    tb__templocations a 
 WHERE   f_locationparent = '0' 
 UNION ALL
    SELECT  a.f_locationid,
           a.f_locationparent,
           a.f_locationname,
           c.f_level + 1,
           CONVERT(varchar(30), f_lineage + '_' + convert(varchar(10), a.f_locationid)),
           cast(c.ordering + (CAST(ROW_NUMBER() OVER(ORDER BY a.f_locationname) as decimal(12,8))
                    /POWER(10,(c.f_level + 1)*2)) as decimal(12,8))
   FROM    cte_locationlineage c
   JOIN    tb__templocations a
       ON a.f_locationparent = c.f_locationID
 )
 SELECT *
 FROM   cte_locationlineage c
 ORDER BY c.ordering
显然，如果每个级别超过999行，这将变得繁琐，但鉴于您的评论，我认为这不应该成为问题。
我很好奇是否有人有更聪明的方法来使用二进制完成相同的事情；我打算今晚稍后看看数学公式。
2
是的，这很简单、优雅，即使加入 Miami 也能很好地完成工作。而且，如果表非常大，递归 CTE 方法已经限制了实用性。 - Aaron Bertrand
这太好了，谢谢。我从未想过解决方案可以适合已经使用的查询。我确信答案涉及抹掉我所拥有的并重新开始，所以真心感谢你。最多，我的表格中可能有1000行。目前，我大约有600行，并且这个程序运行立即完成(实际上，它只需要0.0002秒（200微秒）)。 - Beems
我发现了一个问题。似乎如果我们到达一个有10个或更多子项的父节点，那么我们最终会得到一个顺序为5.8，5.9，6.0... | 当我们到达6.0时，可能会出现多个项目，其中顺序是6.0，这样就导致了顺序的失衡。 - Beems
请更新答案，以便未来搜索此主题的用户可以得到相同的答案。非常感谢您的帮助，真诚地。 - Beems
1
不错。这是一种不使用幂，只使用字符串的变体。适用于多达33个级别和最多具有相同父项的99个项目：**sql-fiddle**。可以通过更改（例如）“'0'”到“'000'”，将varchar(2)更改为varchar(4)来轻松扩展，以便每个父项最多有9999个项目。varchar(99)必须变为 varchar((4+1)*X) ，其中X是层次结构的最大级别。 - ypercubeᵀᴹ
显示剩余2条评论
2
您可以按照完整路径名对行进行排序，使用一个未使用的字符来连接名称。
WITH   cte_locationlineage AS (
    SELECT  a.F_LocationId, a.f_locationparent, a.f_locationname, 
            0 AS f_level,
            CONVERT(varchar(30), '0_' + convert(varchar(10), F_LocationId)) as f_lineage,
            CONVERT(varchar(max), a.f_locationname) as Fullname -- Add this line
    FROM    tb__templocations a 
    WHERE   f_locationparent = '0' 
    UNION ALL
    SELECT  a.F_LocationId,
            a.f_locationparent,
            a.f_locationname,
            c.f_level + 1,
            CONVERT(varchar(30), f_lineage + '_' 
                   + convert(varchar(10), a.F_LocationId)),
            CONVERT(varchar(max), c.fullname + '_' + a.f_locationname) -- Add this line
   FROM     cte_locationlineage c
   JOIN     tb__templocations a ON a.f_locationparent = c.F_LocationId)

SELECT *
FROM   cte_locationlineage c
ORDER BY fullname -- Change this line

SQL Fiddle

1
这是一个有趣的解决方案。使用连接的“fullname”字段，您实际上可以在代码后端将其拆分并用于显示向上导航的“面包屑”。 - Beems
-7

按照首先顶级父名称，然后是谱系排序

ORDER BY (select f_locationname from cte_locationlineage d 
  where d.F_Lineage = substring(c.F_lineage,1,3)), f_lineage

更新

为了处理多位数的ID：

ORDER BY (select f_locationname from cte_locationlineage d 
  where d.f_level = 0 and c.F_LiNEAGE + '_' like d.f_lineage + '[_]%'), f_lineage
不，这样行不通，首先它假设所有的location_id值都是单个数字（你不能像那样硬编码子字符串），并且它明显无法返回正确的结果。不要考虑基于相关子查询排序的额外性能影响... - Aaron Bertrand
哦，这么多不喜欢！！ :) 好吧，那只是一个快速的建议，你明白了。比较可以稍微调整一下，以处理多位数字的 ID。 - CrimsonKing
3
不是喜欢或不喜欢的问题。您更新后的解决方案也行不通 - 您尝试过这两个查询中的任何一个吗？您仍然按lineage排序 - 原帖作者想要按名称在同一级别上按字母顺序排序，而不是按lineage排序。 - Aaron Bertrand
当然，我试过它们了。只是我认为字母顺序仅适用于顶级项目。错过了同一级别的子节点也会被排序的部分。 - CrimsonKing
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接