MySQL：后续出现的次数

Question

I have a table with four columns: id, from, to, msg.

The rows can look like this:

1 - foo - bar - hello
2 - foo - bar - zup?
3 - bar - foo - hi
4 - bar - foo - going okay
5 - bar - foo - you?

Now I wanna know how many times "bar" has tried to "talk" to "foo" without response. Like I wanna count the number of occurrences since "foo" was on the sending end.

In this example, before the next entry, it should return 3.

Is this possible in pure (My)SQL?

I'm using php on the server side.

Thanks for any tips and advice! =)

Answer 1

Give this a go.

It assumes a table name of convo and the id is autoincrementing.

This is just the mysql but the PHP calls can be added quite cleanly if you wrapped it in a function that passes in the from and to variables.

SELECT 
  count(*) AS unreplied
FROM
  `convo`
WHERE
  `convo`.`from` = 'bar' AND
  `convo`.`to` = 'foo' AND
  `convo`.`id` > (SELECT
                    id
                  FROM
                    `convo`
                  WHERE
                    `convo`.`from` = 'foo' AND
                    `convo`.`to` = 'bar'
                  ORDER BY
                    `convo`.`id` DESC
                  LIMIT 1)