SQL select连接:是否可以将所有列都前缀为'prefix.*'? [英] SQL select join: is it possible to prefix all columns as 'prefix.*'?

问题描述

我想知道在SQL中是否可行.假设您有两个表A和B，然后对表A进行选择，然后对表B进行联接:

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:

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"，名称"，"some_id".有什么方法可以在不单独列出每一列的情况下为表B的列名加上前缀?等效于此:

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:

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

基本上可以说，将b.*返回的每一列都以'something'开头".这可能吗?还是我不走运?

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

提前感谢您的帮助！

关于不使用SELECT *的建议是有效的建议，但与我的上下文无关，因此请坚持使用当前的问题-是否可以添加前缀(在SQL查询中指定的常量)联接中表的所有列名?

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 *，并能够从我在结果集中获得的列名称中分辨出哪些列来自表A和哪些列来自表B.同样，我不想单独列出列，我需要能够执行SELECT *.

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 *.

推荐答案

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

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 select连接:是否可以将所有列都前缀为'prefix.*'?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！