请大家帮忙看下mysql排序有关问题

2013-04-27

请大家帮忙看下mysql排序问题本帖最后由 developerFBI 于 2013-04-26 08:22:12 编辑表结构如下:CREATE TAB

请大家帮忙看下mysql排序问题
本帖最后由 developerFBI 于 2013-04-26 08:22:12 编辑表结构如下:
CREATE TABLE terminalGps(
termiGpsId INT AUTO_INCREMENT PRIMARY KEY,
termiGpsLng VARCHAR(20),
termiGpsLat VARCHAR(20),
termiGpsDate VARCHAR(20),
termiId INT,
termiRemark VARCHAR(100)
)ENGINE=INNODB;
插入的数据:

insert into `terminalGps` (`termiGpsId`, `termiGpsLng`, `termiGpsLat`, `termiGpsDate`, `termiId`, `termiRemark`) values('1','106.540983','29.560692','2013-04-22 12:20:30','4',NULL);
insert into `terminalGps` (`termiGpsId`, `termiGpsLng`, `termiGpsLat`, `termiGpsDate`, `termiId`, `termiRemark`) values('2','106.556956','29.556587','2013-04-22 12:20:30','3',NULL);
insert into `terminalGps` (`termiGpsId`, `termiGpsLng`, `termiGpsLat`, `termiGpsDate`, `termiId`, `termiRemark`) values('3','106.554477','29.563624','2013-04-22 13:20:30','3',NULL);
insert into `terminalGps` (`termiGpsId`, `termiGpsLng`, `termiGpsLat`, `termiGpsDate`, `termiId`, `termiRemark`) values('4','106.548332','29.560231','2013-04-22 12:50:30','2',NULL);

##终端表
DROP TABLE IF EXISTS terminal;
CREATE TABLE terminal(
termiId int auto_increment primary key,
termiMachineId varchar(20),#终端唯一标识
termiName varchar(20),#名称
termiOs varchar(20),#操作系统
termiMacAdress varchar(20),#终端的mac地址
termiPort varchar(20),#端口
termiIp varchar(20),#ip
termiRemark varchar(100)
)ENGINE=InnoDB;

insert into `terminal` (`termiId`, `termiMachineId`, `termiName`, `termiOs`, `termiMacAdress`, `termiRemark`, `termiPort`, `termiIp`) values('3','htc2457884','htcName','Anroid','tt','remark',NULL,NULL);
insert into `terminal` (`termiId`, `termiMachineId`, `termiName`, `termiOs`, `termiMacAdress`, `termiRemark`, `termiPort`, `termiIp`) values('4','nokia14754','nokiaName','Anroid','aa','remark',NULL,NULL);
insert into `terminal` (`termiId`, `termiMachineId`, `termiName`, `termiOs`, `termiMacAdress`, `termiRemark`, `termiPort`, `termiIp`) values('5','ipad31547','ipad3','IOS','541','remark',NULL,NULL);
insert into `terminal` (`termiId`, `termiMachineId`, `termiName`, `termiOs`, `termiMacAdress`, `termiRemark`, `termiPort`, `termiIp`) values('6','hase2541','hase','Android','dsf54','remark',NULL,NULL);
insert into `terminal` (`termiId`, `termiMachineId`, `termiName`, `termiOs`, `termiMacAdress`, `termiRemark`, `termiPort`, `termiIp`) values('7','htc324','htc','Android','dsf5421','remark',NULL,NULL);

##终端与员工关系表
DROP TABLE IF EXISTS termAndEmpRelation;
CREATE TABLE termAndEmpRelation(
taerId VARCHAR(20) PRIMARY KEY,
termiId INT,#终端表Id
taerDoDate VARCHAR(20),#被分配日期
taerBeginDate VARCHAR(20),#开始使用日期
taerEndDate VARCHAR(20),#结束使用日期
emplId VARCHAR(20),#人员表id
termiRemark VARCHAR(60) #备注
)ENGINE=INNODB;

insert into `termAndEmpRelation` (`taerId`, `termiId`, `taerDoDate`, `taerBeginDate`, `taerEndDate`, `emplId`, `termiRemark`) values('t41475543','3','2013-1-4','2013-1-4 12:10:20','2013-1-4 18:10:20','345345435346','sdf');
insert into `termAndEmpRelation` (`taerId`, `termiId`, `taerDoDate`, `taerBeginDate`, `taerEndDate`, `emplId`, `termiRemark`) values('t41475544','3','2013-1-4','2013-1-4 12:10:20','2013-1-4 18:10:20','345345435345','sdf');

##员工表
DROP TABLE IF EXISTS Employee;
CREATE TABLE Employee(
#emplIdVARCHAR(20)  PRIMARY KEY,主键
emplIdint  PRIMARY KEY,#主键
emplNo VARCHAR(20),             #账号
emplName VARCHAR(20),           #姓名
emplPwd VARCHAR(20),            #密码
emplPhoneNum VARCHAR(15),       #手机号码
EmplQualifi INT,#人员资质:1、一般查勘员，2、资深查勘员
EmpLat NUMERIC(18,6),        #经度
EmpLngNUMERIC(18,6),#纬度
EmplRoleId VARCHAR(20),#角色表的外键id
EmplBirth VARCHAR(20),#出生日期
EmplJoinDate VARCHAR(20),#入职日期
EmplRemark VARCHAR(200)#备注
)ENGINE=INNODB;

insert into `employee` (`emplId`, `emplNo`, `emplName`, `emplPwd`, `emplPhoneNum`, `EmplQualifi`, `EmpLat`, `EmpLng`, `EmplRoleId`, `EmplBirth`, `EmplJoinDate`, `EmplRemark`) values('25474454477','xusy','徐士义','32','16865326033','0','29.5595400000','106.5463560000','2','1987-4-5','2013-1-5','remark');
insert into `employee` (`emplId`, `emplNo`, `emplName`, `emplPwd`, `emplPhoneNum`, `EmplQualifi`, `EmpLat`, `EmpLng`, `EmplRoleId`, `EmplBirth`, `EmplJoinDate`, `EmplRemark`) values('324234234234','lichg','倪长贵','43','16853592603','0','29.5649750000','106.5540820000','2','1987-5-5','2013-1-5','remark');
insert into `employee` (`emplId`, `emplNo`, `emplName`, `emplPwd`, `emplPhoneNum`, `EmplQualifi`, `EmpLat`, `EmpLng`, `EmplRoleId`, `EmplBirth`, `EmplJoinDate`, `EmplRemark`) values('3435345345','liuj','刘健','432','16853592603','1','29.5651010000','106.5633520000','3','1987-6-5','2013-1-5','remark');
insert into `employee` (`emplId`, `emplNo`, `emplName`, `emplPwd`, `emplPhoneNum`, `EmplQualifi`, `EmpLat`, `EmpLng`, `EmplRoleId`, `EmplBirth`, `EmplJoinDate`, `EmplRemark`) values('34534534534','zhouxy','周星宇','212','16876592603','1','29.5611420000','106.5478290000','2','1987-8-5','2013-1-5','remark');
insert into `employee` (`emplId`, `emplNo`, `emplName`, `emplPwd`, `emplPhoneNum`, `EmplQualifi`, `EmpLat`, `EmpLng`, `EmplRoleId`, `EmplBirth`, `EmplJoinDate`, `EmplRemark`) values('34534535435','xiaoe','小二','21','1683292603','0','29.5649120000','106.5503450000','1','1988-4-5','2013-1-5','remark');
insert into `employee` (`emplId`, `emplNo`, `emplName`, `emplPwd`, `emplPhoneNum`, `EmplQualifi`, `EmpLat`, `EmpLng`, `EmplRoleId`, `EmplBirth`, `EmplJoinDate`, `EmplRemark`) values('345345435345','tfq','唐富强','123','15823592603','1','29.5606920000','106.5409830000','1','1982-4-5','2013-2-5','remark');
insert into `employee` (`emplId`, `emplNo`, `emplName`, `emplPwd`, `emplPhoneNum`, `EmplQualifi`, `EmpLat`, `EmpLng`, `EmplRoleId`, `EmplBirth`, `EmplJoinDate`, `EmplRemark`) values('345345435346','liuls','刘老师','32','15823592603','1','29.5565870000','106.5569560000','1','1987-4-5','2013-2-5','remark');
insert into `employee` (`emplId`, `emplNo`, `emplName`, `emplPwd`, `emplPhoneNum`, `EmplQualifi`, `EmpLat`, `EmpLng`, `EmplRoleId`, `EmplBirth`, `EmplJoinDate`, `EmplRemark`) values('43533443543','fanls','范老师','343','16853532603','1','29.5636240000','106.5544770000','3','1987-4-5','2013-1-5','remark');
insert into `employee` (`emplId`, `emplNo`, `emplName`, `emplPwd`, `emplPhoneNum`, `EmplQualifi`, `EmpLat`, `EmpLng`, `EmplRoleId`, `EmplBirth`, `EmplJoinDate`, `EmplRemark`) values('45465454654','lishi','李四','23','15432292603','0','29.5602310000','106.5483320000','1','1987-4-5','2013-1-5','remark');

要想获取如图的最后两条记录:
请大家帮忙看下mysql排序有关问题

请问一下大家,这条sql应该怎样写.谢谢了! MySQL mysql排序
[解决办法]
为什么要获取最后这两条？
[解决办法]
你是要取termiGpsDate最大时间的那两个数据么？
可以order by termiGpsDate desc，然后取得前两条就是了。

热点排行

Java Web开发

请大家帮忙看下mysql排序有关问题