代码如下:
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;