使用自连接的简单MySQL查询

Question

我有一张名为image的表格，它是由摄像机拍摄的一张表，它记录了可变区域下的汽车注册情况，它在可变摄像机下拍摄的摄像机号，以及yyyy dd 00:00:00 :00形式的时间戳。我被要求找到以下情况：

每一辆被摄像机捕捉到的车辆--显示登记，最早的时间，以及它离开区域的时间和摄像机。

因此，我正在寻找任何特定的汽车被摄像机19拍摄的最短时间，然后在那一天的最近一次，每辆车都和它拍摄的相机一起被捕获。到目前为止，我有以下代码：

SELECT early.reg,
   LEFT(MIN(early.whn), 10) AS date,
   RIGHT(MIN(early.whn), 8) AS 'in',
   RIGHT(MAX(late.whn), 8) AS 'out'
FROM image late
JOIN image early ON (early.reg = late.reg)
WHERE (early.camera = 19)
GROUP BY early.reg

这是非常好的工作，我只需要添加相机，最大的时间是在最大的时间是由右(最大(late.whn)，8)给出的‘输出’，我正在努力做到这一点。我尝试在SELECT调用中添加late.camera，但是很明显，您必须按late.camera添加GROUP，后者返回在每个摄像机上捕获的最新时间。任何帮助都很感激。

Answer 1

好吧，现在一个更好的理解和明确的解释，以确保我得到你想要的.我所得到的印象如下：

你在监控交通，比如收费公路。沿途有不同的摄像机，无论是向北/南或向东/向西。在任何给定的日期，您都有一个所有记录交易的列表，所有记录的事务由沿途的所有摄像机读取。你想知道，对于任何通过特定摄像头的汽车(我们不知道它的方向基础)，你想知道这辆车在那条路线上最终是从摄像机范围里跑出来的。例:摄像头1到30。你知道汽车可以接近19号摄像机，但是他们可能会在22，26，29摄像机之后离开马路。所以，对于那些在19号摄像机上看到的汽车，他们最后经过的相机在哪里。

如果这是正确的，我是接近的意图。内部查询几乎相同。对于给定的车辆登记ID，我仍然在存储它被发现的最小和最大日期(例如，可以在第4摄像机处到达道路，如果相机是顺序的，但不是必需的，则可以在相机27下关闭)。基于此的“拥有”子句要求，照相机19是旅行中包括的照相机之一。如果有人在第1摄像机上，在第18镜头下下车，他们就不会被包括在内(前提是相机确实是连续的，但更多的是为了跟随目的)。

所以现在我有所有的注册，最小和最大的日期/时间。现在，我重新加入到相同的图像表的注册和各自的最小或最大日期，因为它将只是一个记录的每一个注册，不需要分组在外部水平。对于给定的相机，您永远不会有重复的次数，并且它必须从PQ查询中存在。

现在，把各自的相机拿出来。因此，下面的查询实际上给出了他们第一次识别的相机，合格的是通过摄像头19的相机，以及它们最后被摄像机识别的位置。

SELECT
      PQ.Reg,
      LEFT(PQ.MinDateForDay, 10) AS date,
      RIGHT(PQ.MinDateForDay, 8) AS 'in',
      iMinDateCam.Camera CameraIn,
      PQ.TimeAtCamera19,
      RIGHT(PQ.MaxDateForDay, 8) AS 'out',
      iMaxDateCam.Camera CameraOut
   from
      ( SELECT 
              i.reg,
              min( i.whn ) as MinDateForDay,
              MAX( case when i.Camera = 19 then i.whn else '' end ) TimeAtCamera19,
              max( i.whn ) as MaxDateForDay
           FROM 
              image i
           GROUP BY 
              i.reg 
           having
              MAX( case when i.Camera = 19 then 1 else 0 end ) = 1 ) PQ
      join image iMinDateCam
         ON PQ.reg = iMinDateCam.reg
         AND PQ.MinDateForDay = iMinDateCam.whn
      join image iMaxDateCam
         ON PQ.reg = iMaxDateCam.reg
         AND PQ.MaxDateForDay = iMaxDateCam.whn

Answer 2

您已经接近完成了所需的额外groupby，只需添加一个where子句就可以将返回的late.whn限制为最大的

SELECT early.reg,
       LEFT(MIN(early.whn), 10) AS date,
       RIGHT(MIN(early.whn), 8) AS 'in',
       RIGHT(MAX(late.whn), 8) AS 'out',
    late.camera
    FROM image late
    JOIN image early ON (early.reg = late.reg)
    WHERE (early.camera = 19)
    and (late.whn = (select max(whn) from late))
    GROUP BY early.reg, late.camera