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

I have a table with the following type:

我有一个类型如下的表：

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

I wanted to select code and name from the above table such that it is ordered in the following way:

我想从上表中选择代码和名称，以便按以下方式排序：

based on the market where market id=3
基于市场id = 3的市场
Parent id
家长ID
related child
相关的孩子
others
其他

ie. id 1 (Parent_id) should be displayed first followed by id 2 and 3 (Child id). The values in 'parent_id' are from the column 'id'.

即。应首先显示id 1（Parent_id），然后显示id 2和3（Child id）。 'parent_id'中的值来自列'id'。

I have built the following query so far and i am feeling little difficult to order the parent code and the related child codes.

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

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

Can someone please help me out.

有人可以帮助我。

3 个解决方案

#1

A recursive CTE is the best way to construct a parent/child heirarchy as follows:

递归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

As per your request I have refactored the query as a VIEW.

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

To use the view:

要使用视图：

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

It gives the following result:

它给出了以下结果：

    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

To learn more about recursive CTEs click here

要了解有关递归CTE的更多信息，请单击此处

And, as requested, is a new version of the view that does not use a recursive CTE

并且，根据要求，是不使用递归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

Used as follows:

使用如下：

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

nb: The first version, using a recursive CTE, could handle virtually unlimited levels of Parent/Child while version 2 only handles one level.

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

#2

Try this

尝试这个

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

#3

You can put condition in your columns. Try:

您可以在条目中添加条件。尝试：

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

#1