我的表结构是
cat_id cat_desc parent_category par_cat_order cdate status
1 Machine 2017-12-22 13:10:51 active
2 Woods 2017-12-22 13:11:01 active
3 Tools 2017-12-22 13:11:10 active
4 Perol 1 1 2017-12-22 13:11:30 active
5 Diesel 1 1 2017-12-22 13:11:43 active
6 Table 2 2 2017-12-22 13:11:59 active
7 Chair 2 2 2017-12-22 13:12:13 active
8 Round Table 6 2,6 2017-12-22 13:12:32 active
9 Dining 6 2,6 2017-12-22 13:13:01 active
10 Cuting Player 3 3 2017-12-22 13:13:15 active
11 Scrow Driver 3 3 2017-12-22 13:13:40 active
12 Sandal 9 2,6,9 2017-12-22 13:32:23 active
我想在两个类别中都显示由类别编号表示的父类别描述(“ parent_category”,“ par_cat_order”)
我的查询是
select a.cat_id,
a.cat_desc,
b.cat_desc,
group_concat(c.cat_desc)
from category a
left JOIN category b on (a.parent_category=b.cat_id)
left JOIN category c on find_in_set(c.cat_id,a.par_cat_order)
GROUP by a.cat_id
输出是
cat_id cat_desc cat_desc group_concat(c.cat_desc)
1 Machine
2 Woods
3 Tools
4 Perol Machine Machine
5 Diesel Machine Machine
6 Table Woods Woods
7 Chair Woods Woods
8 Round Table Table Table,Woods
9 Dining Table Table,Woods
10 Cuting Player Tools Tools
11 Scrow Driver Tools Tools
12 Sandal Dining Dining,Table,Woods
我需要的是“ group_concat(c.cat_desc)”以存储顺序显示字段。例如,我在第8条记录中以2,6的形式存储在“ par_cat_order”中。它将显示Woods,Table。但显示“桌子,树林”
你可以用order by
在group_concat
select a.cat_id,
a.cat_desc,
b.cat_desc,
group_concat(c.cat_desc order by c.cat_id asc)
from category a
left JOIN category b on (a.parent_category=b.cat_id)
left JOIN category c on find_in_set(c.cat_id,a.par_cat_order)
GROUP by a.cat_id
如果可以规范化表结构,那就更好了。数据库规范化
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句