MySQL 自引用 ID 和选择 [英] MySQL self-referencing ID and selects

问题描述

我在这里有一个 MySQL 表 Starcraft2brackets:

I have a MySQL table here Starcraft2brackets:

http://i.stack.imgur.com/welPq.png(重新编辑所以我可以再次编辑，对不起第一个编辑)

http://i.stack.imgur.com/welPq.png (re-edited so I could edit again, sorry first editor)

uId => 对用户的引用

uId => reference to a user

id => 索引

opponent => 引用此表中的 id，-2 视为再见.

opponent => reference to id in this table, -2 is considered a bye.

我想做的是提出一个 sql 语句，该语句将根据行的 uId 选择一个用户名，以及选择对手的用户名.迄今为止我想出的最具建设性的一个是

What I am trying to do, is come up with a sql statement that will select a username based upon the uId of a row, as well as select the username of their opponent. The most constructive one I have come up with so far was

SELECT u1.username, if(sc2a.opponent = -2, "Bye", u2.username) as username
FROM Starcraft2brackets AS sc2a
LEFT JOIN users AS u1 ON u1.id = sc2a.uId
LEFT JOIN Starcraft2brackets AS sc2b ON sc2a.opponent = sc2b.id
LEFT JOIN users AS u2 ON sc2b.uId = u2.id

这确实给了我正确的用户名，但是它给出了重复的条目，就像这样..

This did give me the correct usernames, however it gave duplicate entries, like so..

http://i.imgur.com/znbAi.png

SELECT u1.username, IF( sc2a.opponent = -2,  "Bye", u2.username ) AS username
FROM Starcraft2brackets AS sc2a
LEFT JOIN users AS u1 ON u1.id = sc2a.uId
LEFT JOIN Starcraft2brackets AS sc2b ON sc2a.opponent = sc2b.id
LEFT JOIN users AS u2 ON sc2b.uId = u2.id
WHERE u1.username < u2.username
OR sc2a.opponent = -2

这给了我我正在寻找的结果集.感谢 stackoverflow.com 的帮助.

This gave me the result set I was looking for exactly. Thanks for the help stackoverflow.com.

推荐答案

试试这个:

SELECT u1.username, if(u1.oppponent = -2, "Bye", u2.username) as username
FROM users AS u1
left join users AS u2 on u1.opponent = u2.id
join Starcraft2brackets AS sc2a on u1.id = sc2a.uId
where u1.uid = ?;

这篇关于MySQL 自引用 ID 和选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！