日期:2014-05-17 浏览次数:20897 次
SQL> select * from t1; ID NAME LEVEL_DATE SKILL_LEVEL ---------- ---- ------------------- ----------- 1 张三 2005-11-11 00:00:00 D 2 张三 2006-04-19 00:00:00 C 3 张三 2009-11-25 00:00:00 B 4 李四 2005-01-05 00:00:00 D 5 李四 2007-12-09 00:00:00 C 6 李四 2009-10-11 00:00:00 B 7 李四 2011-03-13 00:00:00 A 8 王五 2010-09-02 00:00:00 D 9 赵六 2007-08-01 00:00:00 D 10 赵六 2009-11-07 00:00:00 C 11 钱七 2003-03-04 00:00:00 D 12 钱七 2005-09-12 00:00:00 C 13 钱七 2007-11-18 00:00:00 B 14 钱七 2009-05-29 00:00:00 A 已选择14行。 SQL> WITH t AS 2 (SELECT * 3 FROM t1 4 WHERE t1.level_date < to_date('2010-12-31', 'yyyy-mm-dd hh24:mi:ss')) 5 SELECT skill_level || '-' || COUNT(*) SKILL_LEVEL 6 FROM t 7 WHERE NOT EXISTS (SELECT 1 8 FROM t b 9 WHERE b.name = t.name 10 AND b.skill_level < t.skill_level) 11 GROUP BY skill_level; SKILL_LEVEL ----------- D-1 B-2 C-1 A-1 SQL>