MySQL查询INNER JOIN与别名 [英] MySQL Query INNER JOIN with aliases

问题描述

我有两个表:users和users_info

users看起来像这样:

+----+----------+-------+
| id | slug     | name  |
+----+----------+-------+
|  1 | theploki | Kris  |
+----+----------+-------+

和users_info看起来像这样:

+----+--------+----------+---------------+
| id | parent | info_key | info_val      |
+----+--------+----------+---------------+
|  1 | 1      | email    | [e-mail] |
+----+--------+----------+---------------+
|  2 | 1      | age      | 28            |
+----+--------+----------+---------------+

我想SELECT一个具有user_info email ='[e-mail]'的user -和-
返回所有user_info值和users值

I want to SELECT a user who has user_info email = '[e-mail]'
- and -
return ALL user_info values and users values

这是我要寻找的结果:

+----+----------+-------+---------------+-----+
| id | slug     | name  | email         | age |
+----+----------+-------+---------------+-----+
|  1 | theploki | Kris  | [e-mail] | 28  |
+----+----------+-------+---------------+-----+

到目前为止，我得到的最接近的查询是:

So far the closest I've gotten is with this query:

SELECT users.*, users_info.* FROM users
INNER JOIN users_info on users_info.parent = users.id
where users.id = (SELECT users_info.parent FROM users_info
    WHERE users_info.parent = users.id
    AND users_info.info_val = '[e-mail]')

它返回此结果:

+----+----------+-------+----+--------+----------+---------------+
| id | slug     | name  | id | parent | info_key | info_val      |
+----+----------+-------+----+--------+----------+---------------+
|  1 | theploki | Kris  |  1 |  1     | email    | [e-mail] |
+----+----------+-------+----+--------+----------+---------------+
|  1 | theploki | Kris  |  2 |  1     | age      | 28            |
+----+----------+-------+----+--------+----------+---------------+

很明显，我不需要users_info结果的id，我希望每个info_key都是别名"(或列名)，每个info_val都是该别名"的值.

Obviously I don't need the id of the users_info result and I want each info_key to be the "alias" (or column name) and each info_val to be the value for that "alias".

推荐答案

在这种情况下，您可以这样做:)只是一个简单的表枢轴.

For this case, you can do it like this;) Just a simple table pivot.

select
    users.id,
    users.slug,
    users.name,
    max(if(users_info.info_key = 'email', users_info.info_val, null)) as email,
    max(if(users_info.info_key = 'age', users_info.info_val, null)) as age
from users
inner join users_info
on users.id = users_info.parent
group by users.id

SQLFiddle演示此处

如果您有动态info_key，则需要使用动态sql来执行此操作，这里我为您提供示例.

If you have a dynamic info_key, you will need a dynamic sql to do this, here I give you a sample.

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(if(users_info.info_key = ''',
      users_info.info_key,
      ''', users_info.info_val, null)) as ',
      users_info.info_key
    )
  ) INTO @sql
FROM users
inner join users_info
on users.id = users_info.parent
;

SET @sql = CONCAT('select users.id, users.slug, users.name, ', @sql, ' FROM users
inner join users_info group by users.id having email = \'[e-mail]\'');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SQLFiddle演示此处

这篇关于MySQL查询INNER JOIN与别名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！