SELECT *列的MySQL别名 [英] MySQL alias for SELECT * columns

问题描述

我正在创建一个使用两次来自同一表的数据的视图.结果，相同的列名称出现了两次.

I'm creating a view that is using data that comes from the same table twice. As a result, same column names appear twice.

因此，我需要为这些列提供别名.如果我要这样做，我会写为:

Thus, i need to give aliases to these columns. If i were to do it, i'd write it as:

SELECT u.* as 'one_*', u2.* as 'two_*'
FROM users u
LEFT JOIN relationships r ON u.id=r.id_one
LEFT JOIN users u2 ON r.id_two=u2.id

但这不起作用.感谢您的帮助！

But that doesn't work. Thanks for your help!

这是我实际上得到的数据:

Here's the data i'm actually getting:

|  id  | name |  id  | name |
   1     john    2     alex

这是我想获取的数据(同时仍使用SELECT u.*, u2.*):

Here's the data i'd like to get (while still using a SELECT u.*, u2.*):

|  id  | name |  brother_id  | brother_name |
   1     john        2             alex

推荐答案

您不能将*与别名一起使用.别名可用于单独的列.

You can't use * with an alias. Aliases can be used for individual columns.

您必须为每列取别名.

不幸的是，如果您有很多列，则需要执行以下操作:

So unfortunately, if you have a lot of columns, you'll need to go:

SELECT u.col1 AS u_col1
    , u.col2 AS u_col2
    , u.col3 AS u_col3
    -- etc
    , u2.col1 AS u2_col1
    , u2.col2 AS u2_col2
    , u2.col3 AS u2_col3
    -- etc
FROM table1 AS u
-- INNER JOIN / LEFT OR RIGHT OUTER JOIN / ,
    table2 AS u2

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