我想知道这在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 *操作。
最近在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');
对此没有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');
PHP 7.2 + MySQL/Mariadb
MySQL会给你发送多个相同名称的字段。甚至在终端客户端。但如果你想要一个关联数组，你必须自己创建键。
感谢@axelbrz的原创。我已经将它移植到更新的php，并对它进行了一些清理:
function mysqli_rows_with_columns($link, $query) {
$result = mysqli_query($link, $query);
if (!$result) {
return mysqli_error($link);
}
$field_count = mysqli_num_fields($result);
$fields = array();
for ($i = 0; $i < $field_count; $i++) {
$field = mysqli_fetch_field_direct($result, $i);
$fields[] = $field->table . '.' . $field->name; # changed by AS
#$fields[] = $field->orgtable . '.' . $field->orgname; # actual table/field names
}
$rows = array();
while ($row = mysqli_fetch_row($result)) {
$new_row = array();
for ($i = 0; $i < $field_count; $i++) {
$new_row[$fields[$i]] = $row[$i];
}
$rows[] = $new_row;
}
mysqli_free_result($result);
return $rows;
}
$link = mysqli_connect('localhost', 'fixme', 'fixme', 'fixme');
print_r(mysqli_rows_with_columns($link, 'select foo.*, bar.* from foo, bar'));
