mySQL:按给定的ID字符串排序ID? (为分层数据构建面包屑)
I'm building a website with PHP & mySQL. In my application, the categories of products are stored as hierarchy in DB. I followed the approach of "Materialized path" (aka "Flat table Model") & created a table like this :
我正在用PHP和mySQL构建一个网站。在我的应用程序中,产品类别作为层次结构存储在DB中。我遵循“物化路径”(又名“平台模型”)的方法并创建了一个这样的表:
Suppose I want to build a breadcrumb for items in category FLASH (category_id=8) :
假设我想为FLASH类别(category_id = 8)中的项目构建一个痕迹:
ELECTRONICS -> PORTABLE ELECTRONICS -> MP3 PLAYERS -> FLASH
电子 - >便携式电子设备 - > MP3播放器 - >闪存
I wonder if I could get the line of ancestry from 'FLASH' IN CORRECT ORDER by this query:
我想知道我是否可以通过此查询从'FLASH'在正确的顺序中得到祖先的行:
Select * from category where category_ID in (1,6,7,8) ORDER BY '1,6,7,8'
What can be the correct replacement for :
什么可以是正确的替代品:
ORDER BY '1,6,7,8'
? Thanks for all suggestions !
?谢谢你的所有建议!
3 个解决方案
#1
1
I found the answer my own !
我找到了自己的答案!
SELECT *
FROM category
WHERE category_ID
IN ( 1, 6, 7, 8 )
ORDER BY category_ID =1 DESC , category_ID =8 DESC , category_ID =7 DESC , category_ID =6 DESC
The SQL string above can help me to sort ID by any predefined order : 1-6-7-8 or 1-8-7-6 or 6-1-8-7 or any thing else !
上面的SQL字符串可以帮助我按任何预定义的顺序对ID进行排序:1-6-7-8或1-8-7-6或6-1-8-7或其他任何东西!
更多相关文章
- 数据库_MySQL_复杂SQL的书写顺序与执行过程
- MySql SELECT 语句执行顺序
- 如果条件按顺序,Mysql不能使用mysql
- MySQL查询时有时候需要某条记录置顶或者放最后,而其他的记录则按
- javascript数组和对象是否有设置顺序?
- Gulp.js事件流合并顺序
- 正则表达式以任何顺序匹配多个模式
- 使用特定顺序的ID列表从Django数据库中获取记录
- Python多个装饰器的顺序