SQL 选择连接：是否可以将所有列作为“prefix.*”作为前缀？

SQL select join: is it possible to prefix all columns as 'prefix.*'?

提问by foxdonut

I'm wondering if this is possible in SQL. Say you have two tables A and B, and you do a select on table A and join on table B:

我想知道这在 SQL 中是否可行。假设您有两个表 A 和 B，并且您在表 A 上执行选择并在表 B 上连接：

SELECT a.*, b.* FROM TABLE_A a JOIN TABLE_B b USING (some_id);

If table A has columns 'a_id', 'name', and 'some_id', and table B has 'b_id', 'name', and 'some_id', the query will return columns 'a_id', 'name', 'some_id', 'b_id', 'name', 'some_id'. Is there any way to prefix the column names of table B without listing every column individually? The equivalent of this:

如果表 A 有列 'a_id'、'name' 和 'some_id'，而表 B 有 'b_id'、'name' 和 'some_id'，则查询将返回列 'a_id'、'name'、'some_id ', 'b_id', 'name', 'some_id'。有没有办法在不单独列出每一列的情况下为表 B 的列名加上前缀？相当于：

SELECT a.*, b.b_id as 'b.b_id', b.name as 'b.name', b.some_id as 'b.some_id'
FROM TABLE_A a JOIN TABLE_B b USING (some_id);

But, as mentioned, without listing every column, so something like:

但是，如前所述，没有列出每一列，所以像：

SELECT a.*, b.* as 'b.*'
FROM TABLE_A a JOIN TABLE_B b USING (some_id);

Basically something to say, "prefix every column returned by b.* with 'something'". Is this possible or am I out of luck?

基本上要说的是，“在 b.* 返回的每一列前面加上‘某事’”。这是可能的还是我运气不好？

Thanks in advance for your help!

在此先感谢您的帮助！

EDIT: advice on not using SELECT * and so on is valid advice but not relevant in my context, so please stick to the problem at hand -- is it possible to add a prefix (a constant specified in the SQL query) to all the column names of a table in a join?

编辑：关于不使用 SELECT * 等的建议是有效的建议，但在我的上下文中不相关，所以请坚持手头的问题 - 是否可以向所有添加前缀（SQL 查询中指定的常量）连接中表的列名？

EDIT: my ultimate goal is to be able to do a SELECT * on two tables with a join, and be able to tell, from the names of the columns I get in my result set, which columns came from table A and which columns came from table B. Again, I don't want to have to list columns individually, I need to be able to do a SELECT *.

编辑：我的最终目标是能够在两个带有连接的表上执行 SELECT * 操作，并且能够从我在结果集中得到的列的名称判断哪些列来自表 A，哪些列来自来自表 B。同样，我不想单独列出列，我需要能够执行 SELECT *。

采纳答案by dkretz

I see two possible situations here. First, you want to know if there is a SQL standard for this, that you can use in general regardless of the database. No, there is not. Second, you want to know with regard to a specific dbms product. Then you need to identify it. But I imagine the most likely answer is that you'll get back something like "a.id, b.id" since that's how you'd need to identify the columns in your SQL expression. And the easiest way to find out what the default is, is just to submit such a query and see what you get back. If you want to specify what prefix comes before the dot, you can use "SELECT * FROM a AS my_alias", for instance.

我在这里看到两种可能的情况。首先，您想知道是否有针对此的 SQL 标准，无论数据库如何，您都可以使用该标准。不，那里没有。其次，您想了解有关特定 dbms 产品的信息。然后你需要识别它。但我想最可能的答案是你会得到类似“a.id，b.id”的东西，因为这就是你需要识别 SQL 表达式中列的方式。找出默认值的最简单方法就是提交这样的查询，看看你得到了什么。例如，如果要指定点之前的前缀，可以使用“SELECT * FROM a AS my_alias”。

回答by Wayne Bryan

It seems the answer to your question is no, however one hack you can use is to assign a dummy column to separate each new table. This works especially well if you're looping through a result set for a list of columns in a scripting language such as Python or PHP.

您的问题的答案似乎是否定的，但是您可以使用的一种技巧是分配一个虚拟列来分隔每个新表。如果您使用脚本语言（例如 Python 或 PHP）循环遍历列列表的结果集，这将特别有效。

SELECT '' as table1_dummy, table1.*, '' as table2_dummy, table2.*, '' as table3_dummy, table3.* FROM table1
JOIN table2 ON table2.table1id = table1.id
JOIN table3 ON table3.table1id = table1.id

I realize this doesn't answer your question exactly, but if you're a coder this is a great way to separate tables with duplicate column names. Hope this helps somebody.

我意识到这并不能完全回答您的问题，但是如果您是一名编码员，这是一种将具有重复列名的表分开的好方法。希望这可以帮助某人。

回答by Motin

I totally understand why this is necessary - at least for me it's handy during rapid prototyping when there are a lot of tables necessary to be joined, including many inner joins. As soon as a column name is the same in a second "joinedtable.*" field wild card, the main table's field values are overriden with the joinedtable values. Error prone, frustrating and a violation of DRY when having to manually specify the table fields with aliases over and over...

我完全理解为什么这是必要的 - 至少对我来说，当有很多表需要连接时，它在快速原型制作过程中很方便，包括许多内部连接。只要第二个“joinedtable.*”字段通配符中的列名称相同，主表的字段值就会被jointable 值覆盖。当不得不一遍又一遍地手动指定带有别名的表字段时，容易出错、令人沮丧和违反 DRY...

Here is a PHP (Wordpress) function to achieve this through code generation together with an example of how to use it. In the example, it is used to rapidly generate a custom query that will provide the fields of a related wordpress post that was referenced through a advanced custom fieldsfield.

这是一个 PHP (Wordpress) 函数，可通过代码生成以及如何使用它的示例来实现此目的。在示例中，它用于快速生成自定义查询，该查询将提供通过高级自定义字段字段引用的相关 wordpress 帖子的字段。

function prefixed_table_fields_wildcard($table, $alias)
{
    global $wpdb;
    $columns = $wpdb->get_results("SHOW COLUMNS FROM $table", ARRAY_A);

    $field_names = array();
    foreach ($columns as $column)
    {
        $field_names[] = $column["Field"];
    }
    $prefixed = array();
    foreach ($field_names as $field_name)
    {
        $prefixed[] = "`{$alias}`.`{$field_name}` AS `{$alias}.{$field_name}`";
    }

    return implode(", ", $prefixed);
}

function test_prefixed_table_fields_wildcard()
{
    global $wpdb;

    $query = "
    SELECT
        " . prefixed_table_fields_wildcard($wpdb->posts, 'campaigns') . ",
        " . prefixed_table_fields_wildcard($wpdb->posts, 'venues') . "
        FROM $wpdb->posts AS campaigns
    LEFT JOIN $wpdb->postmeta meta1 ON (meta1.meta_key = 'venue' AND campaigns.ID = meta1.post_id)
    LEFT JOIN $wpdb->posts venues ON (venues.post_status = 'publish' AND venues.post_type = 'venue' AND venues.ID = meta1.meta_value)
    WHERE 1
    AND campaigns.post_status = 'publish'
    AND campaigns.post_type = 'campaign'
    LIMIT 1
    ";

    echo "<pre>$query</pre>";

    $posts = $wpdb->get_results($query, OBJECT);

    echo "<pre>";
    print_r($posts);
    echo "</pre>";
}

The output:

输出：

SELECT
    `campaigns`.`ID` AS `campaigns.ID`, `campaigns`.`post_author` AS `campaigns.post_author`, `campaigns`.`post_date` AS `campaigns.post_date`, `campaigns`.`post_date_gmt` AS `campaigns.post_date_gmt`, `campaigns`.`post_content` AS `campaigns.post_content`, `campaigns`.`post_title` AS `campaigns.post_title`, `campaigns`.`post_excerpt` AS `campaigns.post_excerpt`, `campaigns`.`post_status` AS `campaigns.post_status`, `campaigns`.`comment_status` AS `campaigns.comment_status`, `campaigns`.`ping_status` AS `campaigns.ping_status`, `campaigns`.`post_password` AS `campaigns.post_password`, `campaigns`.`post_name` AS `campaigns.post_name`, `campaigns`.`to_ping` AS `campaigns.to_ping`, `campaigns`.`pinged` AS `campaigns.pinged`, `campaigns`.`post_modified` AS `campaigns.post_modified`, `campaigns`.`post_modified_gmt` AS `campaigns.post_modified_gmt`, `campaigns`.`post_content_filtered` AS `campaigns.post_content_filtered`, `campaigns`.`post_parent` AS `campaigns.post_parent`, `campaigns`.`guid` AS `campaigns.guid`, `campaigns`.`menu_order` AS `campaigns.menu_order`, `campaigns`.`post_type` AS `campaigns.post_type`, `campaigns`.`post_mime_type` AS `campaigns.post_mime_type`, `campaigns`.`comment_count` AS `campaigns.comment_count`,
    `venues`.`ID` AS `venues.ID`, `venues`.`post_author` AS `venues.post_author`, `venues`.`post_date` AS `venues.post_date`, `venues`.`post_date_gmt` AS `venues.post_date_gmt`, `venues`.`post_content` AS `venues.post_content`, `venues`.`post_title` AS `venues.post_title`, `venues`.`post_excerpt` AS `venues.post_excerpt`, `venues`.`post_status` AS `venues.post_status`, `venues`.`comment_status` AS `venues.comment_status`, `venues`.`ping_status` AS `venues.ping_status`, `venues`.`post_password` AS `venues.post_password`, `venues`.`post_name` AS `venues.post_name`, `venues`.`to_ping` AS `venues.to_ping`, `venues`.`pinged` AS `venues.pinged`, `venues`.`post_modified` AS `venues.post_modified`, `venues`.`post_modified_gmt` AS `venues.post_modified_gmt`, `venues`.`post_content_filtered` AS `venues.post_content_filtered`, `venues`.`post_parent` AS `venues.post_parent`, `venues`.`guid` AS `venues.guid`, `venues`.`menu_order` AS `venues.menu_order`, `venues`.`post_type` AS `venues.post_type`, `venues`.`post_mime_type` AS `venues.post_mime_type`, `venues`.`comment_count` AS `venues.comment_count`
    FROM wp_posts AS campaigns
LEFT JOIN wp_postmeta meta1 ON (meta1.meta_key = 'venue' AND campaigns.ID = meta1.post_id)
LEFT JOIN wp_posts venues ON (venues.post_status = 'publish' AND venues.post_type = 'venue' AND venues.ID = meta1.meta_value)
WHERE 1
AND campaigns.post_status = 'publish'
AND campaigns.post_type = 'campaign'
LIMIT 1

Array
(
    [0] => stdClass Object
        (
            [campaigns.ID] => 33
            [campaigns.post_author] => 2
            [campaigns.post_date] => 2012-01-16 19:19:10
            [campaigns.post_date_gmt] => 2012-01-16 19:19:10
            [campaigns.post_content] => Lorem ipsum
            [campaigns.post_title] => Lorem ipsum
            [campaigns.post_excerpt] => 
            [campaigns.post_status] => publish
            [campaigns.comment_status] => closed
            [campaigns.ping_status] => closed
            [campaigns.post_password] => 
            [campaigns.post_name] => lorem-ipsum
            [campaigns.to_ping] => 
            [campaigns.pinged] => 
            [campaigns.post_modified] => 2012-01-16 21:01:55
            [campaigns.post_modified_gmt] => 2012-01-16 21:01:55
            [campaigns.post_content_filtered] => 
            [campaigns.post_parent] => 0
            [campaigns.guid] => http://example.com/?p=33
            [campaigns.menu_order] => 0
            [campaigns.post_type] => campaign
            [campaigns.post_mime_type] => 
            [campaigns.comment_count] => 0
            [venues.ID] => 84
            [venues.post_author] => 2
            [venues.post_date] => 2012-01-16 20:12:05
            [venues.post_date_gmt] => 2012-01-16 20:12:05
            [venues.post_content] => Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.
            [venues.post_title] => Lorem ipsum venue
            [venues.post_excerpt] => 
            [venues.post_status] => publish
            [venues.comment_status] => closed
            [venues.ping_status] => closed
            [venues.post_password] => 
            [venues.post_name] => lorem-ipsum-venue
            [venues.to_ping] => 
            [venues.pinged] => 
            [venues.post_modified] => 2012-01-16 20:53:37
            [venues.post_modified_gmt] => 2012-01-16 20:53:37
            [venues.post_content_filtered] => 
            [venues.post_parent] => 0
            [venues.guid] => http://example.com/?p=84
            [venues.menu_order] => 0
            [venues.post_type] => venue
            [venues.post_mime_type] => 
            [venues.comment_count] => 0
        )
)

回答by Bill Karwin

The only database I know that does this is SQLite, depending on the settings you configure with PRAGMA full_column_namesand PRAGMA short_column_names. See http://www.sqlite.org/pragma.html

我知道唯一能做到这一点的数据库是 SQLite，具体取决于您使用PRAGMA full_column_names和配置的设置PRAGMA short_column_names。见http://www.sqlite.org/pragma.html

Otherwise all I can recommend is to fetch columns in a result set by ordinal position rather than by column name, if it's too much trouble for you to type the names of the columns in your query.

否则，如果在查询中键入列的名称太麻烦的话，我只能建议按序数位置而不是按列名获取结果集中的列。

This is a good example of why it's bad practice to use SELECT *-- because eventually you'll have a need to type out all the column names anyway.

这是一个很好的例子，说明为什么使用它是不好的做法SELECT *——因为最终无论如何你都需要输入所有的列名。

I understand the need to support columns that may change name or position, but using wildcards makes that harder, not easier.

我理解需要支持可能更改名称或位置的列，但使用通配符会使这变得更难，而不是更容易。

回答by Bill Karwin

I am in kind of the same boat as OP - I have dozens of fields from 3 different tables that I'm joining, some of which have the same name(ie. id, name, etc). I don't want to list each field, so my solution was to alias those fields that shared a name and use select * for those that have a unique name.

我与 OP 处于同一条船上 - 我有来自我要加入的 3 个不同表的数十个字段，其中一些具有相同的名称（即 ID、名称等）。我不想列出每个字段，因此我的解决方案是为共享名称的字段设置别名，并为具有唯一名称的字段使用 select *。

For example :

例如 ：

table a : id, name, field1, field2 ...

表 a : id, name, field1, field2 ...

table b : id, name, field3, field4 ...

表 b : id, name, field3, field4 ...

select a.id as aID, a.name as aName, a. * , b.id as bID, b.name as bName, b. * .....

选择 a.id 作为 aID，a.name 作为 aName，a。* , b.id 作为 bID, b.name 作为 bName, b. * .....

When accessing the results I us the aliased names for these fields and ignore the "original" names.

在访问结果时，我使用这些字段的别名并忽略“原始”名称。

Maybe not the best solution but it works for me....i'm use mysql

也许不是最好的解决方案，但它对我有用....我使用 mysql

回答by dkretz

DIfferent database products will give you different answers; but you're setting yourself up for hurt if you carry this very far. You're far better off choosing the columns you want, and giving them your own aliases so the identity of each column is crystal-clear, and you can tell them apart in the results.

不同的数据库产品会给你不同的答案；但如果你走得太远，你就会受到伤害。您最好选择您想要的列，并为它们提供您自己的别名，这样每列的身份都非常清晰，您可以在结果中将它们区分开来。

回答by Xiè Jìléi

This question is very useful in practice. It's only necessary to list every explicit columns in software programming, where you pay particular careful to deal with all conditions.

这个问题在实践中非常有用。只需要列出软件编程中的每个显式列，您在处理所有条件时要特别小心。

Imagine when debugging, or, try to use DBMS as daily office tool, instead of something alterable implementation of specific programmer's abstract underlying infrastructure, we need to code a lot of SQLs. The scenario can be found everywhere, like database conversion, migration, administration, etc. Most of these SQLs will be executed only once and never be used again, give the every column names is just waste of time. And don't forget the invention of SQL isn't only for the programmers use.

试想一下，在调试时，或者尝试使用DBMS作为日常办公工具时，我们需要编写大量SQL，而不是特定程序员抽象的底层基础设施的一些可变实现。这种场景随处可见，如数据库转换、迁移、管理等。这些 SQL 中的大多数只会执行一次，永远不会再次使用，给每个列名只是浪费时间。并且不要忘记 SQL 的发明并不仅仅供程序员使用。

Usually I will create a utility view with column names prefixed, here is the function in pl/pgsql, it's not easy but you can convert it to other procedure languages.

通常我会创建一个带有列名前缀的实用程序视图，这里是 pl/pgsql 中的函数，这并不容易，但您可以将其转换为其他过程语言。

-- Create alias-view for specific table.

create or replace function mkaview(schema varchar, tab varchar, prefix varchar)
    returns table(orig varchar, alias varchar) as $$
declare
    qtab varchar;
    qview varchar;
    qcol varchar;
    qacol varchar;
    v record;
    sql varchar;
    len int;
begin
    qtab := '"' || schema || '"."' || tab || '"';
    qview := '"' || schema || '"."av' || prefix || tab || '"';
    sql := 'create view ' || qview || ' as select';

    for v in select * from information_schema.columns
            where table_schema = schema and table_name = tab
    loop
        qcol := '"' || v.column_name || '"';
        qacol := '"' || prefix || v.column_name || '"';

        sql := sql || ' ' || qcol || ' as ' || qacol;
        sql := sql || ', ';

        return query select qcol::varchar, qacol::varchar;
    end loop;

    len := length(sql);
    sql := left(sql, len - 2); -- trim the trailing ', '.
    sql := sql || ' from ' || qtab;

    raise info 'Execute SQL: %', sql;
    execute sql;
end
$$ language plpgsql;

Examples:

例子：

-- This will create a view "avp_person" with "p_" prefix to all column names.
select * from mkaview('public', 'person', 'p_');

select * from avp_person;

回答by axelbrz

I totally understand your problem about duplicated field names.

我完全理解您关于重复字段名称的问题。

I needed that too until I coded my own function to solve it. If you are using PHP you can use it, or code yours in the language you are using for if you have this following facilities.

我也需要它，直到我编写了自己的函数来解决它。如果您正在使用 PHP，您可以使用它，或者如果您拥有以下功能，则可以使用您正在使用的语言编写您的代码。

The trick here is that mysql_field_table()returns the table name and mysql_field_name()the field for each row in the result if it's got with mysql_num_fields()so you can mix them in a new array.

这里的技巧是mysql_field_table()返回表名和mysql_field_name()结果中每一行的字段（如果有的话），mysql_num_fields()这样你就可以将它们混合到一个新数组中。

This prefixes all columns ;)

这是所有列的前缀；)

Regards,

问候，

function mysql_rows_with_columns($query) {
    $result = mysql_query($query);
    if (!$result) return false; // mysql_error() could be used outside
    $fields = mysql_num_fields($result);
    $rows = array();
    while ($row = mysql_fetch_row($result)) { 
        $newRow = array();
        for ($i=0; $i<$fields; $i++) {
            $table = mysql_field_table($result, $i);
            $name = mysql_field_name($result, $i);
            $newRow[$table . "." . $name] = $row[$i];
        }
        $rows[] = $newRow;
    }
    mysql_free_result($result);
    return $rows;
}

回答by Cade Roux

There is no SQL standard for this.

对此没有 SQL 标准。

However With code generation (either on demand as the tables are created or altered or at runtime), you can do this quite easily:

但是，通过代码生成（在创建或更改表时或在运行时按需生成），您可以很容易地做到这一点：

CREATE TABLE [dbo].[stackoverflow_329931_a](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [col2] [nchar](10) NULL,
    [col3] [nchar](10) NULL,
    [col4] [nchar](10) NULL,
 CONSTRAINT [PK_stackoverflow_329931_a] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[stackoverflow_329931_b](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [col2] [nchar](10) NULL,
    [col3] [nchar](10) NULL,
    [col4] [nchar](10) NULL,
 CONSTRAINT [PK_stackoverflow_329931_b] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

DECLARE @table1_name AS varchar(255)
DECLARE @table1_prefix AS varchar(255)
DECLARE @table2_name AS varchar(255)
DECLARE @table2_prefix AS varchar(255)
DECLARE @join_condition AS varchar(255)
SET @table1_name = 'stackoverflow_329931_a'
SET @table1_prefix = 'a_'
SET @table2_name = 'stackoverflow_329931_b'
SET @table2_prefix = 'b_'
SET @join_condition = 'a.[id] = b.[id]'

DECLARE @CRLF AS varchar(2)
SET @CRLF = CHAR(13) + CHAR(10)

DECLARE @a_columnlist AS varchar(MAX)
DECLARE @b_columnlist AS varchar(MAX)
DECLARE @sql AS varchar(MAX)

SELECT @a_columnlist = COALESCE(@a_columnlist + @CRLF + ',', '') + 'a.[' + COLUMN_NAME + '] AS [' + @table1_prefix + COLUMN_NAME + ']'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table1_name
ORDER BY ORDINAL_POSITION

SELECT @b_columnlist = COALESCE(@b_columnlist + @CRLF + ',', '') + 'b.[' + COLUMN_NAME + '] AS [' + @table2_prefix + COLUMN_NAME + ']'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table2_name
ORDER BY ORDINAL_POSITION

SET @sql = 'SELECT ' + @a_columnlist + '
,' + @b_columnlist + '
FROM [' + @table1_name + '] AS a
INNER JOIN [' + @table2_name + '] AS b
ON (' + @join_condition + ')'

PRINT @sql
-- EXEC (@sql)

回答by kobejr

Cant do this without aliasing , simply because, how are you going to reference a field in the where clause, if that field exists in the 2 or 3 tables you are joining? It will be unclear for mysql which one you are trying to reference.

不能在没有别名的情况下做到这一点，仅仅是因为，如果该字段存在于您要加入的 2 或 3 个表中，您将如何引用 where 子句中的字段？mysql 不清楚您要参考哪一个。