我想写一个SQL嵌套查询，发现/使用最大值来查找最大值之前的条目

Question

我对SQL相当新，并且正在尝试编写一个查询，查找最后一次水表是阅读，所以我可以看到价值。有一个属性表包含仪表和另一个表，用于存储工程师的输入。每个输入被列为一个序列，一个关键字列出了输入类型和表达式列出了它们的输入。最大顺序并不总是答案。

我在找的是最后一次读取水表的电量，然后从前一个记录（序列）中存储的读数中获得电量值。为了让工程师输入序列号更难，有些人需要输入序列号（1,2,3），其他人需要输入2（2,4,6），因此前面的输入可能会减1或减2。

我可以编写查询来查找最大序列，另一个查找前一个或两个前一个条目，但无法弄清楚如何使其成为一个查询。

找到网站12345的最大序列，我有：

SELECT MAX(M.SEQUENCE) maxseq 
FROM METERS M JOIN PROPERTY P ON M.PROPNUM = P.PROPNUM 
WHERE (P.CORP_ID ='12345' AND M.KEYWORD = 'WTR') 

我手动搜索条目之前得到用下面的查询电力条目。

SELECT P.NAME, P.CORP_ID, M.KEYWORD, M.SEQUENCE, M.EXPRESSION 
FROM METERS M JOIN PROPERTY P ON M.PROPNUM = P.PROPNUM 
WHERE (P.CORP_ID ='12345') 
ORDER BY M.SEQUENCE 

我试过不同的嵌套查询，但一直没有能够写任何将工作。

，我很感兴趣的米表如下所示的数据：

PROPNUM SEQUENCE KEYWORD EXPRESSION 
10a124 95  ELC  9845 
10a124 96  WTR  4521 
10a124 97  SVC  A105 
10a124 98  HEALTH GOOD 
10a124 99  DAY  150209 
10a124 100  HEALTH GOOD 
10a124 101  ELC  10283 
10a124 102  WTR  4621 

我使用属性表找到我感兴趣的部位PROPNUM因为我有该网站的ID（CORP_ID），但而不是它的PROPNUM值。

我想返回的结果如下所示。

 
NAME WTR_EXPRESSION ELC_EXPRESSION 
SMITH 4621    10283 

Answer 1

可以inner join的METER表到PROPERTY表一次为每个KEYWORD，并指定为SEQUENCE'ELC'（猜测KEYWORD）小于'WTR' SEQUENCE。如果你想做到这一点的

;with wtr_elc as (
    select 
     p.PROPNUM, 
     p.NAME, 
     max(w.SEQUENCE) as max_wtr_seq, 
     max(e.SEQUENCE) as max_elc_seq 
    from PROPERTY as p 
     inner join METERS as w 
      on w.PROPNUM = p.PROPNUM 
      w.KEYWORD = 'WTR' 
     inner join METERS as e 
      on e.PROPNUM = p.PROPNUM 
      and e.KEYWORD = 'ELC' 
      and e.SEQUENCE < w.SEQUENCE 
    where p.CORP_ID ='12345' 
    group by 
     p.PROPNUM, 
     p.NAME) 

select 
    wtr_elc.NAME, 
    wtr.EXPRESSION as WTR_EXPRESSION, 
    elc.EXPRESSION as ELC_EXPRESSION 
from METERS as wtr 
    inner join wtr_elc 
     on wtr_elc.PROPNUM = wtr.PROPNUM 
     and wtr_elc.max_wtr_seq = wtr.SEQUENCE 
    inner join METERS elc 
     on wtr_elc.PROPNUM = elc.PROPNUM 
     and wtr_elc.max_elc_seq = elc.SEQUENCE 
     and elc.KEYWORD = 'ELC' 
where wtr.KEYWORD = 'WTR' 

：由于您使用的SQL服务器，我们可以做到这一点在CTE和inner join数据设置为METER表中单列显示每个KEYWORD的EXPRESSION值更多或全部PROPERTY记录，您可以修改CTE中的where子句。