PostgreSQL视图WHERE子句AND/OR逻辑异常返回多行问题排查

报错的核心原因不是括号嵌套逻辑错误，而是SELECT列表中的标量子查询要求必须仅返回1行1列的结果：你在WHERE条件中用OR匹配了当年和上一年的记录，只要同一个organization同时存在这两个年份的calculated_workday数据，子查询就会返回2行，直接触发该报错。同时你原来的写法也没有实现「优先匹配当年数据，无数据再用上年」的逻辑，只要两年数据都存在就会同时命中。

方案1：排序取首行（推荐，适配需求最直接） #

通过按年份倒序排序取第一条的逻辑，天然实现优先取当年数据、当年无数据自动取上一年数据的需求，代码更简洁：

CREATE OR REPLACE VIEW "public"."TESTe" AS 
SELECT 
    a.id,
    ar.role_id,
    (
        SELECT (((a.duration * (a.frequency)::numeric) * (ar.percentage / (100)::numeric)) / (cw.workdays)::numeric) AS workdays
        FROM calculated_workday cw
        WHERE 
            cw.organization = (SELECT f.org_id FROM functions f WHERE f.id = a.function)
            AND cw.year IN (date_part('year', now())::int, date_part('year', now())::int - 1)
        ORDER BY cw.year DESC
        LIMIT 1
    ) AS total_duration
FROM activities a
INNER JOIN activity_roles ar ON a.id = ar.activity_id;

方案2：COALESCE分两次查询（逻辑更直观） #

如果需要更明确的分层查询逻辑，可以用COALESCE分别查询当年、上年数据，当年查询返回NULL时自动走上年查询：

CREATE OR REPLACE VIEW "public"."TESTe" AS 
SELECT 
    a.id,
    ar.role_id,
    COALESCE(
        (
            SELECT (((a.duration * (a.frequency)::numeric) * (ar.percentage / (100)::numeric)) / (cw.workdays)::numeric)
            FROM calculated_workday cw
            WHERE 
                cw.organization = (SELECT f.org_id FROM functions f WHERE f.id = a.function)
                AND cw.year = date_part('year', now())::int
        ),
        (
            SELECT (((a.duration * (a.frequency)::numeric) * (ar.percentage / (100)::numeric)) / (cw.workdays)::numeric)
            FROM calculated_workday cw
            WHERE 
                cw.organization = (SELECT f.org_id FROM functions f WHERE f.id = a.function)
                AND cw.year = date_part('year', now())::int - 1
        )
    ) AS total_duration
FROM activities a
INNER JOIN activity_roles ar ON a.id = ar.activity_id;

• 把functions表的查询提前到外层JOIN，避免每行SELECT都重复执行子查询，性能更好：可以在外层加LEFT JOIN functions f ON f.id = a.function，后续直接引用f.org_id即可
• 如果同一个年份下同一个organization可能存在多条calculated_workday记录，需要额外增加聚合逻辑（比如取MAX(cw.workdays)）或者补充筛选条件，避免LIMIT返回非预期的行

内容的提问来源于stack exchange，提问作者tj-