Geek Answers Handbook

Select row order across multiple columns

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 can I choose these cats Order id, parentid? it

CatOne 1
--SubCatOne 4
--SubCatOne2 5
CatTwo 2
CatThree 3
--SubCatThree 6
+5
source share
5 answers

This should be done ... with the exception of the 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

FIRST case/ , . , * 1000 , 1000 . SECOND case/when , ID = 0 TOP , .

, ,

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

, , :

select * from  cats
order by
      case when parentid = 0 then id else parentid end,
      case when parentid = 0 then 0 else id end

, id

+5

: ORDER BY parentid, id

, , - .

SQL, , . ...

: , .

: , , ( Cat SubCat), , .

+1

:

select id as parentId,
0 as sortOrder,
id,
name
from cats 
where parentId = 0
union all
select parentId,
1 as sortOrder,
id,
name
from cats 
where parentId > 0
order by parentId, sortOrder, name

:

ParentId sortOrder  id   Name
    1       0       1   CatOne
    1       1       4   SubCatOne
    1       1       5   SubCatOne2
    2       0       2   CatTwo
    3       0       3   CatThree
    3       1       6   SubCatThree
+1

. , , , ?

SELECT c.id,c.name,sc.id as subcatid,sc.name as subcatname
FROM cats c LEFT JOIN cats sc ON c.id=sc.parentid
WHERE c.parentid=0 order by c.id,sc.id;

:

+------+----------+----------+-------------+
| id   | name     | subcatid | subcatname  |
+------+----------+----------+-------------+
|    1 | CatOne   |        4 | SubCatOne   |
|    1 | CatOne   |        5 | SubCatOne2  |
|    2 | CatTwo   |     NULL | NULL        |
|    3 | CatThree |        6 | SubCatThree |
+------+----------+----------+-------------+
0

More articles:


All Articles