去掉重复，取最大日期的数据，急用在线等，
tab
id cpbh XH sjrq
1 001 A-1 2011-02-09
2 001 A-2 2012-03-07
3 001 A-3 2012-06-20
4 002 B-1 2012-04-15
5 002 B-2 2012-06-01
...
TAB 表有5000多条数据，cpbh 有重复，现在只取最大日期的，结果如下
id cpbh XH sjrq
3 001 A-3 2012-06-20
5 002 B-2 2012-06-01
...
请高手指点，谢谢
[解决办法]
SELECT * FROM TB T WHERE sjrq =(SELECT MAX(sjrq ) FROM TB WHERE cpbh =t.cpbh )
[解决办法]
SELECT *
FROM tab t
where not exists (select 1 from tab where cpbh=t.cpbh and sjrq>t.sjrq)
[解决办法]
很难吗？
WITH tab (id, cpbh ,XH ,sjrq ) AS (SELECT 1, '001', 'A-1' ,'2011-02-09' UNION ALL SELECT 2 ,'001', 'A-2', '2012-03-07' UNION ALL SELECT 3 ,'001','A-3', '2012-06-20' UNION ALL SELECT 4 ,'002', 'B-1', '2012-04-15' UNION ALL SELECT 5 ,'002', 'B-2', '2012-06-01') SELECT * FROM TAB a WHERE EXISTS (SELECT 1 FROM (SELECT MAX(sjrq) sjrq ,cpbh FROM TAB GROUP BY cpbh)b WHERE a.sjrq=b.sjrq AND a.cpbh=b.cpbh) /* id cpbh XH sjrq ----------- ---- ---- ---------- 3 001 A-3 2012-06-20 5 002 B-2 2012-06-01 (2 行受影响) */
