有三个表联取,user_group.*, user.*, radacct.* 取20条条件:radacct表里只取一条记录(与user表的登录名相同的一条最新的)$sortby .= ($this->order != "") ? " ORDER BY radacct.AcctStartTime ".$this->order : ""
$where.=" ORDER BY radacct.RadAcctId DESC LIMIT 1 ";
$sql .= $sortby." LIMIT ".(int)$page.",".(int)$offset;
现在这么写是错误的,有二个limit
望大家给个正确写法
$where.=" ORDER BY radacct.RadAcctId DESC LIMIT 1 ";
$sql .= $sortby." LIMIT ".(int)$page.",".(int)$offset;
现在这么写是错误的,有二个limit
望大家给个正确写法
user表 记录注册的用户表radacct 字段: UserName,StartTime(提交新闻时间)
radacct表 记载用户提交过新闻的记录(一个用户可以在不同时间提交N条新闻)表user_group 字段: user_group_id
要求出:所有的用户 在最新时间 提交过的新闻(只取每个用户提交过的最新记录的一条,第二次就不是最新了!)
select u.*,rad.*,ug.*
from user u left join user_group ug
on u.user_group_id=ug.user_group_id
left join
(select * from radacct t1 where not exists
(select 1 from radacct where userName=t1.userName and StartTime>t1.StartTime)) rad
on rad.UserName=u.user_login_name
order by rad.AcctStartTime desc
limit ?,?;
mysql> select * from user;
+-----------------+---------------+
| user_login_name | user_group_id |
+-----------------+---------------+
| n1 | 1 |
| n2 | 2 |
| n3 | 3 |
+-----------------+---------------+
3 rows in set (0.00 sec)mysql> select * from user_group ;
+---------------+
| user_group_id |
+---------------+
| 1 |
| 2 |
| 3 |
+---------------+
3 rows in set (0.00 sec)mysql> select * from radacct;
+----------+---------------------+
| username | starttime |
+----------+---------------------+
| n1 | 2009-11-02 12:10:00 |
| n1 | 2009-11-02 13:10:20 |
| n2 | 2009-11-01 01:20:36 |
| n2 | 2009-10-01 12:00:00 |
| n3 | 2009-10-12 14:00:00 |
| n3 | 2009-10-11 12:50:36 |
+----------+---------------------+
6 rows in set (0.00 sec)mysql> select u.*,rad.*,ug.*
-> from user u left join user_group ug
-> on u.user_group_id=ug.user_group_id
-> left join
-> (select * from radacct t1 where not exists
-> (select 1 from radacct where userName=t1.userName
-> and StartTime>t1.StartTime)) rad
-> on rad.UserName=u.user_login_name;+-----------------+---------------+----------+---------------------+------------
---+
| user_login_name | user_group_id | username | starttime | user_group_
id |
+-----------------+---------------+----------+---------------------+------------
---+
| n1 | 1 | n1 | 2009-11-02 13:10:20 |
1 |
| n2 | 2 | n2 | 2009-11-01 01:20:36 |
2 |
| n3 | 3 | n3 | 2009-10-12 14:00:00 |
3 |
+-----------------+---------------+----------+---------------------+------------
---+
3 rows in set (0.00 sec)