牛客網資料庫SQL實戰剖析(11-20)

来源:https://www.cnblogs.com/shuiyj/archive/2020/04/07/12656302.html
-Advertisement-
Play Games

11. 獲取所有員工當前的manager 獲取所有員工當前的manager，如果當前的manager是自己的話結果不顯示，當前表示to_date='9999 01 01'。結果第一列給出當前員工的emp_no,第二列給出其manager對應的manager_no。 sql CREATE TABLE ...


11. 獲取所有員工當前的manager

獲取所有員工當前的manager，如果當前的manager是自己的話結果不顯示，當前表示to_date='9999-01-01'。結果第一列給出當前員工的emp_no,第二列給出其manager對應的manager_no。

CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`)
);

CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`)
);

解析：首先要查詢員工的領導，只需要dept_emp左連接dept_manager。此外還有兩個條件，當前的manager是自己的話結果不顯示，以及to_date='9999-01-01'，將其寫成 where 子句。

select e.emp_no, m.emp_no as manager_no
from dept_emp e
         left join dept_manager m on e.dept_no = m.dept_no
where e.to_date = '9999-01-01'
  and m.to_date = '9999-01-01'
  and e.emp_no != m.emp_no;

12. 獲取所有部門中當前員工薪水最高的相關信息

獲取所有部門中當前員工薪水最高的相關信息，給出dept_no, emp_no以及其對應的salary。

CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

解題思路：首先按照部門進行分組，然後用 MAX() 函數獲取部門中工資最大值。

select d.dept_no, s.emp_no, MAX(s.salary) AS salary
from dept_emp d
         join salaries s on d.emp_no = s.emp_no
where s.to_date = '9999-01-01'
  and d.to_date = '9999-01-01'
GROUP BY d.dept_no;

13. 從titles表獲取按照title進行分組

從titles表獲取按照title進行分組，每組個數大於等於2，給出title以及對應的數目t。

CREATE TABLE IF NOT EXISTS "titles" (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);

解題思路：分組使用group by，分組之後還要判斷每組個數，則用 having count(*)。

select title, count(*) AS t
from titles
group by title
having t >= 2;

14. 從titles表獲取按照title進行分組，註意對於重覆的emp_no進行忽略。

從titles表獲取按照title進行分組，每組個數大於等於2，給出title以及對應的數目t。
註意對於重覆的emp_no進行忽略。

CREATE TABLE IF NOT EXISTS `titles` (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);

解題思路：在13題基礎上，原來是統計整個組的格式，本題要根據 emp_no 進行去重，所以使用 count(DISTINCT emp_no)。

select title, count(DISTINCT emp_no) AS t
from titles
group by title
having t >= 2;

15. 查找employees表

查找employees表所有emp_no為奇數，且last_name不為Mary的員工信息，並按照hire_date逆序排列

CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

解題思路：這道題思路很直接，emp_no為奇數，即取餘之後不等於0，last_name != 'Mary'，再用order by xxx DESC逆序排列。

select * from employees
where emp_no%2 != 0 AND last_name != 'Mary'
order by hire_date DESC ;

16. 統計出當前各個title類型對應的員工當前薪水對應的平均工資

統計出當前各個title類型對應的員工當前（to_date='9999-01-01'）薪水對應的平均工資。結果給出title以及平均工資avg。

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

CREATE TABLE IF NOT EXISTS "titles" (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);

解題思路：這道題和第12題是一個意思，先通過 title 分組，然後用 AVG() 求工資平均值。

select t.title, AVG(s.salary)
from titles t
join salaries s on t.emp_no = s.emp_no
where t.to_date='9999-01-01' and s.to_date='9999-01-01'
group by t.title;

17. 獲取當前薪水第二多的員工的emp_no以及其對應的薪水salary

獲取當前（to_date='9999-01-01'）薪水第二多的員工的emp_no以及其對應的薪水salary

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

解題思路：這道題前面有類似的，在子查詢裡面，通過排序查出第二多的薪水。然後查詢該薪水的員工。

select emp_no, salary
from salaries
where salary = (
    select salary
    from salaries
    where to_date = '9999-01-01'
    order by salary DESC
    LIMIT 1,1
);

但看輸出樣例只有一行記錄，感覺題目本意應該是薪水第二多的人只有一個，讓我們直接查詢之後用薪水進行排序，再逆序查到這個人。

select emp_no, salary
from salaries
order by salary DESC
LIMIT 1,1;

兩種都能AC，第二種應該是題目想要我們給出的答案。

18. 獲取當前薪水第二多的員工的emp_no以及其對應的薪水salary，不准使用order by

查找當前薪水(to_date='9999-01-01')排名第二多的員工編號emp_no、薪水salary、last_name以及first_name，不准使用order by

CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

解題思路：薪水第二多的，就是除掉薪水最多的數據之後，新數據集中，薪水最多的。說法有點繞，看SQL就能看明白。

SELECT e.emp_no, MAX(s.salary) AS salary, e.last_name, e.first_name
FROM employees e
         JOIN salaries s
              ON e.emp_no = s.emp_no
WHERE s.to_date = '9999-01-01'
  AND s.salary < (SELECT MAX(salary) FROM salaries WHERE to_date = '9999-01-01');

19. 查找所有員工的last_name和first_name以及對應的dept_name

查找所有員工的last_name和first_name以及對應的dept_name，也包括暫時沒有分配部門的員工

CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`dept_no`));

CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));

CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

解題思路：本題考察三個表的關聯查詢，departments 和employees 通過中間表dept_emp 關聯在一起。

select e.last_name, e.first_name, d.dept_name
from employees e
left join dept_emp de on e.emp_no = de.emp_no
left join departments d on de.dept_no = d.dept_no;

20. 查找員工編號emp_no為10001其自入職以來的薪水salary漲幅值growth

查找員工編號emp_no為10001其自入職以來的薪水salary漲幅值growth

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

解題思路：漲幅即最大值-最小值，簡單這樣理解即可。

select MAX(salary)-MIN(salary) AS growth
from salaries
where emp_no=10001;

您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 047.集群管理-資源及配額管理
    一 資源管理 1.1 資源調度機制 對於Kubernetes資源，有兩個重要參數：CPU Request與Memory Request。 通常在定義Pod時並沒有定義這兩個參數，此時Kubernetes會認為該Pod所需的資源很少，並可以將其調度到任何可用的Node上。因此，當集群中的計算資源不很充 ...
  • Nginx 的配置指令
    我們已經瞭解了 Nginx 的基本命令和架構原理，下麵該到最讓人頭疼也是最不容易理解的部分了，那就是 nginx.conf 這個配置文件，下麵從 Nginx 的指令開始，一步步來講解 Nginx 的配置。 Nginx 指令 先來看一個典型的 Nginx 配置文件示例。 從上面可以看到，這個配置文件中 ...
  • 【Redis】集群教程（Windows）
    Redis配置集群教程，介紹Redis集群數據分片，節點通訊，然後準備環境，搭建集群，測試集群使用 ...
  • mysql yum源安裝
    部署伺服器環境的時候經常要安裝mysql,以下是常見的安裝方式 源碼安裝 rpm包安裝 yum源安裝 這篇主要介紹yum源安裝。 yum源下載 進入 https://dev.mysql.com/downloads/ 頁面，可以看到有很多的下載項，我們選擇 MySQL Yum Repository(對 ...
  • SpringBoot整合Redis
    依賴 <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-redis</artifactId> </dependency> 配置文件 #使用springboot，未 ...
  • redis 入門一
    一、redis 簡介 1、什麼是 redis？ redis 是一個開源免費的高性能的 key - value 資料庫。 支持數據持久化，即可以將記憶體的數據存儲在磁碟中，重啟服務後可以再次載入磁碟中的數據並使用。 支持多種類型，比如：String（字元串）、List（列表）、Set（集合）、zset（ ...
  • MySQL 遞歸查詢實踐總結
    MySQL複雜查詢使用實例 By:授客 QQ：1033553122 表結構設計 SELECT id, `name`, parent_id FROM `tb_testcase_suite` 說明： parent_id值關聯表自身id列的值，如果其值為-1，則表示該記錄不存在父級記錄，否則表示該記錄存在 ...
  • Sqlserver 報錯“參數數據類型 ntext/text 對於 replace 函數的參數 1 無效”的解決方案及原理分析擴展
    原因：在數據查詢中replace函數無法對錶table中text/ntext類型的欄位colname進行了字元串操作。 解決方法：將text當作varchar（實際內容長度低於8000位元組時）或把ntext當作nvarchar（實際內容長度低於4000位元組時）。 但是當text欄位內容長度超過800 ...
一周排行
    -Advertisement-
    Play Games
  • WPF 已知問題 監聽 WMI 事件導致觸摸失效
    示例項目結構 在 Visual Studio 中創建一個 WinForms 應用程式後，項目結構如下所示： MyWinFormsApp/ │ ├───Properties/ │ └───Settings.settings │ ├───bin/ │ ├───Debug/ │ └───Release/ ...
  • [STAThread]特性
    [STAThread] 特性用於需要與 COM 組件交互的應用程式，尤其是依賴單線程模型（如 Windows Forms 應用程式）的組件。在 STA 模式下，線程擁有自己的消息迴圈，這對於處理用戶界面和某些 COM 組件是必要的。 [STAThread] static void Main(stri ...
  • UWP WinUI3 傳入 AddHandler 的 RoutedEventHandler 類型與事件所需不匹配將拋出參數異常
    在WinForm中使用全局異常捕獲處理 在WinForm應用程式中，全局異常捕獲是確保程式穩定性的關鍵。通過在Program類的Main方法中設置全局異常處理，可以有效地捕獲並處理未預見的異常，從而避免程式崩潰。 註冊全局異常事件 [STAThread] static void Main() { / ...
  • .NET 多版本 WinForm 開源控制項庫 SunnyUI
    前言 給大家推薦一款開源的 Winform 控制項庫，可以幫助我們開發更加美觀、漂亮的 WinForm 界面。 項目介紹 SunnyUI.NET 是一個基於 .NET Framework 4.0+、.NET 6、.NET 7 和 .NET 8 的 WinForm 開源控制項庫，同時也提供了工具類庫、擴展 ...
  • .net core8 使用Swagger（附當前源碼）
    說明 該文章是屬於OverallAuth2.0系列文章，每周更新一篇該系列文章（從0到1完成系統開發）。 該系統文章，我會儘量說的非常詳細，做到不管新手、老手都能看懂。 說明：OverallAuth2.0 是一個簡單、易懂、功能強大的許可權+可視化流程管理系統。 有興趣的朋友，請關註我吧(*^▽^*) ...
  • WPF 什麼時候 VisualTreeHelper.GetDescendantBounds 將返回無窮大
    一、下載安裝 1.下載git 必須先下載並安裝git，再TortoiseGit下載安裝 git安裝參考教程：https://blog.csdn.net/mukes/article/details/115693833 2.TortoiseGit下載與安裝 TortoiseGit，Git客戶端，32/6 ...
  • C# 開源教程帶你輕鬆掌握數據結構與演算法
    前言 在項目開發過程中，理解數據結構和演算法如同掌握蓋房子的秘訣。演算法不僅能幫助我們編寫高效、優質的代碼，還能解決項目中遇到的各種難題。 給大家推薦一個支持C#的開源免費、新手友好的數據結構與演算法入門教程：Hello演算法。 項目介紹 《Hello Algo》是一本開源免費、新手友好的數據結構與演算法入門 ...
  • 第22篇 生成proto文件bat腳本
    1.生成單個Proto.bat內容 @rem Copyright 2016, Google Inc. @rem All rights reserved. @rem @rem Redistribution and use in source and binary forms, with or with ...
  • 記一次 .NET某上位機視覺程式 卡死分析
    一：背景 1. 講故事 前段時間有位朋友找到我，說他的窗體程式在客戶這邊出現了卡死，讓我幫忙看下怎麼回事？dump也生成了，既然有dump了那就上 windbg 分析吧。 二：WinDbg 分析 1. 為什麼會卡死 窗體程式的卡死，入口門檻很低，後續往下分析就不一定了，不管怎麼說先用 !clrsta ...
  • 醫學DICOM文件與PACS系統進行數據傳輸
    前言 人工智慧時代，人臉識別技術已成為安全驗證、身份識別和用戶交互的關鍵工具。 給大家推薦一款.NET 開源提供了強大的人臉識別 API，工具不僅易於集成，還具備高效處理能力。 本文將介紹一款如何利用這些API，為我們的項目添加智能識別的亮點。 項目介紹 GitHub 上擁有 1.2k 星標的 C# ...
所有分類