不能这样做没有别名，只是因为，你将如何引用一个字段在where子句，如果该字段存在于2或3个表，你要连接? 这将是不清楚的mysql，你试图引用哪一个。

不能这样做没有别名，只是因为，你将如何引用一个字段在where子句，如果该字段存在于2或3个表，你要连接? 这将是不清楚的mysql，你试图引用哪一个。

如果关心模式变化，这可能对你有用: 1. 在所有涉及的表上运行'DESCRIBE table'查询。 2. 使用返回的字段名动态构造以所选别名为前缀的列名字符串。

我完全理解为什么这是必要的——至少对我来说，在快速创建原型时，有很多表需要连接，包括许多内部连接，这很方便。只要一个列名在第二个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
        )
)

Select *通常会导致糟糕的代码，因为往往会添加新列，或者表中列的顺序经常改变，这通常会以非常微妙的方式破坏Select *。所以列出列是正确的解决方案。

至于如何进行查询，mysql不确定，但在sqlserver中，您可以从syscolumns中选择列名，并动态构建select子句。

你可能会认为微软花了13年时间才把它装进去。它对于调试非常有用。 我已经养成了这样做的习惯:选择我认为我想要比较的列，然后在最后加上一个*来捕获我可能想要查看的任何其他内容。

选择a.breed, a.size, p.breed, p.size,a.，p。 源自动物 加入宠物p在a.breed=p.breed

不管怎样，你懂的。