mysql - 尝试错误修复后，带有联合的MySQL View 不起作用

我有一个需要联合的mysql 5.6视图。我在这里阅读有关堆栈溢出的内容，在视图中有一个使用联合的服务器错误。解决方案是从中删除括号。我这样做了，但是它不起作用，希望有人可以以某种方式帮助我解决该错误。

我有2个表orderuccess和orderfailure。该视图将计算该时间段内成功的百分比。时间周期是每15m，每小时4个，15,30,45,00。这是架构，两者相同。计数0不会添加到表中。空值可以为0或100，具体取决于ifnull语句。

+---------+------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+---------+------------+------+-----+---------+-------+| id | int(11) | NO | PRI | NULL | || dtime | datetime | NO | PRI | NULL | || counter | bigint(20) | NO | | NULL | || rate | bigint(20) | YES | | NULL | |+---------+------------+------+-----+---------+-------+

当一段时间没有任何报告时，我有一个日历表来填补空白。每个时期只有日期/时间。这是日历表；

+-------+----------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| dtime | datetime | NO | PRI | NULL | |+-------+----------+------+-----+---------+-------+

这是返回正确数据的查询。

 select
'1000591' AS `id`,
`c`.`dtime` AS `dtime`,
'0' AS `rate`,
ifnull(`p`.`percent`,100) AS `counter`
from
(`calendar` `c` left join
        (
select
    ifnull(f.dtime,s.dtime) as dtime,
    ifnull(ifnull(s.counter,0)/(ifnull(s.counter,0) + ifnull(f.counter,0)) * 100,100) as percent
    from
        (select * from OrderSummarySuccess_14521 where id = 1000591) s
            right join
        (select * from OrderSummaryFailure_14521 where id = 1000591) f
        on s.dtime=f.dtime
union
select
    ifnull(f.dtime,s.dtime) as dtime,
    ifnull(ifnull(s.counter,0)/(ifnull(s.counter,0) + ifnull(f.counter,0)) * 100,100) as percent
    from
        (select * from OrderSummarySuccess_14521 where id = 1000591) s
            left join
        (select * from OrderSummaryFailure_14521 where id = 1000591) f
        on s.dtime=f.dtime
    order by dtime
        ) `p`
        on((`c`.`dtime` = `p`.`dtime`))
)
where c.dtime < now()`

由于必须在from子句中进行选择，因此我必须进行多个视图。这是此查询的创建视图。我删除了所有可能的括号。我错过了什么吗？还是有更好的方法编写查询？

CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER VIEW d61_14521 AS select '1000591' AS id, c.dtime AS dtime, 0 AS rate, ifnull(p.percent,100) AS counter from calendar c left join d61p2_14521 p on c.dtime = p.dtime;

CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER VIEW d61P2_14521 AS select '1000591' AS id, c.dtime AS dtime, '0' AS rate, ifnull(p.percent,100) AS counter
from d61P3_14521 union d61P6_14521 where c.dtime < now();

CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER VIEW d61P3_14521 AS select ifnull(f.dtime,s.dtime) as dtime, ifnull(ifnull(s.counter,0)/(ifnull(s.counter,0) + ifnull(f.counter,0)) * 100,100) as percent from d61P4_14521 s right join d61P5_14521 f on s.dtime=f.dtime ;

CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER VIEW d61P4_14521 AS select * from OrderSummarySuccess_14521 where id = 1000591 ;

CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER VIEW d61P5_14521 AS select * from OrderSummaryFailure_14521 where id = 1000591;

CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER VIEW d61P6_14521 AS select ifnull(f.dtime,s.dtime) as dtime, ifnull(ifnull(s.counter,0)/(ifnull(s.counter,0) + ifnull(f.counter,0)) * 100,100) as percent from d61P4_14521 s left join d61P5_14521 f on s.dtime=f.dtime ;

我按此顺序创建视图。他们成功直到d61P2_14521。


d61P4_14521
d61P5_14521
d61P6_14521
d61P3_14521
d61P2_14521


失败的视图是d61P2_14521。这是错误。

mysql> CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER -> VIEW d61P2_14521 AS select -> '1000591' AS id, -> c.dtime AS dtime, -> '0' AS rate, -> ifnull(p.percent,100) AS counter -> from -> d61P3_14521 -> union -> d61P6_14521 -> where c.dtime < now();ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'vod61P6_14521where c.dtime < now()' at line 10

最佳答案

在MySQL 5.7.7之前，您不能在视图的从子句（或联接子句）中使用子查询。因此，您必须在单独的视图中移动这些子查询。您的查询可以（经过一些清理）分为两个视图：

create viewSubqueryFullOuterJoin as
  select
    ifnull(f.dtime,s.dtime) as dtime,
    ifnull(ifnull(s.counter,0)/(ifnull(s.counter,0)
        + ifnull(f.counter,0)) * 100,100) as percent
  from OrderSummarySuccess_14521 s
  right join OrderSummaryFailure_14521 f
  on s.id = 1000591 and f.id = 1000591 and s.dtime=f.dtime
union
  select
    ifnull(f.dtime,s.dtime) as dtime,
    ifnull(ifnull(s.counter,0)/(ifnull(s.counter,0)
         + ifnull(f.counter,0)) * 100,100) as percent
  from OrderSummarySuccess_14521 s
  left join OrderSummaryFailure_14521 f
  on s.id = 1000591 and f.id = 1000591 and s.dtime=f.dtime
order by dtime;  -- 'order by' might belong in viewQueryWithSubquery

create viewQueryWithSubquery as
select
  '1000591' AS `id`,
  `c`.`dtime` AS `dtime`,
  '0' AS `rate`,
  ifnull(`p`.`percent`,100) AS `counter`
from `calendar` `c`
left join viewSubqueryFullOuterJoin `p`
on `c`.`dtime` = `p`.`dtime`
where `c`.dtime < now();

关于mysql - 尝试错误修复后，带有联合的MySQL View 不起作用，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/37660101/