批量创建别名？ [英] Create Aliases In Bulk?

问题描述

我正在尝试加入三个表，产品，税项& 类别。所有这些表具有冲突的列名。我知道我们可以通过创建别名来解决此冲突。

I'm trying to join three tables, products, taxes & categories. All these tables have conflicting column names. I'm aware that we get around this conflict by creating aliases.

products:
    id
    name
    ...

taxes
    id
    name
    ...

categories
    id
    name
    ...

我的问题是，是否有方便的方法来批量创建别名？我的意思是类似

My question is, is there a convenient way to create aliases in bulk? What I mean is something like

SELECT products.* as product.*, taxes.* as tax.*, categories.* as category.*
...

我希望结果集中会有像：

I would expect the result set to have columns like:

product.id, product.name, tax.id, tax.name, ...

或者，我是否必须坚持一些乏味的操作，例如：

Or, do I have to stick with something tedious as:

SELECT products.id as product_id, products.name as product_name,
       taxes.id as tax_id, taxes.name as tax_name, ...

推荐答案

您在类固醇上搜索 * 。不幸的是， SQL 中没有此类功能。

You search for * on steroid. Unfortunaltely there is no such functionality in SQL.

解决方法1：

在您喜欢的文本编辑器（vim，atom等）中使用块选择的功能。将每一列放在新行中。阻止选择以写入 AS 和表前缀。然后阻止选择并复制列名称。

Use power of block selection in your favourite text editor(vim,atom,...). Place each column in new row. Block selection to write AS and table prefix. Then block selection and copy column names.

解决方法2：

使用生成选择列表INFORMATION_SCHEMA.COLUMNS ：

SELECT 
 string_agg(FORMAT('%s.%s AS %s_%s', "table_name",
                   column_name,"table_name", column_name), ', ')
FROM information_schema.columns
WHERE "table_name" IN ('products', 'taxes', 'categories');

SqlFiddleDemo

您可以使用 E'，\n'将每一列放在新行中。

You could use E',\n' to place each column in new line.

输出：

╔════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╗
║                                                                                   string_agg                                                                                   ║
╠════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╣
║ products.id AS products_id, products.name AS products_name, taxes.id AS taxes_id, taxes.name AS taxes_name, categories.id AS categories_id, categories.name AS categories_name ║
╚════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╝

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