php - 根据最佳匹配对MySQL查询结果进行排序(匹配的列数)

Question

如何使用多个表根据匹配的列数对 MySQL 查询结果进行排序..

select tbl_user.*, tbl_user_personal.*, tbl_user_physical.*, tbl_user_education.*, tbl_user_lifestyle.* 
        from
        tbl_user, tbl_user_personal, tbl_user_physical, tbl_user_education, tbl_user_lifestyle 
        where
        tbl_user.user_id = tbl_user_personal.user_id and 
        tbl_user.user_id = tbl_user_physical.user_id and 
        tbl_user.user_id = tbl_user_education.user_id and 
        tbl_user.user_id = tbl_user_lifestyle.user_id and 
        (
            tbl_user_personal.user_community = 'some-text..' or
            tbl_user_personal.user_sub_caste = 'some-text..' or
            tbl_user_personal.user_marital_status = 'some-text..' or
            tbl_user_personal.user_children = 'some-text..' or
            tbl_user.user_age in ('some-text..', 'some-text..') or
            tbl_user.user_country = 'some-text..' or
            tbl_user_physical.user_height in('some-text..', 'some-text..') or
            tbl_user_physical.user_physical_status = 'some-text..' or
            tbl_user_education.user_education_category = 'some-text..' or
            tbl_user_education.user_occupation = 'some-text..' or
            tbl_user_lifestyle.user_eating_habits = 'some-text..'
        )

Answer 1

首先，您应该学习正确的join语法。简单规则:切勿在 from 子句中使用逗号。

但这不是你的问题。 MySQL 将 bool 表达式视为数字上下文中的数字，1 表示 true，0 表示 false。因此，您只需将这些值相加即可:

order by ((tbl_user_personal.user_community = 'some-text..') +
          (tbl_user_personal.user_sub_caste = 'some-text..') +
          (tbl_user_personal.user_marital_status = 'some-text..') +
          (tbl_user_personal.user_children = 'some-text..') +
          (tbl_user.user_age in ('some-text..', 'some-text..')) +
          (tbl_user.user_country = 'some-text..') +
          (tbl_user_physical.user_height in('some-text..', 'some-text..')) +
          (tbl_user_physical.user_physical_status = 'some-text..') +
          (tbl_user_education.user_education_category = 'some-text..') +
          (tbl_user_education.user_occupation = 'some-text..') +
          (tbl_user_lifestyle.user_eating_habits = 'some-text..')
         ) desc

您还可以将此表达式放在 select 子句中，为其命名，然后将其用于 order by:

order by NumMatches desc;