select * from stu limit m,n; // m=(pageIndex-1)*pageSize,n=pageSize -- 返回总条,查询表添加字段sql_calc_found_rows select sql_calc_found_rows a.* from AAA a limit m,n -- found_rows单独查询总条数 select found_rows total;
select * from( select rownum rn,a.*,count(*) over() total from table_name a where rownum <= x -- 结束行,x=pageIndex*pageSize ) where rn >= y; -- 起始行,y=(pageIndex-1)*pageSize+1 -- 返回总条数 select count(*) over() total from AAA
-- connect by: 指定父子行的条件关系 -- start with: 指定起始节点的条件 select c.bc_id,c.bc_name from org_busicorp c connect by prior c.bc_pid = c.bc_id start with c.bc_id = '001';
-- 二三级查询 select c1.bc_id,c1.bc_name from org_busicorp c1 where c1.bc_pid = '00' union select c2.bc_id,c2.bc_name from org_busicorp c2 left join org_busicorp c1 on c1.bc_id = c2.bc_pid where c1.bc_pid = '00' -- 包含当前id union select c3.bc_id,c3.bc_name from org_busicorp c3 where c3.bc_id = '00'
Mysql递归函数
SELECT ID.LEVEL, DATA.* FROM ( SELECT @ids AS _ids, ( SELECT @ids := GROUP_CONCAT( id ) FROM 表名 WHERE FIND_IN_SET( 父级 id字段, @ids ) ) AS cids, @l := @l + 1 AS LEVEL FROM 表名, ( SELECT @ids := '条件id', @l := 0 ) b WHERE @ids IS NOT NULL ) ID, 表名 DATA WHERE FIND_IN_SET( DATA.id, ID._ids ) ORDER BY LEVEL, id
Mysql插入数据存在修改、不存在新增
insert into `subject`(subjectId,subjectName) values('7','离散') on duplicate key update subjectName='离散数学';
Oracle插入数据存在修改、不存在新增
merge into 目标表 a using 源表 b on(a.条件字段1=b.条件字段1 and a.条件字段2=b.条件字段2 ……) when matched then update set a.字段=b.字段 --目标表别称a和源表别称b都不要省略 when not matched then insert (a.字段1,a.字段2……)values(b.字段1,b.字段2……) --目标表别称a可省略,源表别称b不可省略
源表b可能是一张表结构不同于a的表,有可能是一张构建相同表结构的临时表,也有可能是我们自己组起来的数据
merge into student a using (select '7' as id from dual) s on (a.id = s.id) when matched then update set a.student_name = '小明二号' when not matched then insert (id, student_name, fk_class) values ('7', '小明', '2')