代码之家 › 专栏 › 技术社区 › Echilon Mafarnakus

SQL-按多个条件排序

tsql sql-server-2005 sql

Echilon Mafarnakus · 技术社区 · 16 年前

我有一张分类表。每个类别可以是根级别类别(父级为空),也可以具有根级别类别的父级。不能有多个层次的嵌套。

我有以下表格结构:

Categories Table Structure http://img16.imageshack.us/img16/8569/categoriesi.png

是否可以使用生成以下输出的查询:

Free Stuff
Hardware
Movies
CatA
CatB
CatC
Software
Apples
CatD
CatE

所以结果是按顶级类别排序的,那么在每个顶级类别之后,该类别的子类别都会被列出?

它不是按父级或名称排序,而是两个元素的组合。我正在使用SQL Server。

6 回复 | 直到 16 年前

Cyril Gandon niktrs 16 年前

好的,我们开始:

with foo as
(
select 1 as id, null as parent, 'CatA' as cat from dual
union select 2, null, 'CatB' from dual
union select 3, null, 'CatC' from dual
union select 4, 1, 'SubCatA_1' from dual
union select 5, 1, 'SubCatA_2' from dual
union select 6, 2, 'SubCatB_1' from dual
union select 7, 2, 'SubCatB_2' from dual
)
select child.cat
from foo parent right outer join foo child on parent.id = child.parent
order by case when parent.id is not null then parent.cat else child.cat end,
         case when parent.id is not null then 1 else 0 end

结果:

CatA
SubCatA_1
SubCatA_2
CatB
SubCatB_1
SubCatB_2
CatC

编辑-解决方案更改灵感来源于van's order by!这样更简单。

Sam Saffron James Allen 16 年前

在我看来,您似乎希望将层次结构扁平化并排序,获得此排序的最便宜方法是在具有完整路径的表中存储一个额外的列。

例如:

Name            | Full Path
Free Stuff      | Free Stuff 
aa2             | Free Stuff - aa2

一旦存储了完整的路径,就可以对其进行订购。

如果深度只有一个,则可以使用单个子查询(并对其排序)自动生成达到此效果的字符串,但当深度变深时,此解决方案无法轻松工作。

另一种选择是,将这些内容全部移动到临时表中,并根据需要计算其中的完整路径。但它相当昂贵。

Iain Hoult 16 年前

您可以让表自己看一看,按父名称排序,然后按子名称排序。

select   categories.Name AS DisplayName
from     categories LEFT OUTER JOIN
         categories AS parentTable ON categories.Parent = parentTable.ID
order by parentTable.Name, DisplayName

super9 16 年前

不完全确定你的问题,但听起来按分区可能对你有用。有一篇很好的分区介绍文章 here .

Ronald Wildenberg 16 年前

这里有一个使用复活公共表表达式的完整工作示例。

DECLARE @categories TABLE
(
    ID INT NOT NULL,
    [Name] VARCHAR(50),
    Parent INT NULL
);

INSERT INTO @categories VALUES (4,  'Free Stuff', NULL);
INSERT INTO @categories VALUES (1,  'Hardware', NULL);
INSERT INTO @categories VALUES (3,  'Movies', NULL);
INSERT INTO @categories VALUES (2,  'Software', NULL);
INSERT INTO @categories VALUES (10, 'a', 0);
INSERT INTO @categories VALUES (12, 'apples', 2);
INSERT INTO @categories VALUES (8,  'catD', 2);
INSERT INTO @categories VALUES (9,  'catE', 2);
INSERT INTO @categories VALUES (5,  'catA', 3);
INSERT INTO @categories VALUES (6,  'catB', 3);
INSERT INTO @categories VALUES (7,  'catC', 3);
INSERT INTO @categories VALUES (11, 'aa2', 4);

WITH categories(ID, Name, Parent, HierarchicalName)
AS
(
    SELECT
        c.ID
        , c.[Name]
        , c.Parent
        , CAST(c.[Name] AS VARCHAR(200)) AS HierarchicalName
    FROM @categories c
    WHERE c.Parent IS NULL

    UNION ALL

    SELECT
        c.ID
        , c.[Name]
        , c.Parent
        , CAST(pc.HierarchicalName + c.[Name] AS VARCHAR(200))
    FROM @categories c
    JOIN categories pc ON c.Parent = pc.ID
)
SELECT c.*
FROM categories c
ORDER BY c.HierarchicalName

Tomalak 16 年前

SELECT
  ID,
  Name,
  Parent,
  RIGHT(
    '000000000000000' + 
    CASE WHEN Parent IS NULL 
    THEN CONVERT(VARCHAR, Id) 
    ELSE CONVERT(VARCHAR, Parent) 
    END, 15
  )
  + '_' + CASE WHEN Parent IS NULL THEN '0' ELSE '1' END
  + '_' + Name
FROM
  categories
ORDER BY
  4

长填充是为了说明SQL Server的int数据类型从2147483648到2147483647。

你可以 ORDER BY 表达式直接,无需使用 ORDER BY 4 . 它只是为了显示它正在排序什么。

值得注意的是,此表达式不能使用任何索引。这意味着对大表的排序将很慢。