MySQL 视图,第1349号错误
作者:xling 来源:xling博客 时间:2008-05-18 13:04:00
把下面SQL里的SELECT单独执行,没有问题,但是用来CREATE VIEW 就报错了.
CREATE OR REPLACE VIEW BLOG_V_ADMIN
(ID,NICKNAME,SEX,EMAIL,PHONE,QQ,MSN,HTTP,REGISTER_TIME,
IP,LAST_LOGIN_IP,LOGIN_TIME)
AS
SELECT
A.ID,A.NICKNAME,A.SEX,A.EMAIL,A.PHONE,A.QQ,A.MSN,A.HTTP,A.REGISTER_TIME,
B.IP AS LAST_LOGIN_IP,B.LOGIN_TIME AS LAST_LOGIN_TIME
FROM
BLOG_ADMIN A LEFT JOIN
BLOG_ADMIN_LOGIN_TRACK B ON B.ADMIN = A.ID LEFT JOIN
(SELECT ADMIN,MAX(LOGIN_TIME) AS LOGIN_TIME FROM BLOG_ADMIN_LOGIN_TRACK GROUP BY ADMIN) C ON B.ADMIN = C.ADMIN;
ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause
这是BUG吗?见:http://bugs.mysql.com/bug.php?id=16757
木有办法,我只好这样改了:
CREATE OR REPLACE VIEW BLOG_V_ADMIN
(ID,NICKNAME,SEX,EMAIL,PHONE,QQ,MSN,HTTP,REGISTER_TIME,
LOGIN_TIME,LAST_LOGIN_IP)
AS
SELECT
A.ID,A.NICKNAME,A.SEX,A.EMAIL,A.PHONE,A.QQ,A.MSN,A.HTTP,A.REGISTER_TIME,
(SELECT LOGIN_TIME FROM BLOG_ADMIN_LOGIN_TRACK WHERE ADMIN = A.ID ORDER BY LOGIN_TIME DESC LIMIT 1) AS LAST_LOGIN_TIME,
(SELECT INET_NTOA(IP) FROM BLOG_ADMIN_LOGIN_TRACK WHERE ADMIN = A.ID ORDER BY LOGIN_TIME DESC LIMIT 1) AS LAST_LOGIN_IP
FROM
BLOG_ADMIN A;
标签:mysql,错误
0
投稿
猜你喜欢
Javascript的数组总结
2009-02-03 13:07:00
oracle中的视图详解
2009-12-22 11:48:00
javascript基础之数组(Array)对象
2008-06-25 13:32:00
XML 在使用中产生的二十个热点问题
2008-05-29 11:07:00
SQL Server 2005数据库中表的递归查询
2009-01-08 16:08:00
asp Response.flush 实时显示进度
2011-04-14 10:31:00
ASP代码中的容错机制
2008-10-19 17:30:00
asp封装dll源码分享
2008-09-25 17:20:00
使用ERWin进行基于MySQL数据库的物理设计
2009-01-04 12:54:00
打造设计你自己的字体 Ⅲ
2008-03-14 07:52:00
window.location.hash属性介绍
2008-03-21 12:39:00
另类解读SQL Server中的DateTime数据类型
2009-01-06 11:22:00
SQL学习笔记六 union联合结果集使用
2011-09-30 11:34:27
Safari显示网页字体为超级无敌难看的宋体的原因
2008-04-20 16:49:00
ASP实现语音分时问候
2007-10-02 12:12:00
德国ebay购头记
2009-04-29 11:10:00
分享13款非常有用的jQuery插件
2011-05-16 19:07:00
Web标准基础教程:CSS简写指南
2010-04-02 12:47:00
Chrome V8 引擎对 sort 的优化
2010-02-04 17:27:00
土豆网前端概况
2008-01-24 12:06:00