Mysql从2行中选择记录

Question

我有一个包含一些类似数据的表。这是问题/答案脚本的示例。 ID是自动递增的，如果PID=0则为问题，当回复任何问题时PID被设置为问题的ID。没有回复主题。

ID    PID    SUBJECT                CONTENT                   DATE
1      0     First Question         This is my first          09/01/2013
2      0     Second Question        This is second            09/01/2013
3      1                            Yes this is first         09/01/2013
4      2                            I agree this is second    10/01/2013
5      0     Third Question         This is third question    11/01/2013
6      1                            Reply to first            11/01/2013
7      1                            Another reply to first    12/01/2013
8      5                            This is reply of 5th      13/01/2013
9      2                            Last try for second       14/01/2013

我的问题是，

如何选择带有回复计数的问题？

Ex.
First Question (3)
Second Question (2)
Third Question (1)

如何选择今天已回答的问题或答案？

Ex. For 09/01/2013
First Question (2) ---> 1 question and 1 answer but 2 actions
Second Question (1) ---> just 1 question

Answer 1

选择问题并加入答案:

select q.id, q.subject, count(a.id)
from yourtable q
left join yourtable a on q.id=a.pid
where q.pid=0
group by q.id;