mysql - "in" 在这个位置无效

Question

我不确定mysql语句有什么问题。错误说“IN 在这个位置无效，期待：EOF，';'

这是我的sql语句：

 CREATE VIEW collab AS
 SELECT a.author_name AS Author, ca.no0fCollab
 FROM (SELECT * FROM author) as a, (SELECT * FROM coauthors) as ca
     WHERE a.author_id = ca.author_id
 AND a.author_id IN (
  SELECT UA.author_id
  FROM uniq_authored UA JOING publication p 
  ON P.pubid = UA.pubid
  WHERE (p.pubkey LIKE 'journals/%' OR p.pubkey LIKE 'conf/%')
  AND p.title LIKE '%data%'
  );

标签： mysql

Answer 1

您不能在视图中使用 FROM( subquery)
但您不需要 ....您可以使用 join 重构查询并避免子查询

  CREATE VIEW collab AS
  select a.author_name Author, ca.no0fCollab
  FROM author a
  INNER JOIN coauthors ca ON a.author_id = ca.author_id
  INNER JOIN uniq_authored ua ON a.author_id = ua.author_id
  INNER JOIN publication p ON P.pubid = ua.pubid 
    AND (p.pubkey LIKE 'journals/%' OR p.pubkey LIKE 'conf/%')
      AND p.title LIKE '%data%'