是否可以重写以下视图,以便用户可以使用WHERE
子句(WHERE overlap > @param
,在下面注释)参数化视图?有可能吗?where
子句应在group by
子句之前生效。
我尝试从表vl
中进行选择,并将行(外部联接)与语句中的行进行匹配,该语句在另一个表(vlda
)中计数。
CREATE VIEW dr AS
SELECT
vl.unique_object_id AS unique_object_id,
ifnull(vlda.detectioncount, 0) AS detectioncount,
vl.count AS labelcount,
(1.0*ifnull(vlda.detectioncount, 0) / vl.count) AS detectionrate,
vlda.evalround_id
FROM viewlabelcount vl
LEFT OUTER JOIN
(
SELECT
count() as detectioncount,
unique_object_id,
evalround_id
FROM viewlabeldetections_best
-- WHERE overlap > 0.5 -- is it possible to parametrize?
GROUP BY unique_object_id
) AS vlda
ON
vl.unique_object_id = vlda.unique_object_id
SQLite不允许在模式中存储的对象(如视图或触发器)中使用参数。
如果您使用的是SQLite 3.8.3或更高版本,您可以将该定义作为通用表表达式移动到查询中。