在VIEW的内部SELECT查询中，WHERE约束如何传递给count(..)函数?

Question

我在SQLite中有一个VIEW，大概是这样的:

SELECT ownerid, field, origin, count FROM (
    SELECT 
        null AS ownerid,
        table1.somefield AS field,
        0 AS origin,
        count(table2.ownerid) AS count
    FROM table1 LEFT JOIN table2
    UNION ALL
    SELECT 
        table2.ownerid AS ownerid,
        table2.someotherfield AS field,
        1 AS origin,
        1 AS count
)

我用SELECT FROM viewname WHERE ownerid=? OR origin=0类型查询调用它——换句话说，我想要的是第一个表中的条目，加上第二个表中所有者id正确的条目。

第一个表的count列应该是第二个表中匹配约束的所有行的计数——换句话说，其所有者id是选中的那个。我目前得到的是整个表的计数。

我如何约束count(...)函数调用中的表达式以匹配传递给VIEW的查询约束?

Answer 1

count(SELECT * FROM table2 WHERE ownerid=?)

我认为你真正需要的是简化你的视图，并像这样限制信息。

SELECT ownerid, field, origin, count FROM (
    SELECT 
        null AS ownerid,
        table1.somefield AS field,
        0 AS origin
    FROM table1 LEFT JOIN table2
    UNION ALL
    SELECT 
        table2.ownerid AS ownerid,
        table2.someotherfield AS field,
        1 AS origin
)

，然后从中获取所需的计数等，像这样

SELECT *, CASE WHEN viewname.ownerid IS NULL THEN COUNT(select * from table2 where ownerid=?) ELSE 1 END
FROM viewname