sql - 查询

1 sql  查询重复数据

根据字段 id  name  查询出来 重复的数据
select * from table a
where (a.id,a.name) in (select id,name from table group by id,name having count(*) >= 2);

2 sql  查询 去除重复数据

根据 字段 id name  查询出来去除重复的数据 后的数据数量

select count(*) from (select distinct  id, name from table) ;

根据 字段 id name  查询出来去除重复的数据 后的数据

select * from (select distinct  id, name from table) ;


根据多个字段删除重复数据

delete from table where rowid not in
(select max(rowid) from table  group by(h1,h2));

3 sql  根据一个字段分组 根据另一个字段 排序

将表 table 中查出来的数据  根据age 分组 组内根据grade排序 将组内排序的序号 命名为VersionIndex 
select b.*,row_number()  over(partition by b.age order by b.grade)  VersionIndex from table b;

4 sql  查询数据并过滤

下面的两种sql 作用相同
 
查询各个管理职手下员工的最低工资，其中最低工资不蹦低于6000，没有过管理者的员工不计算在内

manager_id(管理者id) salary(工资) employees(员工表)

1 select manager_id ,min(salary) from employees where manager_id is not null group by manager_id having min(salary) >=6000


2 select manager_id ,min(salary) from employees group by manager_id having min(salary) >=6000 and manager_id is not null

5 sql  子查询 

查询 谁的工资 比Abel高

last_name（名字） salary （工资） employees （员工表）

select last_name,salary from employees where salary>(select salary from employees where last_name ='Abel')


单行 子查询有 = > >= < <= <>   例如：
select last_name,salary from employees where salary>(select salary from employees where last_name ='Abel')


多行子查询 in any  all 
返回其他部门中比job_id为“IT_PROG” 部门 任一 工资低的员工的 员工号 姓名 jon_id以及salary
select employee_id ,last _name ,job_id，salary from employees where job_id<> 'IT_PROG' and salary < any ( select salary from employees where job_id= 'IT_PROG')


返回其他部门中比job_id为“IT_PROG” 部门 所有 工资低的员工的 员工号 姓名 jon_id以及salary
select employee_id ,last _name ,job_id，salary from employees where job_id<> 'IT_PROG' and salary < all ( select salary from employees where job_id= 'IT_PROG')


查询平均工资最低的部门    注意 使用 组函数（avg  min）不能使用where  可以使用 having
department_id(部门id) 

select department_id from employees having avg(salary) = (select min(avg(salary))) from employees group by department_id)

查询平均工资最低的部门信息 
select * from departments where department_id = (select department_id from employees having avg(salary) = (select min(avg(salary))) from employees group by department_id))

6 分页

select * from (select rownum rn, a.*  from (select  * from table a ) a where rownum < 10) where rn >= 0;

7 多列子查询

查询 与141号 或 174号员工的manager_id 和 department_id相同的其他员工的employee_id,manager_id,department_id.

select employee_id,manager_id,department_id from employees e1 
where (manager_id,department_id) in (
                                      select manager_id,department_id
                                      from employees
                                      where employee_id in(141,174)
                                    )
and employee_id not in(141,174);

8 递归查询 

--子向父节点递归
select * from table start with id=1 connect by prior pid=id;

--父向子节点递归（不含根节点）
select * from table start with pid=1 connect by prior id=pid;

--父向子节点递归（含根节点）
select * from table start with id=1 connect by prior id=pid;


--也可以设置条件来查询（子向父） 例如：
select * from table where id = 1 start with id=id connect by prior pid=id;

--也可以根据查到未知id值来查询(子向父)
select a.id ,(select * from table where id = a.id start with id=id connect by prior pid=id) as pid from table;