●○●○orderby 时间字段排序 asc 结果为什么是乱的呢?急在线等

etracer 2012-12-21 01:12:40
SELECT wr_endclosing , wr_driver_tel from hy_workrest where wr_driver_tel ='09012312312' and wr_cheduled_actual=1 and wr_comid =1 order by wr_endclosing asc
结果如下
wr_endclosing wr_driver_tel
2010-09-10 07:09:09 09012312312
2012-09-09 06:09:09 09012312312
2012-09-11 08:09:09 09012312312
--------------
这个结果为什么排序会混乱呢?应当是
2012-09-09 06:09:09
2010-09-10 07:09:09
2012-09-11 08:09:09
怎么能是10.09.11呢?则么这么乱呢?
2010-09-10 07:09:09
2012-09-09 06:09:09
2012-09-11 08:09:09
...全文
339 8 打赏 收藏 转发到动态 举报
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
etracer 2012-12-21
  • 打赏
  • 举报
回复
恩,测试也一下,果真加了data_format就不乱了,看来是需要加上data_format 谢谢您了,我把程序改改看看,然后就结贴,谢谢了
etracer 2012-12-21
  • 打赏
  • 举报
回复
我试验一下,我没写成DATE_FORMAT(wr_endclosing,'%T') ,我试一下
wwwwb 2012-12-21
  • 打赏
  • 举报
回复
这个结果为什么排序会混乱呢?应当是 2012-09-09 06:09:09 2010-09-10 07:09:09 2012-09-11 08:09:09 ORDER BY DATE_FORMAT(wr_endclosing,'%T') ASC 你要按时间:时分秒排序 怎么能是10.09.11呢?则么这么乱呢? 2010-09-10 07:09:09 2012-09-09 06:09:09 2012-09-11 08:09:09 这个是按:年月日时分秒排序
etracer 2012-12-21
  • 打赏
  • 举报
回复
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
etracer 2012-12-21
  • 打赏
  • 举报
回复


CREATE TABLE IF NOT EXISTS `hy_workrest` (
  `wr_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '',
  `wr_comid` int(4) NOT NULL,
  `wr_driver_tel` varchar(11) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '',
  `wr_status` int(1) NOT NULL DEFAULT '0' COMMENT '',
  `wr_getuptime` datetime NOT NULL COMMENT '',
  `getup_lat` decimal(11,8) NOT NULL COMMENT '',
  `getup_lon` decimal(11,8) NOT NULL COMMENT '',
  `getup_acc` decimal(11,3) NOT NULL COMMENT '',
  `wr_meters` int(7) NOT NULL COMMENT '',
  `wr_cost` int(10) NOT NULL COMMENT '',
  `wr_alcohol` int(11) NOT NULL DEFAULT '2' COMMENT '',
  `alcohol_time` datetime NOT NULL,
  `alcohol_lat` decimal(11,8) NOT NULL COMMENT '',
  `alcohol_lon` decimal(11,8) NOT NULL COMMENT '',
  `alcohol_acc` decimal(11,3) NOT NULL COMMENT '',
  `wr_starting` datetime NOT NULL COMMENT '',
  `wr_statusstart` int(1) NOT NULL DEFAULT '0',
  `wr_starting_lat` decimal(11,8) NOT NULL COMMENT '',
  `wr_starting_lon` decimal(11,8) NOT NULL COMMENT '',
  `wr_starting_acc` decimal(11,3) NOT NULL COMMENT '',
  `wr_reach` datetime NOT NULL COMMENT '',
  `wr_statusreach` int(1) NOT NULL,
  `wr_reach_lat` decimal(11,8) NOT NULL COMMENT '',
  `wr_reach_lon` decimal(11,8) NOT NULL COMMENT '',
  `wr_reach_acc` decimal(11,3) NOT NULL COMMENT '',
  `wr_closing` datetime NOT NULL COMMENT '',
  `wr_statusclose` int(1) NOT NULL DEFAULT '0',
  `wr_closing_lat` decimal(11,8) NOT NULL COMMENT '',
  `wr_closing_lon` decimal(11,8) NOT NULL COMMENT '',
  `wr_closing_acc` decimal(11,3) NOT NULL COMMENT '',
  `wr_endclosing` datetime NOT NULL COMMENT '',
  `wr_statusendclose` int(1) NOT NULL DEFAULT '0',
  `wr_endclosing_lat` decimal(11,8) NOT NULL COMMENT '',
  `wr_endclosing_lon` decimal(11,8) NOT NULL COMMENT '',
  `wr_endclosing_acc` decimal(11,3) NOT NULL COMMENT '',
  `wr_cheduled_actual` int(11) NOT NULL DEFAULT '0' COMMENT '',
  `wr_cheduled_actual_lat` decimal(11,8) NOT NULL COMMENT '',
  `wr_cheduled_actual_lon` decimal(11,8) NOT NULL COMMENT '',
  `wr_cheduled_actual_acc` decimal(11,3) NOT NULL COMMENT '',
  `wr_onfirm` int(1) NOT NULL DEFAULT '0' COMMENT '',
  `wr_scheexec` date NOT NULL COMMENT '',
  `wr_createday` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `wr_telposttime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`wr_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=184 ;

--
-- テーブルのデータをダンプしています `hy_workrest`
--

INSERT INTO `hy_workrest` (`wr_id`, `wr_comid`, `wr_driver_tel`, `wr_status`, `wr_getuptime`, `getup_lat`, `getup_lon`, `getup_acc`, `wr_meters`, `wr_cost`, `wr_alcohol`, `alcohol_time`, `alcohol_lat`, `alcohol_lon`, `alcohol_acc`, `wr_starting`, `wr_statusstart`, `wr_starting_lat`, `wr_starting_lon`, `wr_starting_acc`, `wr_reach`, `wr_statusreach`, `wr_reach_lat`, `wr_reach_lon`, `wr_reach_acc`, `wr_closing`, `wr_statusclose`, `wr_closing_lat`, `wr_closing_lon`, `wr_closing_acc`, `wr_endclosing`, `wr_statusendclose`, `wr_endclosing_lat`, `wr_endclosing_lon`, `wr_endclosing_acc`, `wr_cheduled_actual`, `wr_cheduled_actual_lat`, `wr_cheduled_actual_lon`, `wr_cheduled_actual_acc`, `wr_onfirm`, `wr_scheexec`, `wr_createday`, `wr_telposttime`) VALUES
(165, 1, '09012345679', 0, '2012-09-09 01:09:09', '0.00000000', '0.00000000', '0.000', 0, 0, 1, '2012-09-09 01:09:09', '0.00000000', '0.00000000', '0.000', '2012-09-09 01:09:09', 0, '0.00000000', '0.00000000', '0.000', '2012-09-09 01:09:09', 0, '0.00000000', '0.00000000', '0.000', '2012-09-09 15:09:09', 0, '0.00000000', '0.00000000', '0.000', '2012-09-09 15:09:09', 0, '0.00000000', '0.00000000', '0.000', 0, '0.00000000', '0.00000000', '0.000', 0, '2012-09-09', '2012-12-13 11:07:52', '0000-00-00 00:00:00'),
(164, 1, '09012345679', 3, '2010-09-10 01:09:09', '0.00000000', '0.00000000', '0.000', 0, 0, 0, '2010-09-10 01:09:09', '0.00000000', '0.00000000', '0.000', '2010-09-10 01:09:09', 2, '35.10000000', '136.60000000', '136.600', '2010-09-10 01:09:09', 3, '35.10000000', '136.61000000', '0.000', '2010-09-10 04:09:09', 1, '35.10000000', '136.68000000', '0.000', '2010-09-10 04:09:09', 3, '35.10000000', '136.69000000', '0.000', 1, '0.00000000', '0.00000000', '0.000', 0, '2012-09-09', '2012-12-13 11:12:48', '0000-00-00 00:00:00'),
(174, 1, '09012345678', 0, '2012-09-09 01:09:09', '0.00000000', '0.00000000', '0.000', 0, 0, 1, '2012-09-09 01:09:09', '0.00000000', '0.00000000', '0.000', '2012-09-09 01:09:09', 0, '0.00000000', '0.00000000', '0.000', '2012-09-09 01:09:09', 0, '0.00000000', '0.00000000', '0.000', '2012-09-09 15:09:09', 0, '0.00000000', '0.00000000', '0.000', '2012-09-09 15:09:09', 0, '0.00000000', '0.00000000', '0.000', 0, '0.00000000', '0.00000000', '0.000', 0, '2012-09-09', '2012-12-13 11:07:52', '0000-00-00 00:00:00'),
(175, 1, '09012345678', 3, '2010-09-10 01:09:09', '0.00000000', '0.00000000', '0.000', 0, 0, 0, '2010-09-10 01:09:09', '0.00000000', '0.00000000', '0.000', '2010-09-10 01:09:09', 2, '35.10000000', '136.60000000', '136.600', '2010-09-10 01:09:09', 3, '35.10000000', '136.61000000', '0.000', '2010-09-10 10:09:09', 1, '35.10000000', '136.68000000', '0.000', '2010-09-10 10:09:09', 3, '35.10000000', '136.69000000', '0.000', 1, '0.00000000', '0.00000000', '0.000', 0, '2012-09-09', '2012-12-13 11:12:48', '0000-00-00 00:00:00'),
(183, 1, '09012312312', 3, '2010-09-08 01:09:09', '0.00000000', '0.00000000', '0.000', 0, 0, 0, '2010-09-08 01:09:09', '0.00000000', '0.00000000', '0.000', '2010-09-08 01:09:09', 2, '35.10000000', '136.60000000', '136.600', '2010-09-08 01:09:09', 3, '35.10000000', '136.61000000', '0.000', '2010-09-08 05:09:09', 1, '35.10000000', '136.68000000', '0.000', '2010-09-08 05:09:09', 3, '35.10000000', '136.69000000', '0.000', 0, '0.00000000', '0.00000000', '0.000', 0, '2012-09-09', '2012-12-13 11:12:48', '0000-00-00 00:00:00'),
(182, 1, '09012312312', 0, '2012-09-09 01:09:09', '0.00000000', '0.00000000', '0.000', 0, 0, 1, '2012-09-09 01:09:09', '0.00000000', '0.00000000', '0.000', '2012-09-09 01:09:09', 0, '0.00000000', '0.00000000', '0.000', '2012-09-09 01:09:09', 0, '0.00000000', '0.00000000', '0.000', '2012-09-09 06:09:09', 0, '0.00000000', '0.00000000', '0.000', '2012-09-09 06:09:09', 1, '0.00000000', '0.00000000', '0.000', 1, '0.00000000', '0.00000000', '0.000', 0, '2012-09-09', '2012-12-13 11:07:52', '0000-00-00 00:00:00'),
(181, 1, '09012312312', 3, '2010-09-10 01:09:09', '0.00000000', '0.00000000', '0.000', 0, 0, 0, '2010-09-10 01:09:09', '0.00000000', '0.00000000', '0.000', '2010-09-10 01:09:09', 2, '35.10000000', '136.60000000', '136.600', '2010-09-10 01:09:09', 3, '35.10000000', '136.61000000', '0.000', '2010-09-10 07:09:09', 1, '35.10000000', '136.68000000', '0.000', '2010-09-10 07:09:09', 3, '35.10000000', '136.69000000', '0.000', 1, '0.00000000', '0.00000000', '0.000', 0, '2012-09-09', '2012-12-13 11:12:48', '0000-00-00 00:00:00'),
(180, 1, '09012312312', 0, '2012-09-11 01:09:09', '0.00000000', '0.00000000', '0.000', 0, 0, 1, '2012-09-11 01:09:09', '0.00000000', '0.00000000', '0.000', '2012-09-11 01:09:09', 0, '0.00000000', '0.00000000', '0.000', '2012-09-11 01:09:09', 0, '0.00000000', '0.00000000', '0.000', '2012-09-11 08:09:09', 0, '0.00000000', '0.00000000', '0.000', '2012-09-11 08:09:09', 0, '0.00000000', '0.00000000', '0.000', 1, '0.00000000', '0.00000000', '0.000', 0, '2012-09-09', '2012-12-13 11:07:52', '0000-00-00 00:00:00');

wwwwb 2012-12-21
  • 打赏
  • 举报
回复
贴建表及插入记录的SQL,以供测试
etracer 2012-12-21
  • 打赏
  • 举报
回复
wr_endclosing datetime
wwwwb 2012-12-21
  • 打赏
  • 举报
回复
wr_endclosing 什么类型?

56,675

社区成员

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧