多个MySQL表JOINs / id到值替换/别名 [英] Multiple MySQL Table JOINs / id-to-value replacement / aliases
问题描述
我有两个表，我想从中创建一个选择，使得第一个表中的引用ID被第二个表中的值替换。优选对大型数据库的优化查询。以下说明了我的目标：
TABLE_KINGDOMS
============== b $ b kid kingdom id_king id_queen id_prince id_princess
- ------- ------- -------- --------- ---- -------
1 red 1 2 3 4
2 blue 5 6 7 8
...
TABLE_PLAYERS
=== ==========
pid用户名点gold
- -------- ------ ----
1 Jack 34789 35667
2 Jill 2312 23887
3 Walt 8756 23112
4 Winnie 587 255
5 Eric 76521 34678
6 Alice 6799 7549
7 Ned 5565 9009
8 Rose 2312 3429
...
DESIRED_SELECTION（用用户名替换ID）
=================
kid王国王后王子王子公主
- ------- ---- ----- ------ --------
1 red Jack Jill Walt Winnie
2 blue Eric Alice Ned Rose
...
我试过下面的，但它不工作：
SELECT *
FROM table_kingdoms
LEFT JOIN table_users ON id_king = tu.id
LEFT JOIN table_users ON id_queen = tu.id
LEFT JOIN table_users ON id_prince = tu.id
LEFT JOIN table_users ON id_princess = tu.id
谢谢！
我用 INNER 替换了 LEFT JOINS ，因为table_kingdoms中的所有ID都不是null， LEFT 返回如果id可以为null，否则 INNER 更好。您的查询列和表名称与您的结构中的不同，因此我不确定要使用哪个。因此，如果这不会得到你需要的，指定你的意思是不工作，我们可以解决具体的问题。
SELECT K.kid，K.Kingdom，P.Username as King，PQ.Username as Queen，
PP 。用户名为Prince，PS.Username为公主
FROM table_kingdoms K
INNER JOIN TABLE_PLAYERS PK ON K.id_king = PK.pid - 根据实际表名，使用table_users替换TABLE_PLAYERS
- -LEFT JOIN table_users ON id_king = tu.id REPLACE WITH ABOVE
INNER JOIN TABLE_PLAYERS PQ ON K.id_queen = PQ.pid - 根据实际表名，使用table_users替换TABLE_PLAYERS
--LEFT JOIN table_users ON id_queen = tu.id REPLACE WITH ABOVE
INNER JOIN TABLE_PLAYERS PP ON K.id_prince = PP.pid - 根据实际表名，使用table_users替换TABLE_PLAYERS
--LEFT JOIN table_users ON id_prince = tu .id REPLACE WITH ABOVE
INNER JOIN TABLE_PLAYERS PS ON K.id_princess = PS.pid --replace TABLE_PLAYERS with table_users取决于实际的表名
--LEFT JOIN table_users ON id_princess = tu.id REPLACE WITH ABOVE
就原始查询而言，它非常接近。最大的事情是你说tu.id，但从来没有声明为别名，你不能重复使用相同的别名。此外，你必须按照你想要的方式定义列，但这不是一个错误。这里是我上面提到的事情的编辑是实际错误：
SELECT *
FROM table_kingdoms
LEFT JOIN table_users tu ON id_king = tu.id
LEFT JOIN table_users tu2 ON id_queen = tu2.id
LEFT JOIN table_users tu3 ON id_prince = tu3.id
LEFT JOIN table_users tu4 ON id_princess = tu4.id
并且作为注释，我个人也别名table_kingdoms，这里也是包括列显示：
SELECT K.kid，K.Kingdom，tu.Username as King，tu2.Username as Queen，
tu3.Username为Prince，tu4.Username为Princess
FROM table_kingdoms k
LEFT JOIN table_users tu ON k.id_king = tu.id
LEFT JOIN table_users tu2 ON k.id_queen = tu2.id
LEFT JOIN table_users tu3 ON k.id_prince = tu3.id
LEFT JOIN table_users tu4 ON k.id_princess = tu4.id
I have two tables which I want to create a selection from, such that the reference ids in the first table are replaced by the values in the second table. An optimized query for a huge database is preferred. The following illustrates my goal:
TABLE_KINGDOMS
==============
kid kingdom id_king id_queen id_prince id_princess
-- ------- ------- -------- --------- -----------
1 red 1 2 3 4
2 blue 5 6 7 8
...
TABLE_PLAYERS
=============
pid username points gold
-- -------- ------ ----
1 Jack 34789 35667
2 Jill 2312 23887
3 Walt 8756 23112
4 Winnie 587 255
5 Eric 76521 34678
6 Alice 6799 7549
7 Ned 5565 9009
8 Rose 2312 3429
...
DESIRED_SELECTION (Replace id with username)
=================
kid kingdom king queen prince princess
-- ------- ---- ----- ------ --------
1 red Jack Jill Walt Winnie
2 blue Eric Alice Ned Rose
...
I have tried the following, but it isn't working:
SELECT *
FROM table_kingdoms
LEFT JOIN table_users ON id_king = tu.id
LEFT JOIN table_users ON id_queen = tu.id
LEFT JOIN table_users ON id_prince = tu.id
LEFT JOIN table_users ON id_princess = tu.id
Thanks!
I replaced you LEFT JOINS with INNER, as none of your IDs in table_kingdoms are nulls, put the LEFT back if the ids can be null, otherwise INNER is better. Your query column and table names are different than what is in your structures so I wasn't sure which to use. So if this doesn't get what you need, specify what you mean by doesn't work and we can address the specific issue.
SELECT K.kid,K.Kingdom, P.Username as King,PQ.Username as Queen,
PP.Username as Prince,PS.Username as Princess
FROM table_kingdoms K
INNER JOIN TABLE_PLAYERS PK ON K.id_king = PK.pid --replace TABLE_PLAYERS with table_users depending on the actual table name
--LEFT JOIN table_users ON id_king = tu.id REPLACE WITH ABOVE
INNER JOIN TABLE_PLAYERS PQ ON K.id_queen = PQ.pid --replace TABLE_PLAYERS with table_users depending on the actual table name
--LEFT JOIN table_users ON id_queen = tu.id REPLACE WITH ABOVE
INNER JOIN TABLE_PLAYERS PP ON K.id_prince = PP.pid --replace TABLE_PLAYERS with table_users depending on the actual table name
--LEFT JOIN table_users ON id_prince = tu.id REPLACE WITH ABOVE
INNER JOIN TABLE_PLAYERS PS ON K.id_princess = PS.pid --replace TABLE_PLAYERS with table_users depending on the actual table name
--LEFT JOIN table_users ON id_princess = tu.id REPLACE WITH ABOVE
As far as your original query goes,it is pretty close. The biggest thing is you are saying tu.id but never declared that as an alias AND you can't reuse the same alias Then also, you have to define the columns the way you want them, but this is not an error. Here is an edit for the things I mentioned above that are actual errors:
SELECT *
FROM table_kingdoms
LEFT JOIN table_users tu ON id_king = tu.id
LEFT JOIN table_users tu2 ON id_queen = tu2.id
LEFT JOIN table_users tu3 ON id_prince = tu3.id
LEFT JOIN table_users tu4 ON id_princess = tu4.id
And just as a note, I'd personally also alias table_kingdoms and here that is also including column display:
SELECT K.kid,K.Kingdom, tu.Username as King, tu2.Username as Queen,
tu3.Username as Prince,tu4.Username as Princess
FROM table_kingdoms k
LEFT JOIN table_users tu ON k.id_king = tu.id
LEFT JOIN table_users tu2 ON k.id_queen = tu2.id
LEFT JOIN table_users tu3 ON k.id_prince = tu3.id
LEFT JOIN table_users tu4 ON k.id_princess = tu4.id
这篇关于多个MySQL表JOINs / id到值替换/别名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
