开发中常用的一些sql语句细节场景

判断某个字段是否为空用 is null 或者is not null

select * from sjgl_sj_sjjbxx where zhbs is null AND  isdel='0' //筛选出转换标识为空的未被逻辑删除的数据

查询工资排名3-5的员工id及其姓名

select * from
(select t1.*,rownum r
from (select * from employees order by salary desc) t1 )
where  r>=3 and r <=5;
 

//三张表关联查询

select ID, RYBH, AJBH, LRSJ, LRR_SFZH, TJXGSJ, TJXGR_SFZH, ISDEL, DATA_FLAG
        from gl_ragl  where  ISDEL = 0 and AJBH = 'A110223420000002019035007';
SELECT * from gg_rylbb WHERE rybh='R110223420000002019035010';
SELECT * from gg_xyryb WHERE rybh='R110223420000002019035010';

SELECT DISTINCT c.*,a.ajbh FROM gl_ragl a,gg_rylbb b,gg_xyryb c WHERE a.ajbh='A110223420000002019035007' AND a.rybh = b.rybh AND a.rybh=c.rybh
AND b.rylb='0202';

ALTER TABLE sjgl_sj_jfbl alter COLUMN tjmj type varchar (300); //修改表字段的默认长度