记录集的订单子集仅不是全部记录集 [英] order subset of record set only not all record set

问题描述

我有如下表

i have table as follows

INTCATEGORYID   NAME       PARENTID
1               ASD           1
2               EFD           1  
5               FGH           1 
6               JKL           2  
8               VBN           2 
9               FG            3
0               VB            3

我想获得像Follow
这样的结果

I want to have result like follow

INTCATEGORYID   NAME       PARENTID
5               FGH           1 
2               EFD           1  
1               ASD           1
6               JKL           2  
8               VBN           2 
9               FG            3
0               VB            3

表示我想按降序对整个记录集的子集进行短路
例如，具有parentID = 1的行必须按降序排列，只有其余部分应按升序排列.

means i want to short the subset of whole record set in descending order
like rows which having parentID =1 has to be short in descending order only rest should be in ascending order

推荐答案

create table sort_test
(INTCATEGORYID int,
 name varchar(10),
 parentid int)

 insert into sort_test values (1,'ASD',1)
  insert into sort_test values (2,'EFD',1)
 insert into sort_test values (5,'FGH',1)
 insert into sort_test values (6,'JKL',2)
 insert into sort_test values (8,'VBN',3)
 insert into sort_test values (9,'FG',3)
 insert into sort_test values (0,'VB',3)

Select INTCATEGORYID ,NAME ,PARENTID
from sort_test
order by 
  parentid,
 case when parentid = 1
   then INTCATEGORYID * -1
 else 
   INTCATEGORYID
   end

就像这样:

Like this:

SELECT 
    * 
FROM 
    MY_TABLE
ORDER BY
    PARENTID ASC, INTCATEGORYID DESC

阅读有关订购者 [

Read about ORDER BY[^] here.

尝试一下

Try this

select * from
(select top 100 percent * from CATEGORY where PARENTID = 1 order by PARENTID,CATEGORYID desc) t
union all
select * from CATEGORY where PARENTID != 1 order by PARENTID,CATEGORYID

这篇关于记录集的订单子集仅不是全部记录集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！