使用 SORT 選擇父子

我有一個簡單的一級父子關係表，包含以下列：

ID_Asset| Parent_ID_Asset | ProductTitle

我需要按父級後跟子級分組的輸出，並且還按父級和子級名稱排序。我在小提琴中的嘗試。父母必須是第一位的。

詳見此處：https ://rextester.com/PPCHG20007

CREATE TABLE [dbo].[Test](
   [ID_Asset] [int] NOT NULL,
   [Parent_ID_Asset] [int] NULL,
   [ProductTitle] [nvarchar](150) NOT NULL
) ON [PRIMARY]
GO

INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (2, NULL, N'Live Maps Unity')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (3, NULL, N'mShare')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (4, NULL, N'Nessus Professional')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (5, NULL, N'Enterprise Server')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (6, NULL, N'PhantomPDF')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (7, NULL, N'Sharegate')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (8, NULL, N'ADONIS Server')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (9, NULL, N'Automated Intelligence AI Compliance Extender & AI Syncpoint')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (10, NULL, N'Agility BridgeChecker')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (11, NULL, N'Office Timeline')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (12, NULL, N'ThinkBuzan iMindMap 8 Ultimate')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (13, NULL, N'Total Management Suite')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (14, NULL, N'Webex Business Messaging and Advanced Meetings')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (16, 8, N'ADONIS Designer')
INSERT [dbo].[Test] ([ID_Asset], [Parent_ID_Asset], [ProductTitle]) VALUES (20, 8, N'ADONIS Portal Module “Control & Release” Package XS')
GO

SELECT 
   *
FROM 
   Test
ORDER BY (
   CASE WHEN Parent_ID_Asset is null then ID_Asset else Parent_ID_Asset end), ProductTitle
GO

所需的輸出是（相關部分）：

ID_Asset   Parent_ID_Asset    ProductTitle
--------   ---------------    ---------------------------------------------------
  8            NULL          ADONIS Server
 16             8            ADONIS Designer
 20             8            ADONIS Portal Module “Control & Release” Package XS

父母在上，孩子按字母順序排列。父元素（帶有 null in Parent_ID_Asset）也必須按字母順序排列。

您可以通過在 ORDER BY 子句中使用 CASE WHEN 來獲取它：

注意：我添加COALESCE(Parent_ID_Asset, '')只是為了首先獲得 NULL Parent_ID，您可以將其替換為CASE WHEN Parent_ID_Asset IS NULL THEN 0 ELSE 1 END

SELECT 
   ID_Asset,
   Parent_ID_Asset,
   ProductTitle
FROM 
   Test
ORDER BY
   CASE WHEN Parent_ID_Asset IS NULL THEN ID_Asset ELSE Parent_ID_Asset END,
   COALESCE(Parent_ID_Asset, ''),
   ProductTitle;
GO

| ID_Asset | Parent_ID_Asset | ProductTitle                                                 |
|----------|-----------------|--------------------------------------------------------------|
| 2        | NULL            | Live Maps Unity                                              |
| 3        | NULL            | mShare                                                       |
| 4        | NULL            | Nessus Professional                                          |
| 5        | NULL            | Enterprise Server                                            |
| 6        | NULL            | PhantomPDF                                                   |
| 7        | NULL            | Sharegate                                                    |
| 8        | NULL            | ADONIS Server                                                |
| 16       | 8               | ADONIS Designer                                              |
| 20       | 8               | ADONIS Portal Module “Control & Release” Package XS          |
| 9        | NULL            | Automated Intelligence AI Compliance Extender & AI Syncpoint |
| 10       | NULL            | Agility BridgeChecker                                        |
| 11       | NULL            | Office Timeline                                              |
| 12       | NULL            | ThinkBuzan iMindMap 8 Ultimate                               |
| 13       | NULL            | Total Management Suite                                       |
| 14       | NULL            | Webex Business Messaging and Advanced Meetings               |

在這裡重新測試

更新

看來您需要 ProductTitle 以某種方式排序的結果，您可以使用此查詢：（請記住，您正在添加一個可能可以在表示層完成的額外工作。）

SELECT 
   ID_Asset,
   Parent_ID_Asset,
   ProductTitle
FROM 
   Test
ORDER BY
   MAX(CASE WHEN Parent_ID_Asset IS NULL THEN ProductTitle ELSE '' END) 
       OVER (PARTITION BY COALESCE(Parent_ID_Asset, ID_Asset)),
   COALESCE(Parent_ID_Asset, ID_Asset),
   Parent_ID_Asset,
   ProductTitle;


ID_Asset | Parent_ID_Asset | ProductTitle                                                
-------: | --------------: | :-----------------------------------------------------------
      8 |            null | ADONIS Server                                               
     16 |               8 | ADONIS Designer                                             
     20 |               8 | ADONIS Portal Module “Control & Release” Package XS     
     10 |            null | Agility BridgeChecker                                       
      9 |            null | Automated Intelligence AI Compliance Extender & AI Syncpoint
      5 |            null | Enterprise Server                                           
      2 |            null | Live Maps Unity                                             
      3 |            null | mShare                                                      
      4 |            null | Nessus Professional                                         
     11 |            null | Office Timeline                                             
      6 |            null | PhantomPDF                                                  
      7 |            null | Sharegate                                                   
     12 |            null | ThinkBuzan iMindMap 8 Ultimate                              
     13 |            null | Total Management Suite                                      
     14 |            null | Webex Business Messaging and Advanced Meetings              

db<>在這裡擺弄

引用自：https://dba.stackexchange.com/questions/223483