本帖最后由 hujiefei 于 2012-11-07 15:33:01 编辑 测试数据
2012/11/4 16:15:49 369 2012/11/4 16:15:50 1 4
2012/11/4 16:29:39 370 2012/11/4 16:29:39 1 4
2012/11/4 16:30:31 371 2012/11/4 16:30:31 1 4
2012/11/4 16:30:40 372 2012/11/4 16:30:40 1 4
2012/11/4 16:34:09 373 2012/11/4 16:34:09 1 4
2012/11/4 16:35:41 374 2012/11/4 16:35:41 1 4
2012/11/7 14:32:33 388 2012/11/7 14:32:33 1 4 42
2012/11/7 14:32:52 389 2012/11/7 14:32:52 1 4 2
2012/11/7 14:33:14 390 2012/11/7 14:33:14 1 4 5
2012/11/7 15:06:42 391 2012/11/7 15:06:42 1 4 2
2012/11/7 15:15:18 392 2012/11/7 15:15:18 1 4 2
2012/11/7 15:15:26 393 2012/11/7 15:15:26 1 4 42
2012/11/7 15:15:45 394 2012/11/7 15:15:45 1 4 5
2012/11/7 15:15:48 395 2012/11/7 15:15:48 1 4 5
建表语句
CREATE TABLE `rule_setting` (
`RULE_SETTING_ID` bigint(20) NOT NULL AUTO_INCREMENT,
`CREATE_DATE` datetime DEFAULT NULL,
`CREATE_USER` bigint(20) DEFAULT NULL,
`EFFECTIVE_DATE` datetime DEFAULT NULL,
`RULE_CATEGORY_ID` int(11) DEFAULT NULL,
`ITEM_ID` bigint(20) DEFAULT NULL,
`BANK_ID` bigint(20) DEFAULT NULL,
`APPFORM_ID` int(11) DEFAULT NULL,
`BRANCH_ID` bigint(20) DEFAULT NULL,
PRIMARY KEY (`RULE_SETTING_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=396 DEFAULT CHARSET=utf8;
我写的sql 可是达不到我的要求
SELECT RS.EFFECTIVE_DATE,
RS.RULE_SETTING_ID,
RS.CREATE_DATE,
RS.CREATE_USER,
RS.RULE_CATEGORY_ID,
RS.ITEM_ID,
RS.BRANCH_ID
FROM RULE_SETTING RS
WHERE BANK_ID = 1
AND EFFECTIVE_DATE <= now()
AND RS.RULE_CATEGORY_ID = 4
And RS.BRANCH_ID is not null
order by rule_setting_id desc limit 0 ,1
我要得到的数据是
2012/11/7 15:15:26 393 2012/11/7 15:15:26 1 4 42
2012/11/7 15:15:48 395 2012/11/7 15:15:48 1 4 5
2012/11/7 15:15:18 392 2012/11/7 15:15:18 1 4 2
2012/11/4 16:35:41 374 2012/11/4 16:35:41 1 4
------最佳解决方案--------------------------------------------------------
SELECT RS.RULE_SETTING_ID,
RS.CREATE_DATE,
RS.CREATE_USER,
RS.RULE_CATEGORY_ID,
RS.ITEM_ID,
RS.BRANCH_ID
FROM RULE_SETTING RS,
(SELECT max(RULE_SETTING_ID) RULE_SETTING_ID
FROM RULE_SETTING
WHERE BANK_ID = '1'
AND EFFECTIVE_DATE <= now()
AND RULE_CATEGORY_ID = '4'