不同的数据库产品会给你不同的答案;但如果你走得太远，你是在自讨苦吃。您最好选择您想要的列，并为它们提供自己的别名，以便每个列的标识非常清晰，并且可以在结果中区分它们。

从这个解决方案出发，我将如何处理这个问题:

首先创建一个所有AS语句的列表:

DECLARE @asStatements varchar(8000)

SELECT @asStatements = ISNULL(@asStatements + ', ','') + QUOTENAME(table_name) + '.' + QUOTENAME(column_name) + ' AS ' + '[' + table_name + '.' + column_name + ']'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TABLE_A' OR TABLE_NAME = 'TABLE_B'
ORDER BY ORDINAL_POSITION

然后在你的查询中使用它:

EXEC('SELECT ' + @asStatements + ' FROM TABLE_A a JOIN TABLE_B b USING (some_id)');

然而，这可能需要修改，因为类似的东西只在SQL Server中测试。但是这段代码在SQL Server中并不完全有效，因为不支持USING。

请评论，如果你可以测试/纠正这段代码，例如MySQL。

与非常好的“PHP (Wordpress)函数”相同的响应，但为CakePHP 4.3编码。 放在src/Controller/Component/MyUtilsComponent.php中

<?php

namespace App\Controller\Component;

use Cake\Controller\Component;
use Cake\Datasource\ConnectionManager;

class MyUtilsComponent extends Component
{
    public static function prefixedTableFieldsWildcard(string $table, string $alias, string $connexion = 'default'): string
    {
        $c = ConnectionManager::get($connexion);
        $columns = $c->execute("SHOW COLUMNS FROM $table");
        $field_names = [];
        foreach ($columns as $column) {
            $field_names[] = $column['Field'];
        }

        $prefixed = [];
        foreach ($field_names as $field_name) {
            $prefixed[] = "`{$alias}`.`{$field_name}` AS `{$alias}.{$field_name}`";
        }
        return implode(', ', $prefixed);
    }
}

测试和使用

    function testPrefixedTableFieldsWildcard(): void
    {
        $fields = MyUtilsComponent::prefixedTableFieldsWildcard('metas', 'u', 'test');
        $this->assertEquals('`u`.`id` AS `u.id`, `u`.`meta_key` AS `u.meta_key`, `u`.`meta_value` AS `u.meta_value`, `u`.`meta_default` AS `u.meta_default`, `u`.`meta_desc` AS `u.meta_desc`', $fields,);
    }

我在PostgreSQL 13中使用to_jsonb函数来获得连接表中的所有字段作为一列。

select
  TABLE_A.*,
  to_jsonb(TABLE_B.*) as b,
  to_jsonb(TABLE_C.*) as c
from TABLE_A
left join TABLE_B on TABLE_B.a_id=TABLE_A.id
left join TABLE_C on TABLE_C.a_id=TABLE_A.id
where TABLE_A.id=1

结果你会得到TABLE_A列加上b和c列的数量:

您只需要解析b和c列以将它们转换为对象。

我完全理解为什么这是必要的——至少对我来说，在快速创建原型时，有很多表需要连接，包括许多内部连接，这很方便。只要一个列名在第二个joinedtable中是相同的。*"字段通配符，主表的字段值将被joinedtable值覆盖。容易出错，令人沮丧和违反DRY时，必须手动指定表字段与别名一遍又一遍…

下面是一个PHP (Wordpress)函数，通过代码生成以及如何使用它的示例来实现这一点。在本例中，它用于快速生成一个自定义查询，该查询将提供通过高级自定义fields字段引用的相关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
        )
)

PHP 7.2 + MySQL/Mariadb

MySQL会给你发送多个相同名称的字段。甚至在终端客户端。但如果你想要一个关联数组，你必须自己创建键。

感谢@axelbrz的原创。我已经将它移植到更新的php，并对它进行了一些清理:

function mysqli_rows_with_columns($link, $query) {
    $result = mysqli_query($link, $query);
    if (!$result) {
        return mysqli_error($link);
    }
    $field_count = mysqli_num_fields($result);
    $fields = array();
    for ($i = 0; $i < $field_count; $i++) {
        $field = mysqli_fetch_field_direct($result, $i);
        $fields[] = $field->table . '.' . $field->name; # changed by AS
        #$fields[] = $field->orgtable . '.' . $field->orgname; # actual table/field names
    }
    $rows = array();
    while ($row = mysqli_fetch_row($result)) {
        $new_row = array();
        for ($i = 0; $i < $field_count; $i++) {
            $new_row[$fields[$i]] = $row[$i];
        }
        $rows[] = $new_row;
    }
    mysqli_free_result($result);
    return $rows;
}

$link = mysqli_connect('localhost', 'fixme', 'fixme', 'fixme');
print_r(mysqli_rows_with_columns($link, 'select foo.*, bar.* from foo, bar'));