HSQLDB查询用从另一个记录派生的值替换空值

This is a small excerpt from a much larger table, call it LOG:

这是一个更大的表的一个小摘录，称之为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

I would like a query that will replace the null in the 'TFAID' column with the value from the 'TFAID' column from the 'FID' column that matches.

我想要一个查询，它会将'TFAID'列中的null替换为匹配的'FID'列中'TFAID'列中的值。

Desired output would therefore be:

因此，期望的输出将是：

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

I know that something like

我知道类似的东西

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

is what I need, but I can't for the life of me come up with the clever bit of SQL that will fill in the proper TFAID.

是我需要的，但我不能为我的生活提出巧妙的SQL，将填写适当的TFAID。

2 个解决方案

#1

HSQLDB supports SQL features that can be used as alternatives. These features are not supported by some other databases.

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

#2

Here is one approach. This aggregates the log to get the value and then joins the result in:

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

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;

There may be other approaches that are more efficient. However, HSQL doesn't implement all SQL features.

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

#1