是这样的,
我的数据库 表A
有下列字段
id 字段
name varchar
type int
createtime varchar 格式'2011-03-05 10:10:25'
type 有 1 2 3 4 5种
现在我想查询的是type为 2 5 3的 各 两条 记录,根据时间降冪排序
我的数据库 表A
有下列字段
id 字段
name varchar
type int
createtime varchar 格式'2011-03-05 10:10:25'
type 有 1 2 3 4 5种
现在我想查询的是type为 2 5 3的 各 两条 记录,根据时间降冪排序
from tb A
where type in (2,5,3) and 2>(select count(*) from tb where A.type=type and A.createtime<createtime)
order by type,createtime desc
CREATE TABLE `test` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`type` int(10) DEFAULT NULL,
`createTime` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `test` VALUES ('1', 'yang', '1', '2010-08-27 04:15:44');
INSERT INTO `test` VALUES ('2', 'yang1', '4', '2010-08-27 04:18:52');
INSERT INTO `test` VALUES ('3', 'yang2', '1', '2010-08-27 04:20:33');
INSERT INTO `test` VALUES ('4', 'yang3', '1', '2010-08-27 04:23:52');
INSERT INTO `test` VALUES ('5', 'yang4', '3', '2010-08-27 04:27:23');
INSERT INTO `test` VALUES ('6', 'yang5', '1', '2010-08-27 04:29:38');
INSERT INTO `test` VALUES ('7', 'yang', '5', '2010-08-27 04:31:46');
INSERT INTO `test` VALUES ('8', '7', '1', '2010-08-27 07:01:42');
INSERT INTO `test` VALUES ('9', 'yang7', '3', '2010-08-27 08:36:33');
INSERT INTO `test` VALUES ('10', 'yang', '1', '2010-08-27 09:55:24');
INSERT INTO `test` VALUES ('11', 'yang8', '2', '2010-08-27 10:30:34');
INSERT INTO `test` VALUES ('12', 'yang', '1', '2010-08-27 11:44:12');
INSERT INTO `test` VALUES ('13', 'yang', '4', '2010-08-28 12:59:16');
INSERT INTO `test` VALUES ('14', 'yang8', '5', '2010-08-28 05:53:15');
INSERT INTO `test` VALUES ('15', 'yang8', '1', '2010-08-28 07:54:21');
INSERT INTO `test` VALUES ('16', 'yang', '1', '2010-08-28 04:16:01');
INSERT INTO `test` VALUES ('17', 'yang', '4', '2010-08-28 04:33:41');
INSERT INTO `test` VALUES ('18', 'yang', '1', '2010-08-28 04:51:06');
INSERT INTO `test` VALUES ('19', 'yang', '4', '2010-08-28 05:06:30');
INSERT INTO `test` VALUES ('20', 'yang', '2', '2010-08-28 05:35:05');
20 yang 2 2010-08-28 05:35:05
14 yang8 5 2010-08-28 05:53:15
11 yang8 2 2010-08-27 10:30:34
9 yang7 3 2010-08-27 08:36:33
7 yang 5 2010-08-27 04:31:46
5 yang4 3 2010-08-27 04:27:23
type = 2 5 3的各要两条
+----+-------+------+---------------------+
| id | name | type | createTime |
+----+-------+------+---------------------+
| 1 | yang | 1 | 2010-08-27 04:15:44 |
| 2 | yang1 | 4 | 2010-08-27 04:18:52 |
| 3 | yang2 | 1 | 2010-08-27 04:20:33 |
| 4 | yang3 | 1 | 2010-08-27 04:23:52 |
| 5 | yang4 | 3 | 2010-08-27 04:27:23 |
| 6 | yang5 | 1 | 2010-08-27 04:29:38 |
| 7 | yang | 5 | 2010-08-27 04:31:46 |
| 8 | 7 | 1 | 2010-08-27 07:01:42 |
| 9 | yang7 | 3 | 2010-08-27 08:36:33 |
| 10 | yang | 1 | 2010-08-27 09:55:24 |
| 11 | yang8 | 2 | 2010-08-27 10:30:34 |
| 12 | yang | 1 | 2010-08-27 11:44:12 |
| 13 | yang | 4 | 2010-08-28 12:59:16 |
| 14 | yang8 | 5 | 2010-08-28 05:53:15 |
| 15 | yang8 | 1 | 2010-08-28 07:54:21 |
| 16 | yang | 1 | 2010-08-28 04:16:01 |
| 17 | yang | 4 | 2010-08-28 04:33:41 |
| 18 | yang | 1 | 2010-08-28 04:51:06 |
| 19 | yang | 4 | 2010-08-28 05:06:30 |
| 20 | yang | 2 | 2010-08-28 05:35:05 |
+----+-------+------+---------------------+
20 rows in set (0.08 sec)mysql> select *
-> from test a
-> where 2>(select count(*) from test where type=a.type and createTime>a.createTime)
-> and type in (2,5,3)
-> order by id desc;
+----+-------+------+---------------------+
| id | name | type | createTime |
+----+-------+------+---------------------+
| 20 | yang | 2 | 2010-08-28 05:35:05 |
| 14 | yang8 | 5 | 2010-08-28 05:53:15 |
| 11 | yang8 | 2 | 2010-08-27 10:30:34 |
| 9 | yang7 | 3 | 2010-08-27 08:36:33 |
| 7 | yang | 5 | 2010-08-27 04:31:46 |
| 5 | yang4 | 3 | 2010-08-27 04:27:23 |
+----+-------+------+---------------------+
6 rows in set (0.00 sec)mysql>
[征集]分组取最大N条记录方法征集,及散分....
where type in (2,5,3) and 2>(select count(*) from test where a.type=type and a.createTime<createTime)
order by id desc;
from tb A
where type in (2,5,3) and 2>(select count(*) from tb where A.type=type and A.createtime<createtime)
order by type,createtime desc
顶顶顶