第一个表称为连接,其中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建立了一个计划,向您显示我实际获得的结果
谢谢 ：)
So,i want to show it only once for “mikha” whether it’s answered or not and show it again each time it’s answered by any of the people “mikha” follows
这种重复使得事情变得非常困难.
我试图用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;
测试它：
With answer from mikha for question 8
Without answer from mikha for question 8
