常用sql

分组排序取第一条

SELECT soft_id,cn_name,packagename,version,version_code     
  FROM (SELECT ROW_NUMBER() OVER(PARTITION BY packagename ORDER BY version_code DESC) LEV,     
               t.*     
          FROM sdb_softinfo t )  
 WHERE LEV = 1 and soft_flag=1  and version_code is not null ORDER BY download_times DESC

使用Database Link

select id,pin,nick_name,name,city,mobile,email from pp_customer where id in (select id from user_img_log@db16to10 t group by id having count(img_code) > 5 )

统计当天数据

select count(*) from c_log where user_id = 1 and info_id = 1 and to_char(create_time,'yyyy-MM-dd')= to_char(sysdate,'yyyy-MM-dd')

添加约束

alter table user_img_log
add constraint UQ_pin_imgcode UNIQUE(pin,img_code)

搜索不区分大小写

keywords = new String(keywords.getBytes("iso8859-1"), "gbk");
                softinfo.setKeywords(keywords);
                strWhere += " and ( regexp_like(a.cn_name,'" + keywords + "','i') or regexp_like(a.en_name,'"
                        + keywords + "','i') ) ";