postgresql 我可以为多个列设置别名吗？如何？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11254413/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can I alias multiple columns? How?
提问by Ricky Moreno
I'm using pyodbc and postgres.
我正在使用 pyodbc 和 postgres。
Can I alias multiple columns?
我可以为多个列设置别名吗？
Here's the description of my problem:
这是我的问题的描述：
Data structure:
数据结构：
data
id | c1 | c2
-------------
1 | 11 | 12
2 | 21 | 22
Notation: c is for column
符号：c 代表列
dictionary
id | key | value
----------------
1 | k1 | v11
1 | k2 | v12
2 | k1 | v21
2 | k2 | v22
Notation: k is for key, v is for value
符号：k 为键，v 为值
You can think of k1 and k2 as two more columns. The data structure is this way because it's constantly changing. I didn't design it, I just have to go with it.
您可以将 k1 和 k2 视为另外两列。数据结构是这样的，因为它在不断变化。我没有设计它，我只需要随它去。
I can't figure out an sql query to give me something like the following (most importantly, for some row, I can access k1 and k2 columns by some name):
我无法找出一个 sql 查询来为我提供如下内容（最重要的是，对于某些行，我可以通过某个名称访问 k1 和 k2 列）：
data
id | c1 | c2 | k1 | k2
-------------------------
1 | 11 | 12 | v11 | v12
2 | 21 | 22 | v21 | v22
The problem I keep running into is if I alias the tables, then the sql result will contain two "key" columns from the dictionary table, meaning I can't control which column I access of the two, but if I alias the rows, then I can't control which tables are being referenced inside the sql statement.
我一直遇到的问题是，如果我为表设置别名，那么 sql 结果将包含字典表中的两个“关键”列，这意味着我无法控制我访问这两个列的哪一列，但是如果我为行设置别名，那么我无法控制在 sql 语句中引用了哪些表。
The fix I'm thinking is to alias two columns:
我正在考虑的解决方法是为两列添加别名：
SELECT * FROM data
FULL JOIN dictionary AS a1,a2,a3
ON data.id = a1
FULL JOIN dictionary AS a4,a5,a6
ON data.id = a4
WHERE a2 = k1 and a5 = k2
Notation: a is for alias
符号：a 是别名
The result of this would theoretically look like
理论上，这个结果看起来像
data
id | c1 | c2 | a3 | a6
-------------------------
1 | 11 | 12 | v11 | v12
2 | 21 | 22 | v21 | v22
Note all a's would technically be here, but 3 and 6 are the ones I'm interested in
请注意，技术上所有 a 都在这里，但 3 和 6 是我感兴趣的
采纳答案by Andomar
You can alias the entire table, for example dictionary as d1. Then refer to the column names in that table as d1.col1. For example:
您可以为整个表设置别名，例如dictionary as d1。然后将该表中的列名称称为d1.col1. 例如：
SELECT d.id
, d.c1
, d.c2
, d1.value as a3
, d2.value as a6
FROM data as d
LEFT JOIN
dictionary as d1
ON data.id = d1.id
and d1.key = 'k1'
LEFT JOIN
dictionary as d2
ON data.id = d2.id
and d2.key = 'k2'
