mysql - 创建 View 时出错

Question

我收到错误#1349 - View 的 SELECT 在 FROM 子句中包含子查询

我的代码是:

CREATE VIEW `MyViewName` AS
SELECT *
FROM
  (SELECT *
   FROM (
           (SELECT *
            FROM `crm_clients`
            WHERE ctype=1
            ORDER BY rand() LIMIT 0,
                                  3)
         UNION
           (SELECT *
            FROM `crm_clients`
            WHERE ctype=1
            ORDER BY rand() LIMIT 0,
                                  3)
         UNION
           (SELECT *
            FROM `crm_clients`
            WHERE ctype=1
            ORDER BY rand() LIMIT 0,
                                  3)
         UNION
           (SELECT *
            FROM `crm_clients`
            WHERE ctype=1
            ORDER BY rand() LIMIT 0,
                                  3)) t
   ORDER BY rand())

这段代码有什么问题？我不熟悉 View

Answer 1

定义 View 时，MySQL 不允许在 from 子句中使用子查询。

你可以做你想做的事:

Create View `MyViewName` as
    (SELECT * FROM `crm_clients` WHERE ctype=1 order by rand() limit 0,3)
    union 
    (SELECT * FROM `crm_clients` WHERE ctype=1 order by rand() limit 0,3)
    union
    (SELECT * FROM `crm_clients` WHERE ctype=1 order by rand() limit 0,3)
    union
    (SELECT * FROM `crm_clients` WHERE ctype=1 order by rand() limit 0,3)
    order by rand();

我的猜测是您实际上也想更改 ctype 值。否则查询会很奇怪。

Here是对 View 的限制。