我有包含两个表的数据库Team和User。每个团队可以有一个或两个用户。我希望选择该表的输出，Team以便User包括该表中两个用户的信息。定义表结构后，它会更容易理解。
表Team：
+-----------+-------------------+-------------------+-----------------+
| team_name | user_one | user_two | team_note |
+-----------+-------------------+-------------------+-----------------+
| Team one | [email protected] | [email protected] | one to twenty |
| Team two | [email protected] | | three to thirty |
+-----------+-------------------+-------------------+-----------------+
表User：
+-------------------+-----------+-----------------+
| email | user_name | user_note |
+-------------------+-----------+-----------------+
| [email protected] | skuhsaone | gimme money |
| [email protected] | kgihse | drop it |
| [email protected] | dsjgknsz | just eat it |
+-------------------+-----------+-----------------+
我正在寻找的输出是这样的。
+-----------+--------------------+-----------+------------------+-----------+-----------------+
| team_name | user_one | user_name | user_two | user_name | team_note |
+-----------+--------------------+-----------+------------------+-----------+-----------------+
| Team one | [email protected] | skuhsaone | [email protected] | kgihse | one to twenty |
+-----------+--------------------+-----------+------------------+-----------+-----------------+
| Team two | [email protected] | dsjgknsz | | | three to thirty |
+-----------+--------------------+-----------+------------------+-----------+-----------------+
我感觉很容易做到，但是现在我正在尝试各种JOIN和东西，最后得到重复的结果或行。如果有任何PostgreSQL特定的方法可以做到这一点，那对我来说就很好。
SELECT * FROM Team LEFT JOIN User ON Team.user_one=User.email可以，但是如何仅选择所需的列？即，它将如何区分第一位和第二位用户的黑白列？
几个left joins应该可以解决这个问题：
SELECT team_name, user_one, u1.user_name, user_two, u2.user_name, team_note
FROM team t
LEFT JOIN user u1 ON t.user_one = u1.email
LEFT JOIN user u2 ON t.user_one = u2.email
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句