mysql - 比较 MySQL 中不同表中的两列

Question

我有两个表

表格车辆

regno     make      status

KBT567K   ISUZU     operating
KAD897L   DOGDE     operating
KAT876K   JAGUAR    grounded
KAW564H   FERRARI   operating

表贡献

regno      amount    timestamp

KBT567K     200      2015-03-24 18:10:13 
KAD897L     100      2015-03-24 12:32:16
KBT567K     150      2015-03-25 11:06:32

我正在尝试提取一个查询，其中我只从未在 2015-03-25 上贡献的表格车辆中获取 regno 列表，而他们的 status 是 'operating'

我在下面尝试过这个查询，但没有用。任何帮助将不胜感激

select 
  regno
from
  vehicles
where
  regno not in (
    select
      contributions.regno
    from
      contributions,vehicles
    WHERE
      DATE_FORMAT(contributions.timestamp,'%Y-%m-%d') > '2015-03-25' AND 
      DATE_FORMAT(contributions.timestamp,'%Y-%m-%d') > '2015-03-24'
  ) AND
  vehicles.status = 'operating' ORDER BY vehicles.regno asc 

Answer 1

你可以试试这个SQL:

select DISTINCT regno 
from vehicles
where status = 'operating' AND regno in (select regno from contributions WHERE DATE_FORMAT(timestamp,'%Y-%m-%d') != '2015-03-25') 
ORDER BY regno asc