在字段中特定日期类型的最近一次之后发生的SQL显示记录。

Question

我有一套关于面包车的记录(序列号、作业号、作业类型和当前里程)。

select serial_number, call_ref, call_type_description, callm_data19
from calls
left join equipment on link_to_equipment=equipment_code
inner join lu_call_types on call_type=call_type_code
left join call_more on call_ref=callm_link_to_call
where serial_number = 'SH12ZLN'
order by call_ref desc

我想要能够只显示下一个“货车检查”记录，发生在最近的“面包车服务”记录。

以下是数据的快照

SH12ZLN 3023152 Van Inspection  83980
SH12ZLN 3019319 Van Inspection  83046
SH12ZLN 3016999 Van Servicing   NULL
SH12ZLN 3016346 Van Inspection  81818
SH12ZLN 3012977 Van Inspection  80742
SH12ZLN 3010435 Van Inspection  79909
SH12ZLN 3008528 Van Repairs NULL
SH12ZLN 3006880 Van Inspection  78577
SH12ZLN 3001942 Van Inspection  76974
SH12ZLN 2998209 Van Inspection  75976
SH12ZLN 2994475 Van Inspection  75285
SH12ZLN 2991756 Van Repairs NULL
SH12ZLN 2989642 Van Inspection  74408
SH12ZLN 2985795 Van Inspection  73642
SH12ZLN 2981952 Van Inspection  72838
SH12ZLN 2978257 Van Inspection  72011
SH12ZLN 2975667 Van Inspection  70692
SH12ZLN 2972244 Van Inspection  69732
SH12ZLN 2969157 Van Inspection  68821
SH12ZLN 2959335 Van Inspection  67891
SH12ZLN 2956295 Van Inspection  66994
SH12ZLN 2948516 Van Inspection  66481
SH12ZLN 2946213 Van Inspection  65778
SH12ZLN 2939497 Van Inspection  64408
SH12ZLN 2937538 Van Inspection  63765
SH12ZLN 2934421 Van Inspection  62937
SH12ZLN 2932707 Van Inspection  61645
SH12ZLN 2930711 Van Inspection  60713
SH12ZLN 2930023 Van Inspection  59683
SH12ZLN 2924989 Van Inspection  58372
SH12ZLN 2924830 Van Repairs NULL
SH12ZLN 2922412 Van Inspection  57474
SH12ZLN 2919005 Van Servicing   NULL
SH12ZLN 2918376 Van Repairs NULL

为了澄清-我希望它只产生以下记录

SH12ZLN 3019319 Van Inspection  83046

Answer 1

问题澄清后，我编辑了我的答案。要解决这个问题，您应该找到'Van‘类型中最高的job_number，这意味着最近的一个。您可以使用子查询：

SELECT MAX(job_number) FROM tbl WHERE id = 'SH12ZLN' AND type = 'Van Servicing'

有了最新的服务之后，job_number只需过滤job_number高于最近找到的行，并选择job_number最低的行。

最后的查询可能如下所示：

SELECT TOP 1 * FROM tbl WHERE 
job_number > (SELECT MAX(job_number) FROM tbl WHERE id = 'SH12ZLN' 
    AND type = 'Van Servicing')
AND type = 'Van Inspection'
ORDER BY job_number ASC

查看sqlfiddle来尝试一下：http://sqlfiddle.com/#!3/64dfc/2

从初始问题开始，您的样本数据是从查询本身接收的。您的查询可以增强以返回所需的内容，但我需要所有表的架构。