想达到的效果图:如看不到图,图片连接:http://hiphotos.baidu.com/loglab/pic/item/deb30c365e3e09050b55a913.jpg求一句MYSQL搜索语句条件一:现在假定数据表结构及部分数据如下:id username inserttime
1 aa 2010-02-26 01:08:23
2 bb 2010-02-26 01:48:42
3 bb 2010-02-26 02:18:13
4 cc 2010-02-26 03:58:00
5 aa 2010-02-26 08:08:00
6 dd 2010-02-26 08:18:04
7 aa 2010-02-26 08:43:10条件二:假定现在系统时间是2010-02-26 23:59:59求:距系统时间24小时内,各小时的在线人数总和。如能用一句MYSQL表示,请问如何写出?
1 aa 2010-02-26 01:08:23
2 bb 2010-02-26 01:48:42
3 bb 2010-02-26 02:18:13
4 cc 2010-02-26 03:58:00
5 aa 2010-02-26 08:08:00
6 dd 2010-02-26 08:18:04
7 aa 2010-02-26 08:43:10条件二:假定现在系统时间是2010-02-26 23:59:59求:距系统时间24小时内,各小时的在线人数总和。如能用一句MYSQL表示,请问如何写出?
select DATE_FORMAT(inserttime,'%y%m%d%H'),count(*)
from 数据表
where inserttime >= NOW() - 24 hour
group by DATE_FORMAT(inserttime,'%y%m%d%H')
http://hi.baidu.com/loglab/blog/item/246534fc85a04589b801a06c.html
谢谢
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
下面是此表的SQL文件内容,大家可以复制保存为.SQL文件,导入MYSQL测试-- phpMyAdmin SQL Dump
-- version 3.2.5
-- http://www.phpmyadmin.net
--
-- 主机: 127.0.0.1
-- 生成日期: 2010 年 02 月 26 日 09:35
-- 服务器版本: 5.1.44
-- PHP 版本: 5.2.12SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;--
-- 数据库: `radius`
---- ----------------------------------------------------------
-- 表的结构 `routerstats_upuserlog`
--CREATE TABLE IF NOT EXISTS `routerstats_upuserlog` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`mac` varchar(20) NOT NULL,
`clientname` varchar(100) NOT NULL,
`clientmac` varchar(20) NOT NULL,
`kbup` varchar(10) NOT NULL,
`kbdown` varchar(10) NOT NULL,
`uptime` varchar(20) NOT NULL,
`routerip` varchar(20) NOT NULL,
`manufacture` varchar(20) NOT NULL,
`inserttime` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `clientmac` (`clientmac`),
KEY `inserttime` (`inserttime`),
KEY `mac` (`mac`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=47526804 ;--
-- 转存表中的数据 `routerstats_upuserlog`
--INSERT INTO `routerstats_upuserlog` (`id`, `mac`, `clientname`, `clientmac`, `kbup`, `kbdown`, `uptime`, `routerip`, `manufacture`, `inserttime`) VALUES
(47526800, '00:12:CF:CB:8A:30', '', '00:80:87:B6:F8:53', '94', '320', '', '5.203.138.48', '', '2009-06-01 11:32:56'),
(47526801, '00:12:CF:CB:8A:30', '', '00:80:87:B6:F8:53', '99', '327', '', '5.203.138.48', '', '2009-06-01 11:33:42'),
(47526802, '00:12:CF:CB:8A:30', 'aaa', 'aaa', '12', '24', '', '', '', '2010-02-25 20:26:55'),
(47526803, '00:12:CF:CB:8A:30', 'fdsfds', 'fdsfdfdsfsd', '22', '333', '', '', '', '2010-02-26 04:27:34');
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)mysql> select * from routerstats_upuserlog;
+----------+-------------------+------------+-------------------+------+--------+--------+--------------+-------------+---------------------+
| id | mac | clientname | clientmac | kbup | kbdown | uptime | routerip | manufacture | inserttime |
+----------+-------------------+------------+-------------------+------+--------+--------+--------------+-------------+---------------------+
| 47526800 | 00:12:CF:CB:8A:30 | | 00:80:87:B6:F8:53 | 94 | 320| | 5.203.138.48 | | 2009-06-01 11:32:56 |
| 47526801 | 00:12:CF:CB:8A:30 | | 00:80:87:B6:F8:53 | 99 | 327| | 5.203.138.48 | | 2009-06-01 11:33:42 |
| 47526802 | 00:12:CF:CB:8A:30 | aaa | aaa | 12 | 24| | | | 2010-02-25 20:26:55 |
| 47526803 | 00:12:CF:CB:8A:30 | fdsfds | fdsfdfdsfsd | 22 | 333| | | | 2010-02-26 04:27:34 |
+----------+-------------------+------------+-------------------+------+--------+--------+--------------+-------------+---------------------+
4 rows in set (0.00 sec)mysql>