牛客網資料庫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
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類