SQL IF条件来自其他表

I'm new here in the site and I need a help from you guys. Below is the schema i have which can be run in this site http://sqlfiddle.com/#!3/134c3. The name of my database is vehicle inspections. My question is after this schema.

我是新来的，我需要你们的帮助。下面是我的模式，可以在这个站点http://sqlfiddle.com/#!我的数据库的名字是车辆检查。我的问题在这个模式之后。

  CREATE TABLE Car  
  ([CarID] varchar(36), 
   [PlateNo] varchar(6),
   [Package] int);   

  INSERT INTO Car([CarID], [PlateNo], [Package])
  VALUES('A57D4151-BD49-4B44-AF10-000F1C298E05', '8112AG', 4);

  CREATE TABLE Event    
  ([EventID] int, 
   [CarID] varchar(36), 
   [EventTime] smalldatetime, 
   TicketStatus varchar (10)) ;    

 INSERT INTO Event([EventID], [CarID], [EventTime], TicketStatus) 
 VALUES (1, 'A57D4151-BD49-4B44-AF10-000F1C298E05', '20130701', 'Open'),  
        (2, 'A57D4151-BD49-4B44-AF10-000F1C298E05', '20130702', 'Close') ; 

 CREATE TABLE EventDefects
  ([EventDefectsID] int, 
   [EventID] int, 
   [Status] varchar(15), 
   [DefectID] int) ; 

 INSERT INTO EventDefects ([EventDefectsID], [EventID], [Status], [DefectID]) 
 VALUES (1, 1, 'YES', 1),
        (2, 1, 'NO', 2),
        (3, 1, 'N/A', 3),
        (4, 1, 'N/A', 4),
        (5, 2, 'N/A', 1),
        (6, 2, 'N/A', 2),
        (7, 2, 'N/A', 5),
        (8, 2, 'YES', 3),
        (9, 2, 'NO', 4) ;

 CREATE TABLE Defects
   ([DefectID] int, 
    [DefectsName] varchar (36), 
    [DefectClassID] int) ; 

 INSERT INTO Defects ([DefectID], [DefectsName], [DefectClassID]) 
 VALUES (1, 'TYRE', 1),
        (2, 'BRAKING SYSTEM', 1),
        (3, 'OVER SPEEDING', 3),
        (4, 'NOT WEARING SEATBELTS', 3),
        (5, 'MIRRORS AND WINDSCREEN', 2) ;

 CREATE TABLE DefectClass
    ([Description] varchar (15), 
     [DefectClassID] int) ; 

 INSERT INTO DefectClass ([DefectClassID], [Description]) 
 VALUES (1, 'CATEGORY A'),
        (2, 'CATEGORY B'),
        (3, 'CATEGORY C')

To clarify things. There are two conditions when we issue ticket to the driver.

澄清的事情。我们给司机开罚单有两个条件。

When vehicle is inspected and found defects on any items under Class A or B (tick 'yes'). The ticket status of that is OPEN. On the other hand if all items on Class A and B are tick 'No' it means no defects are found. The ticket Status is CLOSE. Lastly items under Class C or (traffic violations) are tick N/A. Meaning its a mere vehicle inspection

当车辆被检查并发现A级或B级以下的任何项目有缺陷时(打上“是”号)。票的状态是开放的。另一方面，如果A类和B类上的所有项目都是勾选“否”，则表示没有发现缺陷。机票状态接近。最后，C类或(违反交通规则的)项目的编号为N/A。这意味着这仅仅是一次车辆检查
Condition No. 2 is where vehicle is stopped because of traffic violation (ex. Over Speeding). Vehicle will NOT be inspected, The distinction of this issued ticket are all items under Class A and B are tick or mark 'N/A' while on Class C is tick either 'yes' or 'no'.

第二种情况是由于违反交通规则(如超速)而导致车辆停车。车辆将不会被检查，这张已发行的票的区别是A类和B类下的所有物品都是在C类上标记为“是”或“否”。

Now I have this SQL code below that can be use in the schema above where it will extract vehicles on its MAX(EventTime) with corresponding Ticket Status.

现在我有下面的SQL代码，可以在上面的模式中使用，它将在它的MAX(EventTime)中提取车辆，并拥有相应的票证状态。

  Select 
      PlateNo, TicketStatus, [EventTime] 
  FROM 
      (SELECT 
         ROW_NUMBER() OVER (PARTITION BY Event.CarID ORDER BY [EventTime] DESC) AS [index],
         Event.CarID, 
         TicketStatus, 
         [EventTime],
         plateNo
      FROM 
         [Event] 
      Join 
         [Car] ON Event.CarID = Car.CarID) A 
  WHERE [index] = 1

Result:

结果:

RESULT: PlateNo - 8112AG ; EventTime - July 2, 2013; TicketStatus - Close.

THIS IS NOT THE CORRECT since on this particular date there were no inspection at all only the driver was caught for OVER SPEEDING (see the schema above) and items under Class A and B are marked N/A.

这不是正确的，因为在这个特定的日期，根本没有检查，只有司机超速被抓住(见上图)，A类和B类的项目被标记为N/A。

The correct result should be one step back which is July 1, 2013 and Ticket Status is OPEN since it was a clear inspection. Items under category A and B are inspected and found TIRES are defective and BRAKING SYSTEM has NO defects.

正确的结果应该是后退一步，也就是2013年7月1日，由于验票是明确的，所以是开放的。检查A类和B类产品，发现轮胎有缺陷，制动系统无缺陷。

Somehow I was thinking code where if Event.TicketStatus = CLOSE it will examine if it is close because it was inspected or close because its a traffic violation.

不知何故，我在想如果事件的代码。TicketStatus = CLOSE它将检查它是否因为被检查而关闭，或者因为它违反了交通规则而关闭。

2 个解决方案

#1

Try this.

试试这个。

SELECT
  PlateNo,
  TicketStatus,
  MAX(EventTime)
FROM
  [Event] E
LEFT OUTER JOIN
  [EventDefects] ED ON E.EventID = ED.EventID
LEFT OUTER JOIN
  [Defects] D ON ED.DefectID = D.DefectID
LEFT OUTER JOIN 
  [Car] C ON E.CarID = C.CarID
WHERE ED.Status = 'YES' AND D.DefectClassID <> 3
GROUP BY PlateNo, TicketStatus

#2

I think you can solve this that way:

我认为你可以这样解决:

SELECT C.PlateNo, E.EventID, E.TicketStatus, E.EventTime 
 FROM Car C
 INNER JOIN Event E ON C.CarID = E.CarID
 INNER  JOIN (
SELECT CarID, MAX(E.EventTime) EventTime FROM Event E
  LEFT JOIN EventDefects ED ON E.EventID = ED.EventID
  LEFT JOIN Defects D ON ED.DefectID = D.DefectID
          WHERE D.DefectClassID IN (1,2) AND ED.Status <> 'N/A'
 GROUP BY CarID
 ) T ON E.CarID = T.CarID AND E.EventTime = T.EventTime

The subquery is filtering all events in class 1 and 2 (inspection) and where something happened (<> 'N/A'), and it's getting it's maximum date, so it will bring the last occurence of a real inspection of each car. Then, there's the join to bring the state on that date. From what I understood, that's what you want, right?

子查询在第1类和第2类(检查)中对所有事件进行过滤，并且在发生某些事情(<> 'N/A')时，它将获得最大的日期，因此它将使每辆车的最后一次真正的检查发生。然后，在那个日期将会有一个连接。根据我的理解，这就是你想要的，对吧?

#1