测试数据:
CREATE TABLE IF NOT EXISTS `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`NUM` int(11) NOT NULL DEFAULT '1',
`createdate` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;INSERT INTO `users` (`id`, `NUM`, `createdate`) VALUES
(1, 1, '2011-10-01 16:50:42'),
(2, 1, '2011-10-03 16:50:54'),
(9, 1, '2011-10-11 17:29:36'),
(10, 1, '2011-10-11 17:29:36'),
(6, 1, '2011-10-05 16:51:26'),
(7, 1, '2011-10-10 16:51:35'),
(8, 1, '2011-10-24 16:51:42');测试SQL:
SELECT SUM( NUM ) AS q, DATE_FORMAT( createdate, '%Y-%m-%w' ) AS b
FROM users
WHERE createdate
BETWEEN '2011-10-03'
AND ' 2011-10-10'
GROUP BY DATE_FORMAT( createdate, '%Y-%m-%w' )
LIMIT 0 , 30
问题就是 我显示的时候如何 显示日期,就是比如10月份第一周是2011-10-02~2011-10-08就显示 q b
1 2011-10-02~2011-10-08
.....而不像上面我 查询出来的那样
CREATE TABLE IF NOT EXISTS `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`NUM` int(11) NOT NULL DEFAULT '1',
`createdate` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;INSERT INTO `users` (`id`, `NUM`, `createdate`) VALUES
(1, 1, '2011-10-01 16:50:42'),
(2, 1, '2011-10-03 16:50:54'),
(9, 1, '2011-10-11 17:29:36'),
(10, 1, '2011-10-11 17:29:36'),
(6, 1, '2011-10-05 16:51:26'),
(7, 1, '2011-10-10 16:51:35'),
(8, 1, '2011-10-24 16:51:42');测试SQL:
SELECT SUM( NUM ) AS q, DATE_FORMAT( createdate, '%Y-%m-%w' ) AS b
FROM users
WHERE createdate
BETWEEN '2011-10-03'
AND ' 2011-10-10'
GROUP BY DATE_FORMAT( createdate, '%Y-%m-%w' )
LIMIT 0 , 30
问题就是 我显示的时候如何 显示日期,就是比如10月份第一周是2011-10-02~2011-10-08就显示 q b
1 2011-10-02~2011-10-08
.....而不像上面我 查询出来的那样
http://topic.csdn.net/u/20110729/15/74b13cab-ed00-4730-af45-ba3e7c857a7b.html
rom users group by dayofweek(createdate);
+----------+---------------------------------------------------------+
| min(num) | concat(date(min(createdate)),'~',date(max(createdate))) |
+----------+---------------------------------------------------------+
| 1 | 2011-10-03~2011-10-24 |
| 1 | 2011-10-11~2011-10-11 |
| 1 | 2011-10-05~2011-10-05 |
| 1 | 2011-10-01~2011-10-01 |
+----------+---------------------------------------------------------+
4 rows in set (0.00 sec)