MySQL查询-基于两个因素连接数据，然后根据值自定义数据排序方式 [英] MySQL query - Join data based on two factors, then customise the way the data is sorted based on values

问题描述

首先，我是查询多个表的新手，所以如果这是一个愚蠢的问题，我深表歉意，但我们都必须从某个地方开始！

firstly I'm new to querying multiple tables so I apologise if this is a bit of a stupid question, but we all have to start somewhere!

我制作了一张图片，应该可以更容易理解:

I've made a picture which should make this easier to understand:

http://www.mediumsliced.co.uk/temp/mysqlhelp.jpg

我的第一个表 wp_user 有几列 - 我想要 3 列中的值，即 ID user_email 和 user_nicename.
我的第二个表 wp_usermeta 有 3 列，用于存储用户的元数据.这些列是 user_id、meta_key 和 meta_value.此表中的 user_id 值始终对应于 wp_user 中匹配的 ID 值(见图).

My first table wp_user has several columns - I want the values from 3 columns which are ID user_email and user_nicename.
My second table wp_usermeta has 3 columns which stores meta data for the users. These columns are user_id, meta_key and meta_value. The user_id values in this table always correspond to the matching ID values in wp_user (see picture).

我想将 meta_key 字段中的数据与其 meta_value 结合起来.到目前为止，我有这个:

I would like to join data from the meta_key fields along with it's meta_value. So far I have this:

SELECT wp_users.ID, wp_users.user_login, wp_users.user_nicename, wp_users.user_email,
       wp_usermeta.user_id, wp_usermeta.meta_key, wp_usermeta.meta_value
FROM wp_users, wp_usermeta
WHERE (wp_users.ID = wp_usermeta.user_id);

这显示了我需要的所有信息，但是我遇到的问题是我实际上想将来自 meta_key 的数据显示为单独的列，并根据用户的 ID 在该用户的正确行中显示该元键的 meta_value.我还需要排除任何没有 wp_user_level 为 0 的用户.(再次希望我提供的图片更清晰)

This displays all the info I need however the issue I have is that I actually want to display the data from meta_key as individual columns and the meta_value for that meta key in the correct row for that user based on their ID. I also need to exclude any users which do not have their wp_user_level as 0. (Again hopefully this is clearer on my picture I provided)

显然，当涉及到 MySql 时，我有很多东西要学习，但如果有人能指导我获得最终结果，我将非常感激，如果您能解释查询以便我可以从中学习，我将更加感激只需将其复制并粘贴到位.

Obviously I have a lot to learn when it comes to MySql but if anyone could guide me to the end result I'd be really grateful, even more so if you could explain the query so that I can learn from it as opposed to just copy and paste it in place.

非常感谢，如果您需要更多信息或需要我澄清任何事情，请随时提问！

Thanks a lot if you need more information or need me to clarify anything then feel free to ask!

克雷格

推荐答案

Select wp_users.ID
    , wp_users.user_login
    , wp_users.user_email
    , wp_users.user_nicename
    , Min( Case When wp_usermeta.meta_key = 'first_name' Then wp_usermeta.meta_value End ) As first_name
    , Min( Case When wp_usermeta.meta_key = 'last_name' Then wp_usermeta.meta_value End ) As last_name
    , Min( Case When wp_usermeta.meta_key = 'address' Then wp_usermeta.meta_value End ) As address
    , Min( Case When wp_usermeta.meta_key = 'dob' Then wp_usermeta.meta_value End ) As dob
From wp_user
    Join wp_usermeta
        On wp_usermeta.user_id = wp_user.ID
Where Exists    (
                Select 1
                From wp_usermeta As Meta1
                Where Meta1.user_id = wp_user.id
                    And Meta1.meta_key = 'wp_user_level' 
                    And Meta1.meta_value = '0' 
                )
        And wp_usermeta.meta_key In('first_name','last_name','address','dob')                   
Group By wp_users.ID
    , wp_users.user_login
    , wp_users.user_email
    , wp_users.user_nicename

首先，正如其他人所提到的，编写此查询如此繁琐的原因之一是您必须使用 EAV 结构.元"表的概念实际上是关系设计的诅咒.其次，为了从 EAV 中获取信息，您必须创建所谓的交叉表查询，您可以在其中构建查询中所需的列.一般来说，关系数据库不是为即时列生成而设计的，就像我在我的解决方案中所做的那样，并且是 EAV 所必需的.

First, as others have mentioned, one of the reasons that this query is so cumbersome to write is that you are having to use an EAV structure. The concept of a "meta" table is really an anathema to relational design. Second, in order to get information out of an EAV, you have to create what is called a crosstab query where you build the columns you want in your query. In general, relational databases are not designed for on-the-fly column generation such as I'm doing in my solution and is required for EAVs.

这篇关于MySQL查询-基于两个因素连接数据，然后根据值自定义数据排序方式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！