用于创建视图的select in form子句

p5cysglq 于 2021-06-21 发布在 Mysql

关注(0)|答案(1)|浏览(283)

我有一个问题需要查看：

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？那是什么意思？或者也不支持select in联接？
问题是-有没有办法让这种观点成为可能？

mysql views

来源：https://stackoverflow.com/questions/50357056/select-in-form-clause-for-view-creation

1条答案

按热度按时间

vsikbqxv1#

应避免创建必要的子选择作为视图

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

赞(0）回复(0）举报 2021-06-21

我来回答

用于创建视图的select in form子句

1条答案

相关问题

热门标签

最新问答