所有在更改视图时生成错误代码的MySQL设计器
所有在更改视图时生成错误代码的MySQL设计器
提问于 2012-02-07 07:03:02
我有一个完美工作的MySQL视图：
SELECT `t`.`TraderId` AS `TraderId`,`t`.`ShopName` AS `ShopName`,`t`.`UserId` AS `UserId`,`t`.`CreatedOn` AS `CreatedOn`,`t`.`MOL` AS `MOL`,`u`.`Phone` AS `Phone`,`u`.`Email` AS `Email`,`rt`.`Name` AS `RentType`,(
SELECT SUM(`o`.`Ammount`) AS `AmmountSum`
FROM `Orders` `o`
WHERE (`o`.`TraderId` = `t`.`TraderId`)) AS `Revenue`,(
SELECT SUM(`o`.`Ammount`) AS `SUM(o.Ammount)`
FROM `Orders` `o`
WHERE ((MONTH(`o`.`DeliveryDate`) = MONTH(CURDATE())) AND (YEAR(`o`.`DeliveryDate`) = YEAR(CURDATE())) AND (`o`.`TraderId` = `t`.`TraderId`))) AS `MonthRevenue`,(`t`.`RatingSum` / `t`.`RatingVotes`) AS `Rating`,(
SELECT CASE `rt`.`IsMonthlyBased` WHEN 1 THEN (
SELECT ( (12 * ((YEAR(CURDATE())
- YEAR(`t`.`LastPaidDate`)))
+ (MONTH(CURDATE())
- MONTH(`t`.`LastPaidDate`))) * `rt`.`Tax`)) END) AS `Debt`
FROM ((`Traders` `t`
LEFT JOIN `Users` `u` ON((`u`.`UserId` = `t`.`UserId`)))
LEFT JOIN `RentTypes` `rt` ON((`rt`.`RentTypeId` = `t`.`RentTypeId`)))我不知道如何格式化代码，所以如果您可以将其复制并粘贴到编辑器中并查看它。谢谢。
因此，视图正在工作。但是当转到任何设计器(heideSql，workBench，navicat)并点击alter view时，生成的脚本是错误的：
SELECT `t`.`TraderId` AS `TraderId`,`t`.`ShopName` AS `ShopName`,`t`.`UserId` AS `UserId`,`t`.`CreatedOn` AS `CreatedOn`,`t`.`MOL` AS `MOL`,`u`.`Phone` AS `Phone`,`u`.`Email` AS `Email`,`rt`.`Name` AS `RentType`,(
SELECT SUM(`o`.`Ammount`) AS `AmmountSum`
FROM `Orders` `o`
WHERE (`o`.`TraderId` = `t`.`TraderId`)) AS `Revenue`,(
SELECT SUM(`o`.`Ammount`) AS `AmmountSumMonth`
FROM `Orders` `o`
WHERE ((MONTH(`o`.`DeliveryDate`) = MONTH(CURDATE())) AND (YEAR(`o`.`DeliveryDate`) = YEAR(CURDATE())) AND (`o`.`TraderId` = `t`.`TraderId`))) AS `MonthRevenue`,(`t`.`RatingSum` / `t`.`RatingVotes`) AS `Rating`,(
SELECT (CASE `rt`.`IsMonthlyBased` WHEN 1 THEN (
SELECT (((12 * (YEAR(CURDATE()) - YEAR(`t`.`LastPaidDate`))) + (MONTH(CURDATE()) - MONTH(`t`.`LastPaidDate`))) * `rt`.`Tax`) AS `( (12 * ((YEAR(CURDATE())
- YEAR(``t``.``LastPaidDate``)))
+ (MONTH(CURDATE())
- MONTH(``t``.``LastPaidDate``))) * ``rt``.``Tax``)`) END) AS `CASE ``rt``.``IsMonthlyBased`` WHEN 1 THEN (
SELECT ( (12 * ((YEAR(CURDATE())
- YEAR(``t``.``LastPaidDate``)))
+ (MONTH(CURDATE())
- MONTH(``t``.``LastPaidDate``))) * ``rt``.``Tax``)) END`) AS `Debt`
FROM ((`Traders` `t`
LEFT JOIN `Users` `u` ON((`u`.`UserId` = `t`.`UserId`)))
LEFT JOIN `RentTypes` `rt` ON((`rt`.`RentTypeId` = `t`.`RentTypeId`)))我的最后一个select列生成了两次，有双引号()等等。那么我的错误在哪里呢？
回答 1
Stack Overflow用户
回答已采纳
发布于 2012-02-07 07:12:09
MySQL视图以可由MySQL快速解析和执行的格式存储，但与存储过程不同的是，不存储为创建视图而输入的文本。设计器提取的代码应该执行与原始查询相同的代码。
关键是将CREATE VIEW语句保存到.sql文件中，当您想要更改视图时，打开该文件，修改，然后复制并粘贴到MySQL中。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/9168664
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号