按多列排序行选择 [英] Order row selection by multiple columns

问题描述

我有一个数据库

id    |     parentid     |       name
1     |        0         |      CatOne
2     |        0         |      CatTwo
3     |        0         |      CatThree
4     |        1         |      SubCatOne
5     |        1         |      SubCatOne2
6     |        3         |      SubCatThree

我如何选择这只猫?按id，parentid排序?那是

How I can select this cats Order By id, parentid? That is

CatOne 1
--SubCatOne 4
--SubCatOne2 5
CatTwo 2
CatThree 3
--SubCatThree 6

推荐答案

这应该做到...除了名称的双破折号-"前缀之外...

This should do it... with exception of a double dash "--" prefix to the name...

SELECT 
      t1.name,
      t1.id
   FROM 
      Table1 t1
   ORDER BY 
      case when t1.parentID = 0 then t1.ID else t1.ParentID end,
      case when t1.parentID = 0 then '1' else '2' end,
      t1.id

按首字母大写的顺序/当将所有属于最高层的项目放在第一层时，或根据第一层的ID放在第二层的时候.因此，如果您有超过1000个条目，尝试使用提供的父* 1000个示例hack就不会成为问题.然后，第二个案例/时间将在父ID为0时进入其分组列表的TOP及其下的所有子条目，但在下一个父ID之前.

The order by FIRST case/when puts all the items that ARE the top level, or at the secondary level by the primary level's ID. So trying to use a parent * 1000 sample hack offered won't be an issue if you have over 1000 entries. The SECOND case/when will then force when the parent ID = 0 to the TOP of its grouped list and all its subsidiary entries UNDER it, but before the next parent ID.

但是，如果您确实想要双破折号，请更改为

however, if you DO want the double dash, change to

SELECT 
      if( t1.ParentID = 0, '', '--' ) + t1.name name,
     <rest of query is the same>

这篇关于按多列排序行选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！