HSQLDB查询将零值替换为从另一个记录得出的值

Question

这是一个较大的表中的小摘录，称其为log：

RN EID FID FRID TID TFAID  
1 364   509 7045    null    7452  
2 364   509 7045    7452    null  
3 364   509 7045    7457    null  
4 375   512 4525    5442    5241  
5 375   513 4525    5863    5241  
6 375   515 4525    2542    5241  
7 576   621 5632    null    5452  
8 576   621 5632    2595    null  
9 672   622 5632    null    5966  
10 672  622 5632    2635    null  

我想要一个查询，将" tfaid"列中的null替换为匹配的" fid"列中的" tfaid"列中的值。

所需的输出将为：

RN EID FID FRID TID TFAID  
1 364   509 7045    null    7452  
2 364   509 7045    7452    7452  
3 364   509 7045    7457    7452  
4 375   512 4525    5442    5241  
5 375   513 4525    5863    5241  
6 375   515 4525    2542    5241  
7 576   621 5632    null    5452  
8 576   621 5632    2595    5452  
9 672   622 5632    null    5966  
10 672  622 5632    2635    5966 

我知道

之类的东西

    SELECT RN, 
        EID, 
        FID, 
        FRID, 
        TID, 
        (COALESCE TFAID, {insert clever code here}) AS TFAID
    FROM LOG

是我所需要的，但是我不能为自己的一生提出一个聪明的SQL，它将填充适当的tfaid。

Answer 1

HSQLDB支持可以用作替代方案的SQL功能。这些功能不受其他一些数据库的支持。

CREATE TABLE LOG (RN INT, EID INT, FID INT, FRID INT, TID INT, TFAID INT);
-- using LATERAL
SELECT l.RN, l.EID, l.FID, l.FRID, l.TID, 
   COALESCE(l.TFAID, f.TFAID) AS TFAID
   FROM LOG l , LATERAL (SELECT MAX(TFAID) AS TFAID FROM LOG f WHERE f.FID = l.FID) f
-- using scalar subquery
SELECT l.RN, l.EID, l.FID, l.FRID, l.TID, 
   COALESCE(l.TFAID, (SELECT MAX(TFAID) AS TFAID FROM LOG f WHERE f.FID = l.FID)) AS TFAID
   FROM LOG l

Answer 2

这是一种方法。这将汇总日志以获取值，然后加入结果：

SELECT l.RN, l.EID, l.FID, l.FRID, l.TID, 
       COALESCE(l.TFAID, f.TFAID) AS TFAID
FROM LOG l join
     (select fid, max(tfaid) as tfaid
      from log
      group by fid
     ) f
     on l.fid = f.fid;

可能还有其他更有效的方法。但是，HSQL并未实现所有SQL功能。