如何加入mysql表 [英] How to join mysql tables

问题描述

我有一张这样的旧桌子:

I've an old table like this:

user> id | name | address | comments

现在，由于某些原因，我必须创建一个别名"表，以允许某些用户使用别名.我创建了一个新表"user_alias"，如下所示:

And now I've to create an "alias" table to allow some users to have an alias name for some reasons. I've created a new table 'user_alias' like this:

user_alias> name | user

但是由于我的SQL级别很低，现在我遇到了一个问题...如何连接两个表以生成如下内容:

But now I have a problem due my poor SQL level... How to join both tables to generate something like this:

1 | my_name    | my_address    | my_comments
1 | my_alias   | my_address    | my_comments
2 | other_name | other_address | other_comments

我的意思是，我想进行一个"SELECT ..."查询，该查询以与"user"表ALL users和ALL别名相同的格式返回.

I mean, I want to make a "SELECT..." query that returns in the same format as the "user" table ALL users and ALL alias.. Something like this:

SELECT user.* FROM user LEFT JOIN user_alias ON `user`=`id`

但这对我不起作用.

推荐答案

类似

SELECT user.name, user.address, user.comment FROM user
UNION ALL
SELECT user_alias.alias, user.address, user.comment 
   FROM user INNER JOIN user_alias on user.name = user_alias.name
ORDER BY name

会让您接近想要的东西.

will get you close to what you want.

您需要将两个SELECT联合在一起，因为其他人提出的LEFT JOIN解决方案将在具有别名的用户的结果集中仅包含一行，而不是您的问题中指定的两行.

You need to UNION two SELECTs together because the LEFT JOIN solution proposed by others will include only one row in the result set for users with aliases, not two as specified in your question.

但是您应该使公共列加入用户，并为id列(而不是name列)作为别名.

But you should make the common column joining user and alias the id column, not the name column.

这篇关于如何加入mysql表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！