如何在WHERE子句(MySQL)中使用VIEW

Question

我想在WHERE子句中使用视图的数据。但是得到一个错误：

create view post_with_answers AS
SELECT DISTINCT postid 
FROM (SELECT postid FROM `qa_posts` WHERE `type` = 'Q') AS q1
INNER JOIN (SELECT parentid FROM `qa_posts` WHERE `type` = 'A') AS q2 ON q1.postid = q2.parentid
select count(*)
from qa_posts
where parentid not in post_with_answers

在最后一行，我得到了以下错误：SQL Error [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'post_with_answers' at line 3

如何解决？

Answer 1

就像使用表一样：

select count(*)
from qa_posts
where parentid not in (select pwa.postid from post_with_answers pwa);

我要提醒您不要将not in与子查询一起使用。如果子查询中有一个值是NULL，则不返回任何行。因此，我推荐NOT EXISTS:

select count(*)
from qa_posts p
where not exists (select 1
from post_with_answers pwa
where p.parentid = pwa.postid
);

此外，您的视图定义有点过于复杂。您不需要子查询：

create view post_with_answers AS
SELECT DISTINCT pq.postid 
FROM qa_posts pq JOIN
qa_posts pa
ON pq.postid = pa.parentid
WHERE pq.type = 'Q' AND pa.type = 'A';

然后，DISTINCT只是增加了开销，所以EXISTS更好：

create view post_with_answers AS
SELECT DISTINCT pq.postid 
FROM qa_posts pq 
WHERE EXISTS (SELECT 1
FROM qa_posts pa
WHERE pq.postid = pa.parentid AND
pa.type = 'A'
)
WHERE pq.type = 'Q';