使用子查询的替代方法 - 无法在 Mysql 中创建视图 [英] Alternative to using a subquery - can't create view in Mysql

问题描述

我创建了一个视图，它根据计算提取一系列行；使用这个脚本(根据这个问题:How to根据字段值选择行范围 - MySQL :

I have created a view that pulls a range of rows based on a calculation; using this script (per this question: How to select Range of rows based on field values - MySQL :

select t.*
from curdataEvents t cross join
     (select max(revs) as maxrev from curdataEvents) x
where t.revs >= x.maxrev - 100000;

这会拉出我需要的行范围.为了获得所需的报告 - 我必须创建多个视图，每个视图创建报告的下一层.问题是 MySQL 不会使用子查询创建视图.关于如何重写上面的脚本以产生相同结果但允许我创建视图的任何想法?我已经使用 UNION 子句等尝试了多种变体.让我感到困惑的是这是对自身的连接.到目前为止我发现的使用多个表的例子.任何帮助是极大的赞赏！！！

This pulls the range of rows I need. In order to get the desired report - I have to create multiple views each creating the next layer of the report. The problem is MySQL won't create a view using a subquery. Any ideas on how to re-write the script above that would yield the same results but allow me to create a view? I have tried multiple variations using a UNION clause, etc. What's tripping me up is this is a join to itself. The examples I've found so far using multiple tables. Any help is greatly appreciated!!!

谢谢

推荐答案

您可以使用子查询，只是不能在 FROM 子句中.只需将其移至 WHERE 子句；

You can use a subquery, just not in the FROM clause. Just move it to the WHERE clause;

CREATE VIEW view1 AS 
SELECT t.*
FROM curdataEvents t 
WHERE t.revs >= (SELECT MAX(revs) - 100000 AS maxrev FROM curdataEvents)

用于测试的 SQLfiddle.

这篇关于使用子查询的替代方法 - 无法在 Mysql 中创建视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！