SQL集锦

分组排序取第一条(根据file1分组，file2排序 取第一条记录)-------------------------------------

   SELECT file1, file2, file3  FROM (SELECT ROW_NUMBER() OVER(PARTITION BY file1 ORDER BY file2 DESC) RN, t1.*   FROM t1)   WHERE RN = 1;

查询树形结构(单位表bt_corp,单位代码corp_id，父单位代码super_corp_code)

SELECT BC.CORP_CODE,
       BC.SUPER_CORP_CODE,
       BC.CORP_NAME,
       LEVEL,
       SYS_CONNECT_BY_PATH(BC.CORP_CODE, ' >> ')
  FROM BT_CORP BC
 WHERE BC.VALID_SIGN = 1
 START WITH BC.SUPER_CORP_CODE IS NULL
CONNECT BY NOCYCLE PRIOR BC.CORP_CODE = BC.SUPER_CORP_CODE

参考:http://blog.csdn.net/Huozhiwu_11/article/details/46459157