Question

Is there a sql query that can generate the date when 2 things appear together?
I mean, let s say I have a table consists of bus schedule. Then, I have bus A and B. Bus A will operate on 22 May, 24 May, and 25 May while B operates on 22 May, 24 May and 26 May. I want to get the most recent date that 2 buses appear together which is 24 May.

Answer 1

To see those that both buses share:

  SELECT t.date
    FROM YOUR_TABLE t
   WHERE t.bus IN ( A ,  B )
GROUP BY t.date
  HAVING COUNT(DISTINCT t.bus) = 2

2. 看到最近两辆公共汽车共用的日期:

  SELECT t.date
    FROM YOUR_TABLE t
   WHERE t.bus IN ( A ,  B )
GROUP BY t.date
  HAVING COUNT(DISTINCT t.bus) = 2
ORDER BY t.date DESC
   LIMIT 1

Answer 2

假设你有一个名为公共汽车的桌子,其中包含一辆公共汽车——姓名和公共汽车——日野。

select bus_schedule_a.bus_date
from bus_schedule bus_schedule_a
inner join bus_schedule bus_schedule_b
on bus_schedule_a.bus_date = bus_schedule_b.bus_date
and bus_schedule_a.bus_name <> bus_schedule_b.bus_name
order by bus_schedule_a.bus_date desc
limit 1

友情链接