我有3个表包含以下数据:
第一个表称为连接,其中connections.username1是跟随者,connections.username2是被跟随的人.
它有以下行:
connections.username1 | connections.username2
mikha | guy
guy | maricela
maricela | guy
第二个表称为问题.它有一个列为asker称为questions.asker_username,另一个为接收问题称为questions.target_username的.当asker被称为“sys.tem”,目标称为“every.one”时,它被认为是一个全球性问题,可以由所有成员回答.
匿名用户可以询问并将其ip记录为asker_username.
它有以下行:
questions.id | questions.asker_username | questions.target_username | questions.question
1 | mikha | guy | what's your name?
2 | mikha | maricela | What's your age?
3 | guy | mikha | what's your name?
4 | maricela | guy | favorite food?
5 | xx.xx.xxx.xx | mikha | favorite pet?
6 | xx.xx.xxx.xx | guy | first name?
7 | xx.xx.xxx.xx | maricela | first name?
8 | sys.tem | every.one | what's ur name?
9 | sys.tem | every.one | favorite movie?
10 | sys.tem | every.one | favorite game?
第三个表被称为答案.答案表中的id与问题id相同.此表有一列用于id和username和answer的列.
answers.id | answers.username | answers.answer
1 | guy | my name is guy
2 | maricela | my name is maricela
3 | mikha | my name is mikha
4 | guy | pizza
8 | guy | guy is my name
8 | maricela | maricela is my name
9 | maricela | avatar
我想要一个结合以下与“mikha”有关的条件和他所遵循的人的一个查询:
1)questions.asker_username不是“mikha”
2)questions.target_username是“mikha”或他遵循的任何用户.
3)如果questions.target_username等于“every.one”并由“mikha”回答,则显示问题.
4)如果questions.target_username等于“every.one”,并且由“mikha”跟随的任何人回答,显示问题及其答案.如果“mikha”的用户没有回答,请不要显示问题.
5)如果questions.target_username等于“every.one”,任何一个都没有回答,请显示问题一次.
6)如果questions.target_username等于“every.one”,并没有被“mikha”回答,而没有接受任何人的回答,只显示一次问题.
我使用以下查询:
SELECT questions.id,answers.id,questions.asker_username,questions.target_username,
answers.username,questions.question,answers.answer
FROM questions
LEFT JOIN answers ON (questions.id = answers.id)
LEFT JOIN connections ON connections.username1 = 'mikha'
AND (questions.target_username = connections.username2
OR questions.asker_username = connections.username2
OR connections.username2 = answers.username)
WHERE questions.asker_username <> 'mikha'
AND (questions.target_username = 'mikha'
OR questions.target_username = connections.username2
OR (questions.target_username = 'every.one'
AND (answers.username = 'mikha'
OR answers.username = connections.username2
OR answers.username IS NULL)
)
)
GROUP BY questions.id,answers.username
我期待的结果:
questions.id | answers.id | questions.asker_username | questions.target_username | answers.username | questions.question | answers.answer
3 | 3 | guy | mikha | mikha | what's your name? | my name is mikha
4 | 4 | maricela | guy | guy | favorite food? | pizza
5 | 5 | xx.xx.xxx.xx | mikha | NULL | favorite pet? | NULL
6 | 6 | xx.xx.xxx.xx | guy | NULL | first name? | NULL
8 | 8 | sys.tem | every.one | NULL | what's ur name? | NULL
8 | 8 | sys.tem | every.one | guy | what's ur name? | guy is my name
9 | 9 | sys.tem | every.one | NULL | favorite movie? | NULL
10 | 10 | sys.tem | every.one | NULL | favorite game? | NULL
结果我实际得到:
questions.id | answers.id | questions.asker_username | questions.target_username | answers.username | questions.question | answers.answer
3 | 3 | guy | mikha | mikha | what's your name? | my name is mikha
4 | 4 | maricela | guy | guy | favorite food? | pizza
5 | 5 | xx.xx.xxx.xx | mikha | NULL | favorite pet? | NULL
6 | 6 | xx.xx.xxx.xx | guy | NULL | first name? | NULL
8 | 8 | sys.tem | every.one | guy | what's ur name? | guy is my name
10 | 10 | sys.tem | every.one | NULL | favorite game? | NULL
谢谢 :)