牛客網資料庫SQL實戰剖析(1-10)

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

1. 查找最晚入職員工的所有信息 sql CREATE TABLE ( int(11) NOT NULL, date NOT NULL, varchar(14) NOT NULL, varchar(16) NOT NULL, char(1) NOT NULL, date NOT NULL, PRIM ...


1. 查找最晚入職員工的所有信息

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`));

解題思路：根據入職時間倒序排序order by ... DESC，然後再取一條記錄，就是最晚入職的員工。

select * from employees 
order by hire_date DESC limit 1;

這樣做有一個問題，hire_date是 date 類型，很有可能多條記錄中是同一個時間入職的，所以說時間類型還是用時間戳比較精切。

來源:https://www.cnblogs.com/Jie-Jack/p/3793304.html

針對這道題目可以使用 MAX() 函數，然後用一個子查詢。

select * from employees
where hire_date = (select MAX(hire_date) FROM employees);

MAX()先查詢出最晚入職的時間，然後再查詢出在最晚時間入職的所有員工。

2. 查找入職員工時間排名倒數第三的員工所有信息

查找入職員工時間排名倒數第三的員工所有信息

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`));

解題思路：查詢入職時間倒數第三的，要先進行排序，再取三條記錄。MySQL中的結果集預設以正序排列，要逆序排列就要使用 DESC，取第三條則是使用limit。

select * from employees
order by hire_date DESC 
limit 2,1;

3. 查找當前薪水詳情以及部門編號dept_no

查找各個部門當前(to_date='9999-01-01')領導當前薪水詳情以及其對應部門編號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`));

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`));

解題思路：這是一個等值連接的題目，用員工編號emp_no 做關聯即可。

select s.*, d.dept_no
from salaries s
join dept_manager d on d.emp_no = s.emp_no
where d.to_date='9999-01-01';

上面的SQL有一個優化的點，就是可以用 s.to_date='9999-01-01' 再進行一次去重鎖定。修改之後的 SQL 如下所示：

select s.*, d.dept_no
from salaries s
join dept_manager d on d.emp_no = s.emp_no
where d.to_date='9999-01-01'  and s.to_date='9999-01-01';

吐槽：牛客網裡，表 dept_manager 用兩個字母的別名 dm，竟然過不了...

4. 查找所有已經分配部門的員工的last_name和first_name以及dept_no

查找所有已經分配部門的員工的last_name和first_name以及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`));

解題思路：這道題開始想到用一個子查詢，先查詢出所有部門id，作為條件去查詢員工表。然後看到還需要查詢出部門表裡的dept_no ，所以覺得用外連接更加合適，用dept_emp 表作為主表進行查詢。

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

5. 查找所有員工的last_name和first_name以及對應部門編號dept_no

查找所有員工的last_name和first_name以及對應部門編號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`));

解題思路：這個和上一題一個意思，只是這題里，沒有部門號的也要查詢出來，即查詢所有員工，以員工表作為主表。

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

6. 查找所有員工入職時候的薪水情況

查找所有員工入職時候的薪水情況，給出emp_no以及salary， 並按照emp_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`));

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`));

解題思路：這道題要考慮到，員工的入職時的薪水情況，即他入職第一個月時的薪水，即employees.​hire_date=salaries.​from_date​​。

select e.emp_no, s.salary
from employees e
join salaries s on e.emp_no=s.emp_no and e.hire_date = s.from_date
order by e.emp_no DESC;

7. 查找薪水漲幅超過15次的員工號emp_no以及其對應的漲幅次數t

查找薪水漲幅超過15次的員工號emp_no以及其對應的漲幅次數t

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`)
);

解題思路：看到這道題第一反應是，這道題也太複雜了。因為薪水漲幅需要比較前後兩個月的薪水，如果薪水下降是不能算漲幅的。所以，用解題的思路，我覺得這道題不會這麼複雜，就大膽假設它預設一直漲薪的。

從應試的角度，我覺得它是要考察 group by...having...，也就是先通過 emp_no 進行分組，再過濾分組記錄數大於15的記錄。

select emp_no, count(emp_no) as t
from salaries
group by emp_no
having t > 15;

果然是這樣。不用糾結這道題的題意，知道考察點在group by...having...就行了。

MySQL having子句

8. 找出所有員工當前薪水salary情況

找出所有員工當前(to_date='9999-01-01')具體的薪水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`));

解題思路：這道題講到了，相同的薪水只顯示一次，就是讓我們用 DISTINCT 去重。

select DISTINCT salary
from salaries
where to_date='9999-01-01'
order by salary DESC;

9. 獲取所有部門當前manager的當前薪水情況，給出dept_no, emp_no以及salary，當前表示to_date='9999-01-01'

獲取所有部門當前manager的當前薪水情況，給出dept_no, emp_no以及salary，當前表示to_date='9999-01-01'

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`));

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`));

解題思路：這道題先看表結構，emp_no,dept_no是dept_manager 表的主鍵，這就表示一個部門可能有多個領導，然後dept_manager 去關聯salaries 表查詢工資即可，限制條件是查詢當天的工資 s.to_date='9999-01-01';。

select d.dept_no, d.emp_no, s.salary
from dept_manager d
join salaries s on d.emp_no = s.emp_no
where s.to_date='9999-01-01';

但是我這樣寫，竟然AC不過，導入數據後發現，查詢結果是這樣的，意思就是一個部門的一個領導，在同一天會有多份工資？？

d001	10002	72527
d001	10002	72527
d001	10002	72527
d001	10002	72527
d001	10002	72527
d001	10002	72527
d004	10004	40054
d004	10004	42283
d004	10004	42542
d004	10004	46065
d004	10004	48271
d004	10004	50594
d004	10004	52119
d004	10004	54693
d004	10004	58326

要再加一個查詢條件d.to_date='9999-01-01'，看討論說是表示在職的經理。

select d.dept_no, d.emp_no, s.salary
from dept_manager 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';

10. 獲取所有非manager的員工emp_no

獲取所有非manager的員工emp_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`));

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`));

解題思路：查找不是 Manager 的員工，即 employees 左連接 dept_manager 之後，沒有在 dept_manager 表中查詢到記錄的員工。所以在關聯之後，判斷 d.dept_no IS NULL; 就是在 dept_manager 表中沒有數據的員工了。

select e.emp_no
from employees e
left join dept_manager d on e.emp_no = d.emp_no
where d.dept_no IS NULL;

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

-Advertisement-
Play Games
更多相關文章
  • 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 ...
  • 牛客網資料庫SQL實戰剖析(11-20)
    11. 獲取所有員工當前的manager 獲取所有員工當前的manager，如果當前的manager是自己的話結果不顯示，當前表示to_date='9999 01 01'。結果第一列給出當前員工的emp_no,第二列給出其manager對應的manager_no。 sql CREATE TABLE ...
一周排行
    -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# ...
所有分類