11.1- 数据库-SQL

最新推荐文章于 2025-03-23 10:13:58 发布

apriaaaa

最新推荐文章于 2025-03-23 10:13:58 发布

阅读量127

点赞数

CC 4.0 BY-SA版权

分类专栏：个人笔记

本文链接：https://blog.youkuaiyun.com/apriaaaa/article/details/79624134

个人笔记专栏收录该内容

124 篇文章

订阅专栏

本文介绍了复杂的SQL查询技巧，包括如何查找与特定员工具有相同管理者和部门的其他员工，筛选出薪资高于本部门平均水平的员工，以及根据部门ID确定员工所在位置等。还展示了如何使用伪表DUAL进行数据插入。

1. dual 确实是一张表.是一张只有一个字段,一行记录的表. 
2.习惯上,我们称之为'伪表'.因为他不存储主题数据.
3. 他的存在,是为了操作上的方便.因为select 都是要有特定对象的.

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

[ 方式一]
select employee_id,manager_id,department_id
from employees
where manager_id in
    (select manager_id
    from employees
    where employee_id in (174,141))
    and
    department_id in (
        select department_id 
        from employees
        where employee_id in (174,141))
        and
        employee_id not in(174,141);
[ 方式二]
select employee_id,manager_id,department_id
from employees
where(manager_id,department_id)
    in
    (
    select manager_id,department_id
    from employees
    where employee_id in (141,174))
    and
    employee_id not in (174,141);




的 返回比本部门平均工资高的员工的 last_name, department_id, 
salary

SELECT a.last_name, a.salary,
a.department_id, b.salavg
FROM employees a,

 (SELECT department_id,
AVG(salary) salavg
FROM employees
GROUP BY department_id) b

 WHERE a.department_id = b.department_id
AND a.salary > b.salavg;

的显式员工的 employee_id,last_name 和和 location 。其中，若员工
department_id 与与 location_id 为为 1800 的的 department_id 相同，则
location 为’Canada’, 其余则为’USA’ 。

SELECT employee_id, last_name,
(CASE department_id
WHEN (SELECT department_id FROM departments
WHERE location_id = 1800)
THEN 'Canada' ELSE 'USA' END) location
FROM employees;

employees 表中 employee_id 与 job_history  表中 employee_id 
于 相同的数目不小于 2 ，输出这些相同 id  的员工的 employee_id,last_name 
其 和其 job_id

select e.employee_id ,last_name,e.job_id
from employees e
    where 2<=(select count(*)
        from job_history
        where employee_id = e.employee_id
    );

insert into demo_score select * from (
select to_date('2008-8-8','yyyy-MM-dd'),'拜仁','胜' from dual
union
select to_date('2008-8-9','yyyy-MM-dd'),'奇才','胜' from dual
union
select to_date('2008-8-9','yyyy-MM-dd'),'湖人','胜'from dual
union
select to_date('2008-8-10','yyyy-MM-dd'),'拜仁','负' from dual
union
select to_date('2008-8-8','yyyy-MM-dd'),'拜仁','胜' from dual
union
select to_date('2008-8-12','yyyy-MM-dd'),'奇才','胜' from dual
)

select Name, SUM(胜)as "胜",SUM(负) as "负" from
(
select Name ,(case score when '胜' then 1 else 0 end)as "胜",(case score when '负' then 1 else 0 end)as "负"
from demo_score )

group by Name