常用sql

最新推荐文章于 2025-11-26 14:12:29 发布

原创最新推荐文章于 2025-11-26 14:12:29 发布 · 112 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

oracle 专栏收录该内容

6 篇文章

订阅专栏

分组排序取第一条

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') ) ";