我想知道这在SQL中是否可行。假设你有两个表A和B，你在表A上做一个选择，在表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的列名加上前缀而不单独列出每一列吗?等价于这个:
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);
但是，如前所述，没有列出每一列，所以像这样:
SELECT a.*, b.* as 'b.*'
FROM TABLE_A a JOIN TABLE_B b USING (some_id);
基本上就是说，“用‘something’为b.*返回的每一列添加前缀”。这可能吗，还是我运气不好?
编辑
关于不使用SELECT *等的建议是有效的建议，但与我的上下文无关，因此请关注眼前的问题——是否可以在连接中为表的所有列名添加前缀(SQL查询中指定的常量)?
我的最终目标是能够通过连接对两个表执行SELECT *操作，并且能够从结果集中获得的列的名称中分辨出哪些列来自表a，哪些列来自表b。同样，我不想单独列出列，我需要能够执行SELECT *操作。
对此没有SQL标准。
然而，通过代码生成(在表创建或修改或运行时按需生成)，你可以很容易地做到这一点:
CREATE TABLE [dbo].[stackoverflow_329931_a](
[id] [int] IDENTITY(1,1) NOT NULL,
[col2] [nchar](10) NULL,
[col3] [nchar](10) NULL,
[col4] [nchar](10) NULL,
CONSTRAINT [PK_stackoverflow_329931_a] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[stackoverflow_329931_b](
[id] [int] IDENTITY(1,1) NOT NULL,
[col2] [nchar](10) NULL,
[col3] [nchar](10) NULL,
[col4] [nchar](10) NULL,
CONSTRAINT [PK_stackoverflow_329931_b] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
DECLARE @table1_name AS varchar(255)
DECLARE @table1_prefix AS varchar(255)
DECLARE @table2_name AS varchar(255)
DECLARE @table2_prefix AS varchar(255)
DECLARE @join_condition AS varchar(255)
SET @table1_name = 'stackoverflow_329931_a'
SET @table1_prefix = 'a_'
SET @table2_name = 'stackoverflow_329931_b'
SET @table2_prefix = 'b_'
SET @join_condition = 'a.[id] = b.[id]'
DECLARE @CRLF AS varchar(2)
SET @CRLF = CHAR(13) + CHAR(10)
DECLARE @a_columnlist AS varchar(MAX)
DECLARE @b_columnlist AS varchar(MAX)
DECLARE @sql AS varchar(MAX)
SELECT @a_columnlist = COALESCE(@a_columnlist + @CRLF + ',', '') + 'a.[' + COLUMN_NAME + '] AS [' + @table1_prefix + COLUMN_NAME + ']'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table1_name
ORDER BY ORDINAL_POSITION
SELECT @b_columnlist = COALESCE(@b_columnlist + @CRLF + ',', '') + 'b.[' + COLUMN_NAME + '] AS [' + @table2_prefix + COLUMN_NAME + ']'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table2_name
ORDER BY ORDINAL_POSITION
SET @sql = 'SELECT ' + @a_columnlist + '
,' + @b_columnlist + '
FROM [' + @table1_name + '] AS a
INNER JOIN [' + @table2_name + '] AS b
ON (' + @join_condition + ')'
PRINT @sql
-- EXEC (@sql)
最近在NodeJS和Postgres中遇到了这个问题。
ES6方法
我知道没有任何RDBMS特性提供这种功能，所以我创建了一个包含我所有字段的对象，例如:
const schema = { columns: ['id','another_column','yet_another_column'] }
定义了一个reducer将字符串与表名连接在一起:
const prefix = (table, columns) => columns.reduce((previous, column) => {
previous.push(table + '.' + column + ' AS ' + table + '_' + column);
return previous;
}, []);
这将返回一个字符串数组。为每个表调用它并合并结果:
const columns_joined = [...prefix('tab1',schema.columns), ...prefix('tab2',schema.columns)];
输出最后的SQL语句:
console.log('SELECT ' + columns_joined.join(',') + ' FROM tab1, tab2 WHERE tab1.id = tab2.id');
从这个解决方案出发，我将如何处理这个问题:
首先创建一个所有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。
