我有一个tbl_categories表,如下所示:
id category_name parent_id status category_order slug
我还有另一个表tbl_users,其中每个用户记录最多可以属于每个主项中的3个主要类别和3个子类别(因此,总共3个主要类别和9个子类别)
我知道我可以在tbl_users表上使用别名并创建我的查询,该查询将返回用户所属的类别和子类别。但是,我可以想到的tbl_users表的表结构如下所示:
id用户名密码maincat_1 subcat_1_1 subcat_1_2 subcat_1_3 maincat_2 subcat_2_1 subcat_2_2 subcat_2_3 maincat_3 maincat_3_1 maincat_3_2 maincat_3_3
有没有更好的结构/方式来做到这一点?
要查询属于用户的子类别:
SELECT t1.first_name, t1.last_name, m1.category_name AS maincategory_1, m2.category_name AS maincategory_2, m3.category_name AS maincategory_3
FROM tbl_user_individual AS t1
LEFT JOIN tbl_categories AS m1 ON t1.subcat_1 = m1.id
LEFT JOIN tbl_categories AS m2 ON t1.subcat_2 = m2.id
LEFT JOIN tbl_categories AS m3 ON t1.subcat_3 = m3.id
LEFT JOIN tbl_categories AS m4 ON t1.subcat_4 = m4.id
LEFT JOIN tbl_categories AS m5 ON t1.subcat_5 = m5.id
LEFT JOIN tbl_categories AS m6 ON t1.subcat_6 = m6.id
LEFT JOIN tbl_categories AS m7 ON t1.subcat_7 = m7.id
LEFT JOIN tbl_categories AS m8 ON t1.subcat_8 = m8.id
LEFT JOIN tbl_categories AS m9 ON t1.subcat_9 = m9.id
WHERE
m1.slug = 'english_1394435671' OR
m2.slug = 'english_1394435671' OR
m3.slug = 'english_1394435671' OR
m4.slug = 'english_1394435671' OR
m5.slug = 'english_1394435671' OR
m6.slug = 'english_1394435671' OR
m7.slug = 'english_1394435671' OR
m8.slug = 'english_1394435671' OR
m9.slug = 'english_1394435671'
要知道用户是否属于主要类别,查询将缩短,因为用户只能属于3个主要类别。希望这对某人有帮助!
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句