RT :CREATE TABLE `test` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`mydate` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) INSERT INTO `test` VALUES ('1', '2011-4-11 12:23:22');
INSERT INTO `test` VALUES ('2', '2011-4-11 12:21:22');
INSERT INTO `test` VALUES ('3', '2011-4-10 12:23:22');
INSERT INTO `test` VALUES ('4', '2011-4-10 12:18:22');
INSERT INTO `test` VALUES ('5', '2011-4-12 12:23:22');
INSERT INTO `test` VALUES ('6', '2011-4-11 11:23:22');
现在要统计出 每天的记录条数,如: 2011-4-11 有3条记录,2011-4-10有2条,2011-4-11有一条记录。
谢谢好心人帮忙咯
`id` int(4) NOT NULL AUTO_INCREMENT,
`mydate` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) INSERT INTO `test` VALUES ('1', '2011-4-11 12:23:22');
INSERT INTO `test` VALUES ('2', '2011-4-11 12:21:22');
INSERT INTO `test` VALUES ('3', '2011-4-10 12:23:22');
INSERT INTO `test` VALUES ('4', '2011-4-10 12:18:22');
INSERT INTO `test` VALUES ('5', '2011-4-12 12:23:22');
INSERT INTO `test` VALUES ('6', '2011-4-11 11:23:22');
现在要统计出 每天的记录条数,如: 2011-4-11 有3条记录,2011-4-10有2条,2011-4-11有一条记录。
谢谢好心人帮忙咯
from `test`
group by date(mydate)
select mid(mydate,1,9),count(*)
from `test`
group by mid(mydate,1,9)