一个查询语句 求解答 在线等
本帖最后由 hujiefei 于 2012-11-07 15:33:01 编辑 测试数据
2012/11/4 16:15:493692012/11/4 16:15:5014
2012/11/4 16:29:393702012/11/4 16:29:3914
2012/11/4 16:30:313712012/11/4 16:30:3114
2012/11/4 16:30:403722012/11/4 16:30:4014
2012/11/4 16:34:093732012/11/4 16:34:0914
2012/11/4 16:35:413742012/11/4 16:35:4114
2012/11/7 14:32:333882012/11/7 14:32:331442
2012/11/7 14:32:523892012/11/7 14:32:52142
2012/11/7 14:33:143902012/11/7 14:33:14145
2012/11/7 15:06:423912012/11/7 15:06:42142
2012/11/7 15:15:183922012/11/7 15:15:18142
2012/11/7 15:15:263932012/11/7 15:15:261442
2012/11/7 15:15:453942012/11/7 15:15:45145
2012/11/7 15:15:483952012/11/7 15:15:48145
建表语句
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:263932012/11/7 15:15:261442
2012/11/7 15:15:483952012/11/7 15:15:48145
2012/11/7 15:15:183922012/11/7 15:15:18142
2012/11/4 16:35:413742012/11/4 16:35:4114
[最优解释]
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'
GROUP BY BRANCH_ID) a
WHERE RS.RULE_SETTING_ID = a.RULE_SETTING_ID
