/*
Navicat MySQL Data TransferSource Server : localhost
Source Server Version : 60002
Source Host : localhost:3306
Source Database : huadiTarget Server Type : MYSQL
Target Server Version : 60002
File Encoding : 65001Date: 2011-10-04 22:17:20
*/SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `template`
-- ----------------------------
DROP TABLE IF EXISTS `template`;
CREATE TABLE `template` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`tj_month` varchar(50) DEFAULT NULL,
`model` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of template
-- ----------------------------
INSERT INTO `template` VALUES ('1', '2010-01-12', 'BX101');
INSERT INTO `template` VALUES ('2', '2010-01-23', 'SD101');
INSERT INTO `template` VALUES ('3', '2010-03-19', 'OK101');
INSERT INTO `template` VALUES ('4', '2010-01-15', 'BX101');
INSERT INTO `template` VALUES ('5', '2010-03-28', 'RF101');
INSERT INTO `template` VALUES ('6', '2010-03-18', 'RF101');说明:分组统计月份,并筛选每组中数量最多的一个,即limit 0,1
mysql如何实现如下结果:
月份 型号 数量
2010-01 BX101 2
2010-03 RF101 2
Navicat MySQL Data TransferSource Server : localhost
Source Server Version : 60002
Source Host : localhost:3306
Source Database : huadiTarget Server Type : MYSQL
Target Server Version : 60002
File Encoding : 65001Date: 2011-10-04 22:17:20
*/SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `template`
-- ----------------------------
DROP TABLE IF EXISTS `template`;
CREATE TABLE `template` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`tj_month` varchar(50) DEFAULT NULL,
`model` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of template
-- ----------------------------
INSERT INTO `template` VALUES ('1', '2010-01-12', 'BX101');
INSERT INTO `template` VALUES ('2', '2010-01-23', 'SD101');
INSERT INTO `template` VALUES ('3', '2010-03-19', 'OK101');
INSERT INTO `template` VALUES ('4', '2010-01-15', 'BX101');
INSERT INTO `template` VALUES ('5', '2010-03-28', 'RF101');
INSERT INTO `template` VALUES ('6', '2010-03-18', 'RF101');说明:分组统计月份,并筛选每组中数量最多的一个,即limit 0,1
mysql如何实现如下结果:
月份 型号 数量
2010-01 BX101 2
2010-03 RF101 2
[征集]分组取最大N条记录方法征集,及散分....
select substring(tj_month,1,7) as mon ,model,count(*) as num
from template
group by substring(tj_month,1,7),model;mysql> select * from test A where not exists (select 1 from test where A.mon=mon
and A.num<num );
+---------+-------+-----+
| mon | model | num |
+---------+-------+-----+
| 2010-01 | BX101 | 2 |
| 2010-03 | RF101 | 2 |
+---------+-------+-----+
2 rows in set (0.00 sec)