dongtangu6144
dongtangu6144
2012-10-18 18:10

带有多个INNER JOIN的MySQL ORDER BY

已采纳

I can't seem to figure out how to order by in this MySQL select. I hope you can help me out.

Tabels:

categories

catId, catParentId, catDisplay
1      0            1
2      0            1
3      0            1
4      1            1
5      1            1

categories_translation

transId, catId, catName, catDesc, langId
1        1      Title1   Desc1    1
2        2      Title2   Desc2    1
3        3      Title3   Desc3    1
4        4      Title4   Desc4    1
5        5      Title5   Desc5    1

language

langId, langName, langCode
1       Danish    da
2       English   en

My query:

SELECT `categories`.`catId`,
       `categories`.`catParentId`,
       `categories`.`catDisplay`,
       `categories_translation`.`catName`,
       `categories_translation`.`catDesc`,
       `language`.`langCode`
FROM   `categories`
INNER JOIN `categories_translation` ON `categories_translation`.`catId` = `categories`.`catId`
INNER JOIN `language` ON `language`.`langId` = `categories_translation`.`langId`
WHERE `language`.`langCode` = 'da'

Now, I get returned what I want, but is there a way to order the child categories to their parents, so the result looks like this:

Desired result:

catId | catParentId | catDisplay | catName | catDesc | langCode
1       0             1            Title1    Desc1     da
4       1             1            Title4    Desc4     da
5       1             1            Title5    Desc5     da
2       0             1            Title2    Desc2     da
3       0             1            Title3    Desc3     da

I've tried order by, but can seem to get the results like I want.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

3条回答

  • douzi9744 douzi9744 9年前

    Try the following:

    ORDER BY
        CASE WHEN categories.catParentId = 0 THEN
            categories.catId
        ELSE 
            categories.catParentId
        END,
        CASE WHEN categories.catParentId = 0 THEN
            0
        ELSE
            categories.catId
        END
    

    For those that don't get the ordering it would be easier to think of the desired result as:

    catId | catParentId | orderingCategory
    1       0             1.0
    4       1             1.4
    5       1             1.5
    2       0             2.0
    3       0             3.0
    

    So it's a hierarchy of categories, OP wants to order the result so that each parent categories are followed by their child categories.

    点赞 评论 复制链接分享
  • doufei8250 doufei8250 9年前

    You can't get the results you like, because its not possible. I can't see a pattern or an id which allow to order the way you want.

    Why title 4, title 5 go first?

    I can't see the logic. Maybe the example is incomplete, maybe the order of CatName and CatDesc is the key you need.

    点赞 评论 复制链接分享
  • dtfbj24048 dtfbj24048 9年前
    ORDER BY catParentID, catID, ...
    
    点赞 评论 复制链接分享

相关推荐