按多列排序行选择

I have a database

我有一个数据库

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

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

我怎样才能选择这只猫订购ID，parentid？那是

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

5 个解决方案

#1

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

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.

FIRST case / when的顺序将所有项目置于顶级，或者将次级别的项目放入主要级别的ID。因此，如果您有超过1000个条目，尝试使用提供的父* 1000样本黑客将不会成为问题。 SECOND case / when将在父ID = 0时强制执行其分组列表的TOP及其所有子条目UNDER，但在下一个父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>

5 个解决方案

#1

更多相关文章

随机推荐