Mysql 按州、邮政编码和 maternitybg(3 个条件)过滤 Wordpress wp_usermeta 表

Question

我试图在 Wordpress wp_usermeta 表下按三个条件进行搜索，如下所示:

      umeta_id  user_id   meta_key  meta_value       
            49  3   email   schodde.virginia@saugov.sa.gov.au
        51  3   suburb  Noarlunga
        52  3   state   SA
        53  3   postcode    5168
        54  3   maternitybg Yes
        55  3   occupation  Comm midwife
        56  3   wp_user_level   0
        57  3   nick_name   Virginia
        58  3   last_name   Schodde
        59  3   first_name  Virginia
        60  3   title    
        61  3   country  
        63  3   isinstructor     
        64  3   gender   
        65  3   idf_indig_tsi    
        66  3   idf_ct_ld    
        67  3   comments     
        68  3   action   
        69  3   user_id 3
        70  3   submit  Save
        72  3   traininglocation    Adelaide
        46980   3   workplace   Noarlunga Health service
        45304   3   trainingdate    22/03/2005
        48658   3   contact_no  0883849673
        43616   3   wp_capabilities a:1:{s:10:"subscriber";s:1:"1";}
        41939   3   maternitybg Yes

下面是我当前的 SQL，但它没有按邮政编码 3000 过滤:

    select distinct user_id from wp_usermeta where 
( meta_key='state' and meta_value= 'Vic') or 
( meta_key='postcode' and meta_value= '3000') or 
( meta_key='maternitybg' and meta_value= 'No') 
GROUP BY user_id HAVING COUNT(meta_key) = 3

有什么想法吗？

Answer 1

3 号用户有两行 maternitybg = Yes 键，因此匹配:

having count(meta_key) = 3

要解决此问题，请添加 distinct 以验证它是否匹配三个不同的键:

having count(distinct meta_key) = 3