包含子查询的 MySQL 临时 View

Question

我正在尝试使用子查询创建一个 View 。根据the MySQL docs当我的 View 使用 MERGE 算法时，我无法执行此操作，但如果我使用 temptable 算法，则可以执行此操作。有什么指示我在做什么/读错了吗？

CREATE ALGORITHM =  temptable VIEW `vw_prod_placementinfo_destination` AS
select 
    d.branch,
    d.media_plan_name,  
    d.placement,
    case c.country WHEN null or 'n/a' then d.one else d.three end as Creative,
    case length(d.four)-length(replace(four, "x", '')) > 0 when true then d.four else Null end as AdSize
from (
    select 
        branch, 
        media_plan_name, 
        placement,
        split_str(placement, '_', 1) as One,
        split_str(placement, '_', 2) as Two,
        split_str(placement, '_', split_count(placement, '_')-1) as Three,
        split_str(placement, '_', split_count(placement, '_')) as Four
    from campaign_delivery_flat
    where media_plan_name like '%Destinatio%'
    group by branch, media_plan_name, placement ) d
left join country_code c on d.One = c.code

Answer 1

评论有点长。

您引用的文档对于 from 子句中的子查询没有任何说明。相应的文档是here 。而且，它相当明确且明确:

Subqueries cannot be used in the FROM clause of a view.

(第二段)。

如果您需要进行此设置，请为子查询创建一个 View ，并为外部查询创建一个 View 。

或者，重写逻辑，这样就不需要子查询。例如，您可以使用相关子查询来获取国家/地区:

select 
    branch, 
    media_plan_name, 
    placement,
    (case when exists (select 1 from country_code c on d.One = c.code)
          then split_str(placement, '_', 1)
          else split_str(placement, '_', split_count(placement, '_')-1)
     end) as creative
     as One,
     (case length( split_str(placement, '_', split_count(placement, '_')) )-length(replace( split_str(placement, '_', split_count(placement, '_')) , "x", '')) > 0
           when true then split_str(placement, '_', split_count(placement, '_'))
           else Null
      end) as AdSize
from campaign_delivery_flat
where media_plan_name like '%Destinatio%'
group by branch, media_plan_name, placement