Firebird计算列实现自最近更新日起天数统计时的“多行单例查询”错误解决及优化（无需多计算列）

Let's tackle this problem directly. The root cause of your error is that your original subquery wasn't restricted to the current row of T1—it was returning results for all T1 rows at once, which violates the requirement that a computed column's subquery must be a singleton (return exactly one row and one column per row in the parent table).

Correct Single Computed Column SQL #

Here's the optimized SQL that achieves your requirement in a single computed column without errors:

ALTER TABLE T1 ADD DATEDIFF COMPUTED BY (
    DATEDIFF(DAY, 
        COALESCE(
            -- Get the latest T2DATE for the current T1 row only
            (SELECT MAX(T2DATE) FROM T2 WHERE T2.FK_T1ID = T1.T1ID),
            T1.T1DATE
        ),
        CURRENT_DATE
    )
)

Why This Works #

Let's break down the fix:

1. Row-specific subquery: The inner SELECT MAX(T2DATE) uses WHERE T2.FK_T1ID = T1.T1ID to only target T2 records linked to the current row in T1. This ensures the subquery returns exactly one value (either the max date for that T1ID, or NULL if no matches exist).
2. COALESCE logic: Falls back to T1.T1DATE if there are no matching T2 records, which aligns with your requirement.
3. Clean DATEDIFF calculation: Uses Firebird's DATEDIFF function to compute days between the selected date (from T2 or T1) and the current date. If both MAX(T2DATE) and T1.T1DATE are NULL, COALESCE returns NULL, so DATEDIFF also returns NULL as expected.

Verification Against Your Expected Results #

• T1ID=1: Subquery returns 07/03/21 (max T2DATE for FK_T1ID=1), DATEDIFF(DAY, '07/03/21', '10/03/21') = 3 ✔️
• T1ID=2: Subquery returns 07/03/21, DATEDIFF gives 3 ✔️
• T1ID=3: No T2 matches, so COALESCE uses 05/03/21, DATEDIFF gives 5 ✔️
• T1ID=4: Both MAX(T2DATE) and T1.T1DATE are NULL, so result is NULL ✔️

Why Your Original Query Failed #

Your initial query used a LEFT JOIN and GROUP BY across the entire T1 and T2 tables, which returns a row for every T1ID in the dataset—not just the current row being evaluated for the computed column. Firebird throws the "multiple rows in singleton select" error because it expects a single value per computed column instance, not a full result set. Even adding T1DATE to the GROUP BY didn't fix this, as the subquery still wasn't filtered to the current row.

Your temporary three-column workaround worked because each intermediate computed column's logic was scoped to the current row, but this single-column approach is cleaner and more efficient.

内容的提问来源于stack exchange，提问作者Dragos Dascalu