How to identify rows that have the same value in same columns in mysql? and also i have to add condition to those rows

Question

id  bid   status   time
1   c1   close   2019.10.11
2   c2   close   2019.12.12
3   c2   open    2019.12.11
4   c3   close   2019.12.14

here I want to capture both c2 s and I have to find the time difference

Answer 1

One method is join:

select to.*, tc.time as close_time,
       datediff(tc.time, to.time)
from t to join
     t tc
     on to.bid = tc.bid and
        to.status = 'open' and
        tc.status = 'close';