带有子查询的 MySQL 视图 [英] MySQL View with a SubQuery

问题描述

我有以下观点，我需要从 Oracle 移植到 MySQL.这在 Oracle 中非常有效，但在 MySQL 中却不能，因为 subquery cannot exist in a view 错误.如何转换它以使其在 MySQL 中工作?如果我要对此提出另一个观点，它将如何影响性能?是否可取?如果是这样，我该怎么做?如果第二个视图不是一个好主意，如何转换它?感谢您的帮助！

I have the following view that I need to port from Oracle to MySQL. This works perfectly in Oracle, but not in MySQL due to a subquery cannot exist in a view error. How can I convert this to make it work in MySQL? If I am to make another view for this, how would it impact performance? Is it advisable? If so, how can I do that? If a 2nd view is not that great of an idea, how to convert this? Thanks for your help!

CREATE OR REPLACE VIEW view_name (var1, var2, var3, var4, var5) AS 
SELECT SUM(A.var1a) var1,
        SUM(A.var2a) var2,
        SUM(A.var3a) var3,
        SUM(A.var4a) var4,
        SUM(A.var5a) var5
 FROM (SELECT CASE columnx when 'abc' then COUNT(E.ID) end var1a,
     CASE columnx when 'def' then COUNT(E.ID) end var2a,
     CASE columnx when 'ghi' then COUNT(E.ID) end var3a,
     CASE columnx when 'jkl' then COUNT(E.ID) end var4a,
                    COUNT(E.ID) var5a
             FROM   <list of tables>
            WHERE   <set of conditions>
         GROUP BY   columnx) A;

推荐答案

您应该能够使用以下内容重写此内容:

You should be able to rewrite this using the following:

CREATE OR REPLACE VIEW view_name (var1, var2, var3, var4, var5) AS 
SELECT 
    sum(CASE columnx when 'abc' then 1 else 0 end) var1a,
    sum(CASE columnx when 'def' then 1 else 0 end) end var2a,
    sum(CASE columnx when 'ghi' then 1 else 0 end) end var3a,
    sum(CASE columnx when 'jkl' then 1 else 0 end)end var4a,
    COUNT(E.ID) var5a
FROM   <list of tables>
WHERE   <set of conditions>

这篇关于带有子查询的 MySQL 视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！