有一张表
t_testid int4, 主健
user_id int4,
visit_date timestamp要求输出记录,但是user_id相同的记录并且visit_date里同一天的数据只输出一条
而且, 排序是按 user_id 升序, visit_date 降序insert into t_test values(1, 13, timestamp '2007-4-12 09:30:21');
insert into t_test values(2, 13, timestamp '2007-4-12 12:31:32');
insert into t_test values(3, 13, timestamp '2007-4-12 23:11:00');
insert into t_test values(4, 13, timestamp '2007-5-23 07:41:42');
insert into t_test values(5, 15, timestamp '2007-4-12 23:51:52');结果应该只有3条输出。
t_testid int4, 主健
user_id int4,
visit_date timestamp要求输出记录,但是user_id相同的记录并且visit_date里同一天的数据只输出一条
而且, 排序是按 user_id 升序, visit_date 降序insert into t_test values(1, 13, timestamp '2007-4-12 09:30:21');
insert into t_test values(2, 13, timestamp '2007-4-12 12:31:32');
insert into t_test values(3, 13, timestamp '2007-4-12 23:11:00');
insert into t_test values(4, 13, timestamp '2007-5-23 07:41:42');
insert into t_test values(5, 15, timestamp '2007-4-12 23:51:52');结果应该只有3条输出。
FROM t_test
GROUP BY USER_ID,DATE(VISIT_DATE)
ORDER BY user_id ASC,visit_date DESC
id user_id visit_date
4 13 2007-05-23 07:41:42
3 13 2007-04-12 23:11:00
5 15 2007-04-12 23:51:52
select distinct [user_id],visit_date from t_test
输出:
13 2007-04-12 00:00:00.000
13 2007-05-23 00:00:00.000
15 2007-04-12 00:00:00.000
我把你日期后的时间去掉了.
还有,如果你要同时输出id的话,我感觉是不能只输出3条信息的,因为id是主键,每条信息都对应不同的id,可以group user_id,visit_date,但是你不能同时group id
(select id ,user_id ,substr(visit_date,0,length(visit_date)-6) as visit_date2 from t_test
group by user_id,substr(visit_date,0,length(visit_date)-6) ) b
where a.id=b.id
and a.substr(visit_date,0,length(visit_date)-6)=b.visit_date2
order by a.user_id asc,a.visit_date desc
应该可以,不会用date
以下试试,参考:
select max(id) as id,user_id,visit_date from t_test group by concat(user_id,left(visit_date,10)) order by user_id asc,id desc;
mysql> select max(id),user_id,visit_date from t_test group by user_id,date(visit
_date) order by user_id,visit_date desc;结果:
+---------+---------+---------------------+
| max(id) | user_id | visit_date |
+---------+---------+---------------------+
| 4 | 13 | 2007-05-23 07:41:42 |
| 3 | 13 | 2007-04-12 09:30:21 |
| 5 | 15 | 2007-04-12 23:51:52 |
+---------+---------+---------------------+
3 rows in set (0.01 sec)
#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '( visit_date )
ORDER BY user_id, visit_date DESC LIMIT 0, 30
create table t_test(id int(4) primary key,user_id int(4),visit_date timestamp)
;
//插入数据
insert into t_test values(1,13,"2007-4-12 09:30:21");
insert into t_test values(2,13,"2007-4-12 12:31:32");
insert into t_test values(3,13,"2007-4-12 23:11:00");
insert into t_test values(4,13,"2007-5-23 07:41:42");
insert into t_test values(5,15,"2007-4-12 23:51:52");
//实现:
select t.id,t.user_id,min(t.visit_date) as visitdate
from t_test as t
group by day(t.visit_date),t.user_id
order by t.user_id,t.visit_date desc//查询结果:
+----+---------+---------------------+
| id | user_id | visitdate |
+----+---------+---------------------+
| 4 | 13 | 2007-05-23 07:41:42 |
| 1 | 13 | 2007-04-12 09:30:21 |
| 5 | 15 | 2007-04-12 23:51:52 |
+----+---------+---------------------+