基于另一个具有自联接的列值按值排序

jm81lzqq 于 2021-06-20 发布在 Mysql

关注(0)|答案(1)|浏览(335)

我以前问过这个问题，但是现在需要添加更多的字段，同样的方法现在因为太多的自连接而超时。
这是我当前的查询。它很慢，但在添加城市和州列时性能足够，但在添加zip列时超时。还有别的方法吗？

SELECT um.user_id, 
       users.display_name, 
       GROUP_CONCAT(um.meta_value SEPARATOR ' '),
       city.meta_value,
       state.meta_value,
       zip.meta_value
FROM `wp_7bv88z_usermeta` um, 
     `wp_7bv88z_pmpro_memberships_users` u, 
     `wp_7bv88z_users` users,
     `wp_7bv88z_usermeta` lname,
     `wp_7bv88z_usermeta` fname,
     `wp_7bv88z_usermeta` city,
     `wp_7bv88z_usermeta` state,
     `wp_7bv88z_usermeta` zip
WHERE u.user_id = um.user_id 
     and users.id = u.user_id 
     and (lname.user_id = u.user_id and lname.meta_key = 'pmpro_slastname')
     and (fname.user_id = u.user_id and fname.meta_key = 'pmpro_sfirstname')
     and (city.user_id = u.user_id and city.meta_key = 'pmpro_scity')
     and (state.user_id = u.user_id and state.meta_key = 'pmpro_sstate')
     and (zip.user_id = u.user_id and zip.meta_key = 'pmpro_szipcode')
     and (um.meta_key='pmpro_sfirstname' or um.meta_key='pmpro_slastname') 
     and u.status='active'
GROUP BY lname.meta_value, fname.meta_value, city.meta_value, 
         state.meta_value, zip.meta_value, um.user_id

样本数据：
用户\元表

╔════=====╦══════════════==╦══════=======╗
║ user_id ║  meta_key        ║ meta_value  ║
╠════=====╬══════════════==╬══════=======╣
║  1      ║ pmpro_sfirstname ║ Jeff        ║
║  1      ║ pmpro_slasttname ║ Atwood      ║
║  1      ║ pmpro_scity      ║ Chicago     ║
║  1      ║ pmpro_sstate     ║ IL          ║
║  1      ║ pmpro_szip       ║ 60007       ║
║  2      ║ pmpro_sfirstname ║ Geoff       ║
║  2      ║ pmpro_slasttname ║ Davis       ║
║  2      ║ pmpro_scity      ║ Fairfax     ║
║  2      ║ pmpro_sstate     ║ VA          ║
║  2      ║ pmpro_szip       ║ 22030       ║
║  3      ║ pmpro_sfirstname ║ Jarrod      ║
║  3      ║ pmpro_slasttname ║ Minor       ║
║  3      ║ pmpro_scity      ║ Dallas      ║
║  3      ║ pmpro_sstate     ║ TX          ║
║  3      ║ pmpro_szip       ║ 75001       ║
║  4      ║ pmpro_sfirstname ║ Joel        ║
║  4      ║ pmpro_slasttname ║ Brown       ║
║  4      ║ pmpro_scity      ║ Boston      ║
║  4      ║ pmpro_sstate     ║ MA          ║
║  4      ║ pmpro_szip       ║ 02101       ║
╚════=====╩════════════=====╩══════======╝

预期结果：

1, Jeff Atwood, Chicago, IL, 60007
4, Joel Brown, Fairfax, VA, 22030
2, Geoff Davis, Dallas, TX, 75001
3, Jarrod Minor, Boston, MA, 02101

sql mysql wordpress

来源：https://stackoverflow.com/questions/50873823/sort-by-value-based-on-another-column-value-with-self-joins

1条答案

按热度按时间

v2g6jxz61#

您可以使用聚合从eav数据结构中选取值

select  user_id,
        max( case when meta_key = 'pmpro_sfirstname' then meta_value else null end) fname,
        max( case when meta_key = 'pmpro_slasttname' then meta_value else null end) lname,
        max( case when meta_key = 'pmpro_scity' then meta_value else null end) city,
        max( case when meta_key = 'pmpro_sstate' then meta_value else null end) state,
        max( case when meta_key = 'pmpro_szip' then meta_value else null end) zip
from user_meta
group by user_id

演示

赞(0）回复(0）举报 2021-06-20

我来回答

基于另一个具有自联接的列值按值排序

1条答案

相关问题

热门标签

最新问答