mysql - 用于 View 创建的 FORM 子句中的 SELECT

Question

我有这个查询，我需要查看:

CREATE VIEW view_user_data AS
SELECT 
    u.uid AS uid, 
    u.name AS name,     
    (coalesce(value1, 0) + coalesce(value2, 0)) AS total_value, 
    (
        SELECT 
            value 
        FROM table3 
        WHERE value > total_value 
        LIMIT 1
    ) - 1 AS value,
FROM users AS u 
LEFT OUTER JOIN 
    (
        SELECT 
            a.id AS id, 
            ROUND(SUM(a.value * a.multiplier),0) AS value1 
        FROM table1 AS a 
        GROUP BY a.uid
    ) join1 ON u.uid = join1.uid 
LEFT OUTER JOIN 
    (
        SELECT 
            b.id AS id,
            ROUND(SUM(b.value * b.multiplier),0) AS value2 
        FROM table2 AS b 
        GROUP BY b.uid
    ) join2 ON u.uid = join2.uid 
ORDER BY uid

当我尝试时，我收到了这条消息:

#1349 - View's SELECT contains a subquery in the FROM clause

我的任何 FROM 子句中都没有 SELECT？这意味着什么？还是不支持在 JOINS 中选择？

问题是 - 有什么方法可以让这种观点成为可能吗？

Answer 1

您应该避免子选择将必要的子选择创建为 View

  CREATE VIEW join1 AS 
    SELECT 
      a.id AS id, 
      ROUND(SUM(a.value * a.multiplier),0) AS value1 
    FROM table1 AS a 
    GROUP BY a.uid
  ;


  CREATE VIEW join2 AS 
    SELECT 
        b.id AS id,
        ROUND(SUM(b.value * b.multiplier),0) AS value2 
    FROM table2 AS b 
    GROUP BY b.uid
  ; 

  CREATE VIEW view_value AS 
    SELECT 
        value 
    FROM table3 
    WHERE value > total_value 
    LIMIT 1 
  ;


  CREATE VIEW view_user_data AS
  SELECT 
      u.uid AS uid, 
      u.name AS name,     
      (coalesce(value1, 0) + coalesce(value2, 0)) AS total_value, 
      view_value.value -1 as value,
  FROM users AS u
  CROSS JOIN  view_value 
  LEFT OUTER JOIN join1 ON u.uid = join1.uid 
  LEFT OUTER JOIN join2 ON u.uid = join2.uid 
  ORDER BY uid