php – MYSQL错误的结果输出
|
我有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
我在http://sqlfiddle.com/#!2/29929e/1建立了一个计划,向您显示我实际获得的结果 谢谢 ：)
问题在于 – 在某些情况下,您只想在一个匹配的答案存在时显示两次问题.我引用：
这种重复使得事情变得非常困难. 我试图用UNION解决这个问题,似乎有效.但是,我还没有完全了解你的要求… 无论如何,我们去： select * from
(
select
q.id as q_id,a.id as a_id,q.asker_username,q.target_username,a.username,q.question,a.answer
from
questions q
left outer join answers a on q.id = a.id
where
q.asker_username <> 'mikha'
and
(
q.target_username = 'mikha'
or q.target_username in
(select username2 from connections where username1 = 'mikha')
or
(
q.target_username = 'every.one'
and
(
a.username = 'mikha'
or a.username in
(select username2 from connections where username1 = 'mikha')
or a.id is null
)
)
)
union
select
q.id as q_id,NULL as a_id,NULL,NULL
from
questions q
where
q.asker_username <> 'mikha'
and q.target_username = 'every.one'
and not exists (select id
from answers
where
id = q.id
and username = 'mikha'
)
) r
order by q_id;
测试它： （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |