user表如下(保存用户名和uid)id username
1 admin
2 admin888
3 admin999user_login_history表如下(保存用户登陆历史)uid lasttime lastip
1 2009-10-09 192.168.1.2
1 2009-10-11 127.0.0.1
1 2009-11-19 34.54.67.2我想查出如下数据
id username lasttime lastip
1 admin 2009-11-19 34.54.67.2
2 admin888 null null
3 admin999 null null
不知道如何才能查询出来 请各位高手支招
1 admin
2 admin888
3 admin999user_login_history表如下(保存用户登陆历史)uid lasttime lastip
1 2009-10-09 192.168.1.2
1 2009-10-11 127.0.0.1
1 2009-11-19 34.54.67.2我想查出如下数据
id username lasttime lastip
1 admin 2009-11-19 34.54.67.2
2 admin888 null null
3 admin999 null null
不知道如何才能查询出来 请各位高手支招
FROM user
LEFT OUTER JOIN (
SELECT uid, lasttime, lastip
FROM user_login_history t1
JOIN (
SELECT uid, max( lasttime ) f1
FROM user_login_history
GROUP BY uid
)t2
USING ( uid )
WHERE t1.lasttime = t2.f1
)t3
USING ( uid )
但是如果当lasttime最大的记录不唯一时,即有两条这样的记录:
uid lasttime lastip
2 2010-01-01 1.1.1.2
2 2010-01-01 2.2.2.2
最后结果中会显示出两条。解决方法是将lasttime字段设置成datetime类型,避免重复。
from [user] u
join
(select uid,lasttime,lastip
from user_login_history t
where lasttime=(select max(lasttime) from user_login_history where uid=t.uid)
) t on u.id.t.uid
INSERT INTO [USER] SELECT
1 ,'admin' UNION ALL SELECT
2 ,'admin888' UNION ALL SELECT
3 ,'admin999'
GO
CREATE TABLE user_login_history(UID INT,lasttime DATETIME,lastip VARCHAR(20))
INSERT INTO user_login_history SELECT
1 ,'2009-10-09', '192.168.1.2' UNION ALL SELECT
1 ,'2009-10-11', '127.0.0.1' UNION ALL SELECT
1 ,'2009-11-19', '34.54.67.2'
select U.*,t.lasttime,t.lastip
from [user] u
LEFT join
(
select uid,lasttime,lastip
from user_login_history t
where lasttime=(select max(lasttime) from user_login_history where uid=t.uid)
) t on u.id=t.uid
/*
id username lasttime lastip
----------- ---------- ------------------------------------------------------ --------------------
1 admin 2009-11-19 00:00:00.000 34.54.67.2
2 admin888 NULL NULL
3 admin999 NULL NULL(所影响的行数为 3 行)*/ DROP TABLE [USER],user_login_history
1楼的也不错 只是会提示 #1054 - Unknown column 'uid' in 'from clause'信息 应该是mysql版本的问题 总之谢谢各位了