牛客網資料庫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
  • 移動開發（一）：使用.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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類