MySQL SQL SELECT 名称按 id
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27696238/
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
SQL SELECT name by id
提问by magl1te
I need help with a sql query.
我需要有关 sql 查询的帮助。
I have these 2 tables:
我有这两张表：
player_locations:
player_locations:
ID | playerid | location <- unqiue key
---|-----------------------
1 | 1 | DOWNTOWN
and users:
和 users:
ID | playername | [..]
----|--------------------
1 | example1 | ...
I need a select to get the users.playernamefrom the player_locations.playerid. I have the unique location to get the player_locations.playerid.
我需要一个选择来users.playername从player_locations.playerid. 我有独特的位置来获取player_locations.playerid.
Pseudo query:
伪查询：
SELECT playername
FROM users
WHERE id = player_locations.playerid
AND player_locations.location = "DOWNTOWN";
The output should be example1.
输出应该是example1.
回答by AdamMc331
This is just a simple INNER JOIN. The general syntax for a JOINis:
这只是一个简单的INNER JOIN. JOIN的一般语法是：
SELECT stuff
FROM table1
JOIN table2 ON table1.relatedColumn = table2.relatedColumn
In your case, you can relate the two tables using the idcolumn from users and playeridcolumn from player_locations. You can also include your 'DOWNTOWN'requirement in the JOINstatement. Try this:
在您的情况下，您可以使用id来自 users的列和playerid来自的列关联这两个表player_locations。您还可以'DOWNTOWN'在JOIN声明中包含您的要求。尝试这个：
SELECT u.playername
FROM users u
JOIN player_locations pl ON pl.playerid = u.id AND pl.location = 'DOWNTOWN';
EDIT
编辑
While I personally prefer the above syntax, I would like you to be aware of another way to write this which is similar to what you have now.
虽然我个人更喜欢上面的语法，但我希望你知道另一种写法，它类似于你现在所拥有的。
You can also select from multiple tables by using a comma in your FROMclause to separate them. Then, in your WHEREclause you can insert your conditions:
您还可以通过在FROM子句中使用逗号将它们分开来从多个表中进行选择。然后，在您的WHERE条款中，您可以插入您的条件：
SELECT u.playername
FROM users u, player_locations pl
WHERE u.id = pl.playerid AND pl.location = 'DOWNTOWN';
回答by jewelhuq
Here is the solution.
这是解决方案。
SELECT
playername
FROM users
WHERE id = (SELECT id FROM player_locations WHERE location='DOWNTOWN')
回答by Gally
I have a idea, try this:
我有个主意，试试这个：
SELECT playername
FROM users
WHERE id IN (SELECT DISTINCT playerid FROM player_location WHERE location LIKE "DOWNTOWN");
