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