多行数据,如何取日期最小的那一行? 数据库 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 mysql> select num1,num3 from t1 where num3 <= (select min(num3) from t1);+------+------------+| num1 | num3 |+------+------------+| 京*1 | 2013-02-03 || 京*2 | 2013-02-03 |+------+------------+2 rows in set (0.00 sec)num1:车牌号;num2:使用者;num3:使用日期;敢问这样可以否? select 车牌号 使用者 使用日期 from table a,(select 车牌号 min(使用日期)使用日期 from table group by 车牌号 使用日期)b where a.车牌号 = b.车牌号 and a.使用日期 = b.使用日期 select num1 ,min(num3) from tablename group by num1; 可能是我数据拟的不好,让你们误会了其实我想做的是这样的select num1, num2, num3 from t1 (select num1,min(num3) num3) t2 where t1.num1 = t2.num2但是在做大数据时感觉这样的自交效率不是很高,想求是不是能有什么高效的解法 可能是我数据拟的不好,让你们误会了其实我想做的是这样的select num1, num2, num3 from t1 (select num1,min(num3) num3) t2 where t1.num1 = t2.num2但是在做大数据时感觉这样的自交效率不是很高,想求是不是能有什么高效的解法 错了,是这样 select num1, num2, num3 from t1 (select num1,min(num3) num3) t2 where t1.num1 = t2.num2 and t1.num3 = t2.num3 如果要显示该最小日期对应的驾驶员,则sql 如下:create table `table3` ( `num1` varchar (90), `num2` varchar (90), `num3` datetime ); insert into `table3` (`num1`, `num2`, `num3`) values('京00001','张三','2013-02-03 00:00:00');insert into `table3` (`num1`, `num2`, `num3`) values('京00001','李四','2013-02-01 00:00:00');insert into `table3` (`num1`, `num2`, `num3`) values('京00001','王六','2013-02-02 00:00:00');insert into `table3` (`num1`, `num2`, `num3`) values('京00002','赵1','2013-03-02 00:00:00');insert into `table3` (`num1`, `num2`, `num3`) values('京00002','赵2','2013-03-01 00:00:00');insert into `table3` (`num1`, `num2`, `num3`) values('京00002','赵3','2013-03-05 00:00:00');insert into `table3` (`num1`, `num2`, `num3`) values('京00002','赵4','2013-03-01 00:00:00');SELECT num1,num2,num3 FROM table3 t1 WHERE NOT EXISTS (SELECT 1 FROM table3 t2 WHERE t1.num1=t2.num1 AND t1.num3>t2.num3); 如果要显示该最小日期对应的驾驶员,则sql 如下:create table `table3` ( `num1` varchar (90), `num2` varchar (90), `num3` datetime ); insert into `table3` (`num1`, `num2`, `num3`) values('京00001','张三','2013-02-03 00:00:00');insert into `table3` (`num1`, `num2`, `num3`) values('京00001','李四','2013-02-01 00:00:00');insert into `table3` (`num1`, `num2`, `num3`) values('京00001','王六','2013-02-02 00:00:00');insert into `table3` (`num1`, `num2`, `num3`) values('京00002','赵1','2013-03-02 00:00:00');insert into `table3` (`num1`, `num2`, `num3`) values('京00002','赵2','2013-03-01 00:00:00');insert into `table3` (`num1`, `num2`, `num3`) values('京00002','赵3','2013-03-05 00:00:00');insert into `table3` (`num1`, `num2`, `num3`) values('京00002','赵4','2013-03-01 00:00:00');SELECT num1,num2,num3 FROM table3 t1 WHERE NOT EXISTS (SELECT 1 FROM table3 t2 WHERE t1.num1=t2.num1 AND t1.num3>t2.num3);我试试效率 mysql的事务处理问题 mysql的问题 怎么合并这2句呢 求一条SQL linux+mysql脚本如何写 网页游戏物品合成的疑问 mysql的存储过程中,如何获得一个字段的值呢? mysql数据表被锁定应该怎么解决? 为什么CPU被Mysql大量占用?有什么优化吗? 关于mysql的缓存更新 mysql workbench 6.3ce:语法没错可运行但是一直显示红叉,重启后消失,怎么回事啊,强迫症忍不了啊T_T inner join + split 的简单问题 ! 关于两表数据匹配的求助
+------+------------+
| num1 | num3 |
+------+------------+
| 京*1 | 2013-02-03 |
| 京*2 | 2013-02-03 |
+------+------------+
2 rows in set (0.00 sec)
num1:车牌号;
num2:使用者;
num3:使用日期;
敢问这样可以否?
select 车牌号 使用者 使用日期 from table a,
(select 车牌号 min(使用日期)使用日期 from table group by 车牌号 使用日期)b
where a.车牌号 = b.车牌号 and a.使用日期 = b.使用日期
create table `table3` (
`num1` varchar (90),
`num2` varchar (90),
`num3` datetime
);
insert into `table3` (`num1`, `num2`, `num3`) values('京00001','张三','2013-02-03 00:00:00');
insert into `table3` (`num1`, `num2`, `num3`) values('京00001','李四','2013-02-01 00:00:00');
insert into `table3` (`num1`, `num2`, `num3`) values('京00001','王六','2013-02-02 00:00:00');
insert into `table3` (`num1`, `num2`, `num3`) values('京00002','赵1','2013-03-02 00:00:00');
insert into `table3` (`num1`, `num2`, `num3`) values('京00002','赵2','2013-03-01 00:00:00');
insert into `table3` (`num1`, `num2`, `num3`) values('京00002','赵3','2013-03-05 00:00:00');
insert into `table3` (`num1`, `num2`, `num3`) values('京00002','赵4','2013-03-01 00:00:00');SELECT num1,num2,num3 FROM table3 t1 WHERE NOT EXISTS (SELECT 1 FROM table3 t2 WHERE t1.num1=t2.num1 AND t1.num3>t2.num3);
create table `table3` (
`num1` varchar (90),
`num2` varchar (90),
`num3` datetime
);
insert into `table3` (`num1`, `num2`, `num3`) values('京00001','张三','2013-02-03 00:00:00');
insert into `table3` (`num1`, `num2`, `num3`) values('京00001','李四','2013-02-01 00:00:00');
insert into `table3` (`num1`, `num2`, `num3`) values('京00001','王六','2013-02-02 00:00:00');
insert into `table3` (`num1`, `num2`, `num3`) values('京00002','赵1','2013-03-02 00:00:00');
insert into `table3` (`num1`, `num2`, `num3`) values('京00002','赵2','2013-03-01 00:00:00');
insert into `table3` (`num1`, `num2`, `num3`) values('京00002','赵3','2013-03-05 00:00:00');
insert into `table3` (`num1`, `num2`, `num3`) values('京00002','赵4','2013-03-01 00:00:00');SELECT num1,num2,num3 FROM table3 t1 WHERE NOT EXISTS (SELECT 1 FROM table3 t2 WHERE t1.num1=t2.num1 AND t1.num3>t2.num3);
我试试效率