mysql 按周查询时间显示问题

[t240034137]

测试数据：
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
.....

而不像上面我 查询出来的那样

[shine333]

参考这个帖子
http://topic.csdn.net/u/20110729/15/74b13cab-ed00-4730-af45-ba3e7c857a7b.html

[rucypli]

mysql> select min(num),concat(date(min(createdate)),'~',date(max(createdate))) f
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)

[adzhiming]

来学习的