题 SQL select join:是否可以将所有列前缀为'prefix。*'?


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

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

如果表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);

但是,如上所述,没有列出每一列,所以类似于:

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

基本上可以说,“b。*返回的每一列都带有'某事'”。这可能还是我运气不好?

在此先感谢您的帮助!

编辑:不使用SELECT *等建议是有效的建议,但在我的上下文中不相关,所以请坚持手头的问题 - 是否可以添加前缀(SQL查询中指定的常量)给所有连接中表的列名?

编辑:我的最终目标是能够在两个带有连接的表上执行SELECT *,并且能够从我在结果集中获得的列的名称中知道哪些列来自表A以及哪些列来自哪些列来自表B.再次,我不想单独列出列,我需要能够做一个SELECT *。


150
2017-12-01 03:15


起源


您对查询结果的期望是什么?我很困惑 - GregD
GregD:我希望b。*中出现的所有列名都带有我指定的常量前缀。例如,我想要指定'special_'前缀并获取'special_name'和'special_number',而不是'name'和'number'。但我不想单独为每个列执行此操作。 - foxdonut
当我快速选择查看多个表中的列时,我有时会选择SELECT'AAAAA',A。*,'BBBBB',B。* FROM TableA AS A JOIN TableB AS B ON A.ID = B.ID so I沿着行扫描时至少有一个表标识符 - Kristen
可能重复: stackoverflow.com/questions/2595068/... - Andrioid


答案:


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


33
2017-12-01 03:42



我不确定这是如何回答你的问题的。我正在使用MS SQL Server并在表名后没有将别名附加到结果集中的列名称时添加别名。 - paiego
没有别名添加到mysql输出 - user3791372


似乎你的问题的答案是否定的,但是你可以使用的一个黑客是分配一个虚拟列来分隔每个新表。如果您在脚本语言(如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

我意识到这并不能完全回答你的问题,但如果你是一个编码器,这是一个很好的方法来分离具有重复列名的表。希望这有助于某人。


50
2018-03-29 13:16





我完全理解为什么这是必要的 - 至少对我而言,在快速原型制作过程中,当需要加入很多表时,包括许多内部连接时,它很方便。只要第二个“joinedtable。*”字段通配符中的列名相同,主表的字段值就会用jointable值覆盖。当必须手动指定带有别名的表字段时,容易出错,令人沮丧并且违反DRY ......

这是一个PHP(Wordpress)函数,通过代码生成以及如何使用它的示例来实现此目的。在该示例中,它用于快速生成自定义查询,该查询将提供通过a引用的相关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>";
}

输出:

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
        )
)

17
2018-01-19 16:17





我知道的唯一数据库是SQLite,具体取决于您配置的设置 PRAGMA full_column_names 和 PRAGMA short_column_names。看到 http://www.sqlite.org/pragma.html

否则,我建议的是按顺序位置而不是列名来获取结果集中的列,如果在查询中键入列的名称太麻烦了。

这是一个很好的例子 这是不好的做法 SELECT *  - 因为最终你需要输入所有的列名。

我理解需要支持可能更改名称或位置的列,但使用通配符可以实现这一点 更难,不容易。


8
2017-12-01 03:39



请注意两者 full_column_names 和 short_column_names 是 弃用 在SQLite中。 - isanae


我与OP的船相同 - 我从3个不同的表中加入了几十个字段,其中一些具有相同的名称(即id,name等)。我不想列出每个字段,因此我的解决方案是为那些共享名称的字段添加别名,并对具有唯一名称的字段使用select *。

例如 :

表a:   ID,   名称,   FIELD1,   field2 ......

表b:   ID,   名称,   字段3,   field4 ...

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

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

也许不是最好的解决方案,但它适用于我....我使用的是mysql


5
2017-12-02 14:06





不同的数据库产品会给你不同的答案;但如果你带着这么远的话,你就会让自己受伤。你最好选择你想要的列,并给它们自己的别名,这样每列的标识都是清晰的,你可以在结果中区分它们。


4
2017-12-01 03:19



重点,但我的目标是非常通用的,所以不明确不是问题。事实上,必须要具体 将 是个问题。 - foxdonut
见下面的进一步提交。可以使用dot.notation,这可能是默认的吗? - dkretz


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

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

通常我会创建一个以列名为前缀的实用程序视图,这里是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;

例子:

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

select * from avp_person;

3
2017-10-22 03:43





这没有SQL标准。

但是,通过代码生成(在创建或更改表时或在运行时根据需要),您可以非常轻松地执行此操作:

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)

2
2017-12-01 04:58



这可行,但问题相当愚蠢。为什么不只是执行联合或子查询。你为什么要加入并仍然希望列名中有表前缀? - D3vtr0n
凯德:谢谢你的信息,这很有趣。不幸的是,在我的情况下,生成/修改数据库不是一个选项。 Devtron:如果您尝试将从查询返回的信息映射到对象的不同属性,那么该信息将变得非常有用。 - foxdonut
有时,不同表中的列名相同,但不包含相同的值。因此需要为它们添加前缀以在视图或派生表(必须具有所有唯一列名称)中区分它们。 - Cade Roux
@Frederic,你的代码必须住在某个地方 - 这只是生成代码。同样,这可以在开发期间完成一次,也可以在运行时动态完成。 - Cade Roux