在包含共享值的行之间具有MAX值的计算表列 [英] Computed table column with MAX value between rows containing a shared value

问题描述

我有下表

CREATE TABLE T2
( ID_T2 integer NOT NULL PRIMARY KEY,
  FK_T1 integer,                    <--- foreign key to T1(Table1)
  FK_DATE date,                     <--- foreign key to T1(Table1)
  T2_DATE date,                     <--- user input field
  T2_MAX_DIFF COMPUTED BY ( (SELECT DATEDIFF (day, MAX(T2_DATE), CURRENT_DATE) FROM T2 GROUP BY FK_T1) )
);

我希望T2_MAX_DIFF显示自具有相同FK_T1的所有相似条目的上次输入以来的天数.

I want T2_MAX_DIFF to display the number of days since last input across all similar entries with a common FK_T1.

它确实有效，但是如果将另一个FK_T1值添加到表中，则会出现关于单例中的多行选择"的错误..

It does work, but if another FK_T1 values is added to the table, I'm getting an error about "multiple rows in singleton select".

我假设我需要某种 WHERE FK_T1 =对应行的FK_T1 .可以添加这个吗?我正在将Firebird 3.0.7与Flamerobin结合使用.

I'm assuming that I need some sort of WHERE FK_T1 = FK_T1 of corresponding row. Is it possible to add this? I'm using Firebird 3.0.7 with flamerobin.

推荐答案

错误单例中的多行选择" 表示应提供单个标量值的查询会产生多行.对于使用 GROUP BY FK_T1 进行的查询，这并不意外，因为它将为每个 FK_T1 值生成一行.

The error "multiple rows in singleton select" means that a query that should provide a single scalar value produced multiple rows. And that is not unexpected for a query with GROUP BY FK_T1, as it will produce a row per FK_T1 value.

要解决此问题，您需要通过执行以下操作来使用相关子查询:

To fix this, you need to use a correlated sub-query by doing the following:

1. 将子查询中的表别名化，以使其与表本身保持歧义
2. 添加一个where子句，确保使用别名表(例如 src 和 src.FK_T1 )，并显式引用表本身以用于另一侧比较(例如 T2.FK_T1 )
3. (可选)删除 GROUP BY 子句，因为在给出 WHERE 子句的情况下并不必要.但是，将 GROUP BY 保留在原位可能会发现某些类型的错误.

1. Alias the table in the subquery to disambiguate it from the table itself
2. Add a where clause, making sure to use the aliased table (e.g. src, and src.FK_T1), and explicitly reference the table itself for the other side of the comparison (e.g. T2.FK_T1)
3. (optional) remove the GROUP BY clause because it is not necessary given the WHERE clause. However, leaving the GROUP BY in place may uncover certain types of errors.

结果子查询变为:

(SELECT DATEDIFF (day, MAX(src.T2_DATE), CURRENT_DATE) 
 FROM T2 src 
 WHERE src.FK_T1 = T2.FK_T1 
 GROUP BY src.FK_T1)

请注意子查询中引用的表的别名 src ，条件中使用 src.FK_T1 以及在中显式使用表T2.FK_T1 引用表本身当前行的列.如果您使用 src.FK_T1 = FK_T1 ，它将与 src 的 FK_T1 列进行比较(就像您使用过> src.FK_T1 = src.FK_T2 )，因此始终为真.

Notice the alias src for the table referenced in the subquery, the use of src.FK_T1 in the condition, and the explicit use of the table in T2.FK_T1 to reference the column of the current row of the table itself. If you'd use src.FK_T1 = FK_T1, it would compare with the FK_T1 column of src (as if you'd used src.FK_T1 = src.FK_T2), so that would always be true.

CREATE TABLE T2
( ID_T2 integer NOT NULL PRIMARY KEY,
  FK_T1 integer, 
  FK_DATE date,
  T2_DATE date,
  T2_MAX_DIFF COMPUTED BY ( (
    SELECT DATEDIFF (day, MAX(src.T2_DATE), CURRENT_DATE) 
    FROM T2 src 
    WHERE src.FK_T1 = T2.FK_T1 
    GROUP BY src.FK_T1) )
);

这篇关于在包含共享值的行之间具有MAX值的计算表列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！