如何根据sql server中的列值对结果排序

Question

我有一个表，它的类型如下:

Id  Parent_id  Code   Name       market
1    NULL      1ex    name 1       3
2    1         2ex    name 2       3
3    1         3ex    name 3       3
4    Null      4ex    name 4       1
5    null      5ex    name 5       3
6    4         6ex    name 6       3

我想从上面的表中选择code和name，使其按以下方式排序:

1. 基于市场id=3的市场
2. 父级id
3. 相关子
别人

ie。应该先显示id 1 (Parent_id)，然后是id 2和3 (Child id)。'parent_id'中的值来自'id'列。

到目前为止，我已经建立了以下查询，我觉得订购父代码和相关的子代码有点困难。

select code,name from tbl_codes A
order by  CASE WHEN(A.[Market] = 3) THEN 0 ELSE 1 END

有谁能帮我一下吗

Answer 1

试试这个

SELECT  code ,
        name 
FROM    tbl_codes A
ORDER BY CASE WHEN ( A.[Market] = 3 ) THEN 0
             ELSE 1
        END  ,
        CASE WHEN ( ISNULL(parent_id,0) = 1 ) THEN 0
             ELSE 1
        END 

Answer 2

递归CTE是构造父/子层次结构的最佳方法，如下所示:

-- Set up test data
CREATE TABLE tbl_codes (id INT , Parent_id INT, Code VARCHAR(3), NAME VARCHAR(12), Market INT)
INSERT tbl_codes 
SELECT 1, NULL, '1ex', 'name 1', 3 UNION ALL
SELECT 2, 1    , '2ex', 'name 2', 3 UNION ALL
SELECT 3, 1    , '3ex', 'name 3', 3 UNION ALL
SELECT 4, NULL , '4ex', 'name 4', 1 UNION ALL
SELECT 5, NULL , '5ex', 'name 5', 3 UNION ALL
SELECT 6, 4    , '6ex', 'name 6', 3
CREATE VIEW [dbo].[View_ParentChild]
AS
-- Use a recursive CTE to build a parent/child heirarchy
WITH  
  RecursiveCTE AS 
  (
     SELECT
       id,
       name,
       parent_id,
       Code,
       market,
       sort = id
     FROM
      tbl_codes
     WHERE
      parent_id IS NULL
     UNION ALL
     SELECT
       tbl_codes.id,
       tbl_codes.name,
       tbl_codes.parent_id,
       tbl_codes.Code,
       tbl_codes.market,
       sort = tbl_codes.parent_id
     FROM
      tbl_codes
      INNER JOIN RecursiveCTE
        ON tbl_codes.parent_id = RecursiveCTE.id
     WHERE
      tbl_codes.parent_id IS NOT NULL
  )
  SELECT
    Code,
    NAME,
    Market,
    Sort
  FROM
    RecursiveCTE
GO

根据您的要求，我已将查询重构为VIEW。

使用视图:

SELECT
  *
FROM
  dbo.View_ParentChild AS vpc
ORDER BY
  CASE WHEN ( Market = 3 ) THEN 0
       ELSE 1
  END,
  sort 

它给出如下结果:

    Code    NAME    Market  Sort
    ----    ------  ------  ----
    1ex     name 1      3     1
    2ex     name 2      3     1
    3ex     name 3      3     1
    6ex     name 6      3     4
    5ex     name 5      3     5
    4ex     name 4      1     4

要了解更多关于递归cte的信息，请点击这里

And是不使用递归CTE的视图的新版本

CREATE VIEW [dbo].[View_ParentChild_v2]
AS
SELECT
  id,
  Code,
  market,
  sort
FROM
(
  SELECT
    id,
    name,
    parent_id,
    Code,
    market,
    sort = id
  FROM
    tbl_codes
  WHERE
    parent_id IS NULL
  UNION ALL
  SELECT
    tbl_codes.id,
    tbl_codes.name,
    tbl_codes.parent_id,
    tbl_codes.Code,
    tbl_codes.market,
    sort = tbl_codes.parent_id
  FROM
    tbl_codes
  WHERE
    tbl_codes.parent_id IS NOT NULL
) AS T
GO

用法如下:

SELECT
  *
FROM
  View_ParentChild_v2
ORDER BY
  CASE WHEN ( Market = 3 ) THEN 0
       ELSE 1
  END,
  sort 

注意:第一个版本，使用递归CTE，可以处理几乎无限的父/子级别，而版本2只能处理一个级别。

Answer 3

您可以在列中添加条件。试一试:

SELECT  code ,
        name ,
        CASE WHEN ( A.[Market] = 3 ) THEN 0
             ELSE 1
        END AS marketOrder ,
        CASE WHEN ( parent_id = 1 ) THEN 0
             ELSE 1
        END AS parentOrder
FROM    tbl_codes A
ORDER BY parentOrder ,
        marketOrder