php - mysql:通过自定义搜索查询查找匹配的 friend

Question

我想通过示例查找匹配的 friend (城市|年龄|性别|等)

用户选择从表单和html选择中查找 friend

年龄(22 - 36 岁之间)性别女 )城市(a、b、c)

我的 table 就像

---------------------------------------------
  member_id   |   member_key  | member_value    
---------------------------------------------
   2          |     city      |  a
---------------------------------------------
   2          |     gender    |  female
---------------------------------------------
   2          |     age       |  20
---------------------------------------------
   2          |     degree    |  5
---------------------------------------------
   3          |     city      |  x
---------------------------------------------
   3          |     gender    |  male
---------------------------------------------
   3          |     age       |  25
---------------------------------------------
   3          |     degree    |  3
---------------------------------------------
   4          |     city      |  a
---------------------------------------------
   4          |     gender    |  female
---------------------------------------------
   4          |     age       |  26
---------------------------------------------
   4          |     degree    |  3
---------------------------------------------

我希望结果是这样的

----------------------
member_id      |  match_count 
----------------------
4            |   3
----------------------
2            |   2
----------------------
3            |   1

我尝试过这样的查询但不起作用

SELECT `member_id`, `member_key`, SUM(
    CASE `member_key`
        WHEN ( "age" AND `member_value` BETWEEN 22 AND 36 )
        THEN 1
        WHEN ( "city" and `member_value` IN ("a","b","c") )
        THEN 1
        WHEN ( "gender" and `member_value` = "male" )
        THEN 1
        ELSE 0
    END ) AS match_count
FROM members_info

我找到了这个解决方案，它对我来说看起来不错，但性能:(

SELECT member_id,concat_ws(",",member_key) as match_in,count(member_id) as match_count
FROM members_info WHERE
  ( member_key = "age" AND member_value BETWEEN 22 AND 36) OR 
  ( member_key = "city" AND member_value IN ('a', 'b', 'c') ) OR
  ( member_key = "gender" AND member_value = "female" )
GROUP BY member_id

Answer 1

现在我明白了你想要实现的目标。这是另一种可能更快的方法。希望一切顺利:)

SELECT T.member_id, COUNT(*) AS affinity
FROM
    (SELECT member_id
    FROM members_info
    WHERE member_key = 'age' AND member_value BETWEEN 22 AND 36
    UNION
    SELECT member_id
    FROM members_info
    WHERE member_key = 'city' AND member_value IN ('a', 'b', 'c')
    UNION
    SELECT member_id
    FROM members_info
    WHERE member_key = 'gender' AND member_value = 'female') AS T
GROUP BY T.member_id