ClickHouse golang 海思 php nhibernate gdb seo mysql在线测试 oracle查看数据库 java 数据分析 nodejs后端开发 maven插件 河南普通话报名入口 python调用方法 pythonset python对象 python使用正则表达式 python插件 java写入文件 java连接mysql数据库 p2pover lanhelper 狮子狗出装 苹果5s降级 bash命令 视频字幕提取器 maxtoc4d fdisk下载 t470拆机 linux安卓模拟器 光标变粗 红巨星插件 python简单代码 七彩虹网驰版为什么坑 ps反光效果 代理服务器软件 英雄塔 苹果手机哪个版本最好 devtools 1004
当前位置: 首页 > 学习教程  > 编程语言

数据库学习(六)

2020/12/28 19:46:09 文章标签:

话不多说,就是干! 各部门工资最高的员工 CREATE TABLE Employee(Id INT,Name VARCHAR(10),Salary FLOAT,DepartmentId INT); INSERT INTO Employee VALUES(1,Joe,70000,1); INSERT INTO Employee VALUES(2,Henry,80000,2); INSERT INTO Employee VALUES…

话不多说,就是干!

各部门工资最高的员工

在这里插入图片描述

CREATE TABLE Employee(Id INT,Name VARCHAR(10),Salary FLOAT,DepartmentId INT);
INSERT INTO Employee VALUES(1,'Joe',70000,1);
INSERT INTO Employee VALUES(2,'Henry',80000,2);
INSERT INTO Employee VALUES(3,'Sam',60000,2);
INSERT INTO Employee VALUES(4,'Max',90000,1);

CREATE TABLE Department(Id INT,Name VARCHAR(10));
INSERT INTO Department VALUES(1,'IT');
INSERT INTO Department VALUES(2,'Sales');

SELECT Department.Name Department,Employee.Name Employee,Salary
FROM Employee inner join Department
ON Employee.DepartmentId = Department.Id
WHERE Employee.Salary in
(SELECT MAX(Salary) FROM Employee GROUP BY Employee.DepartmentId);

换座位

在这里插入图片描述

CREATE TABLE seat(id INT,student VARCHAR(10));
INSERT INTO seat VALUES(1,'Abbot');
INSERT INTO seat VALUES(2,'Doris');
INSERT INTO seat VALUES(3,'Emerson');
INSERT INTO seat VALUES(4,'Green');
INSERT INTO seat VALUES(5,'Jeames');

SELECT id,
CASE 
WHEN id = (SELECT MAX(id) FROM seat) THEN (SELECT student FROM seat s2 WHERE s2.id = s1.id) 
WHEN id % 2 = 1 THEN (SELECT student FROM seat s3 WHERE s3.id = s1.id + 1) 
WHEN id % 2 = 0 THEN (SELECT student FROM seat s4 WHERE s4.id = s1.id - 1) 
ELSE 'ERROR' 
END student
FROM seat s1;

分数排名

在这里插入图片描述

CREATE DATABASE test3;
use test3;
CREATE TABLE score(Id INT,Score FLOAT);
INSERT INTO score VALUES(1,3.50);
INSERT INTO score VALUES(2,3.65);
INSERT INTO score VALUES(3,4.00);
INSERT INTO score VALUES(4,3.85);
INSERT INTO score VALUES(5,4.00);
INSERT INTO score VALUES(6,3.65);

SELECT Score,DENSE_RANK() OVER(ORDER BY Score DESC) 'Rank'
FROM score;

连续出现的数字

在这里插入图片描述

CREATE DATABASE test4;
USE test4;
CREATE TABLE Logs(Id INT,Num INT);
INSERT INTO Logs VALUES(1,1);
INSERT INTO Logs VALUES(2,1);
INSERT INTO Logs VALUES(3,1);
INSERT INTO Logs VALUES(4,2);
INSERT INTO Logs VALUES(5,1);
INSERT INTO Logs VALUES(6,2);
INSERT INTO Logs VALUES(7,2);

SELECT Num ConsecutiveNums
FROM Logs L1
WHERE Num = (SELECT L2.Num FROM Logs L2 WHERE L1.Id = L2.Id - 1 AND L1.Num = L2.Num)
AND Num = (SELECT L2.Num FROM Logs L2 WHERE L1.Id = L2.Id - 2 AND L1.Num = L2.Num);

树节点

在这里插入图片描述
在这里插入图片描述

CREATE DATABASE test5;
USE test5;
CREATE TABLE tree(id INT,p_id INT);
INSERT INTO tree VALUES(1,NULL);
INSERT INTO tree VALUES(2,1);
INSERT INTO tree VALUES(3,1);
INSERT INTO tree VALUES(4,2);
INSERT INTO tree VALUES(5,2);

SELECT id,
CASE WHEN p_id = (SELECT MAX(p_id) FROM tree) THEN 'Leaf'
WHEN id NOT IN (SELECT p_id FROM tree WHERE p_id IS NOT NULL) THEN 'Leaf'
WHEN p_id IS NULL THEN 'Root'
ELSE 'Inner' END 'Type'
FROM tree;

至少有五名直接下属的经理

在这里插入图片描述

CREATE DATABASE test6;
USE test6;
CREATE TABLE Employee(Id INT,Name VARCHAR(10),Department CHAR(1),ManagerId INT);
INSERT INTO Employee VALUES(101,'John','A',NULL);
INSERT INTO Employee VALUES(102,'Dan','A',101);
INSERT INTO Employee VALUES(103,'James','A',101);
INSERT INTO Employee VALUES(104,'Amy','A',101);
INSERT INTO Employee VALUES(105,'Anne','A',101);
INSERT INTO Employee VALUES(106,'Ron','B',101);

SELECT E1.Name
FROM Employee E1 INNER JOIN Employee E2
ON E1.Id = E2.ManagerId
GROUP BY E1.Name
HAVING COUNT(E2.Id) >= 5;

分数排名

在这里插入图片描述

USE test3;
SELECT Score,RANK() OVER(ORDER BY Score DESC) 'Rank' FROM score;

查询回答率最高的问题

在这里插入图片描述

CREATE DATABASE test8;
USE test8;
CREATE TABLE survey_log(
    uid INT NOT NULL,
    action VARCHAR(20) NOT NULL,
    question_id INT,
    answer_id INT,
    q_num INT,
    timestamp INT);
 
INSERT INTO survey_log VALUES(5,'show',285,null,1,123);
INSERT INTO survey_log VALUES(5,'answer',285,124124,1,124);
INSERT INTO survey_log VALUES(5,'show',369,null,2,125);
INSERT INTO survey_log VALUES(5,'skip',369,null,2,126);

WITH tmp(question_id,ratio)
AS
(
SELECT question_id,SUM(CASE WHEN answer_id IS NULL THEN 0 ELSE 1 END) / COUNT(answer_id) 'ratio'
FROM survey_log
GROUP BY question_id
)
SELECT question_id
FROM tmp
WHERE ratio = (SELECT MAX(ratio) FROM tmp);

各部门前3高工资的员工

在这里插入图片描述

CREATE DATABASE test9;
USE test9;
CREATE TABLE employee (
id VARCHAR(3) NOT NULL,
name VARCHAR(25) NOT NULL,
salary INT,
departmentid VARCHAR(1),
PRIMARY KEY(id)
);
CREATE TABLE department (
Id VARCHAR(1) NOT NULL,
Name VARCHAR(20) NOT NULL,
PRIMARY KEY(Id)
);
 
INSERT INTO employee VALUES(1,'Joe',70000,1);
INSERT INTO employee VALUES(2,'Henry',80000,2);
INSERT INTO employee VALUES(3,'Sam',60000,2);
INSERT INTO employee VALUES(4,'Max',90000,1);
INSERT INTO employee VALUES(5,'Janet',69000,1);
INSERT INTO employee VALUES(6,'Randy',85000,1);
 
INSERT INTO department VALUES('1','IT');
INSERT INTO department VALUES('2','Sales');

SELECT Department,Employee,Salary
FROM(
SELECT department.Name 'Department',Employee.name 'Employee',Salary,ROW_NUMBER() OVER(PARTITION BY department.Id ORDER BY Salary DESC) 'ranking'
FROM employee INNER JOIN department
ON employee.departmentid = department.Id) t1
WHERE ranking <= 3;

平面上最近距离

在这里插入图片描述

CREATE DATABASE test10;
USE test10;
CREATE TABLE point_2d(x INT,y INT);
INSERT INTO point_2d VALUES(-1,-1);
INSERT INTO point_2d VALUES(0,0);
INSERT INTO point_2d VALUES(-1,-2);


SELECT MIN(POW(p1.x - p2.x,2) + POW(p1.y - p2.y,2)) shortest
FROM point_2d p1 cross join point_2d p2
ON (p1.x <> p2.x or p1.y <> p2.y);

行程和用户

在这里插入图片描述
在这里插入图片描述

CREATE DATABASE test11;
USE test11;

CREATE TABLE trips
(id INT NOT NULL,
client_id INT, 
driver_id INT,
city_id INT,
status VARCHAR(60),
request_at DATE);
 
INSERT INTO trips VALUES(1,1,10,1,'completed','2013-10-1');
INSERT INTO trips VALUES(2,2,11,1,'cancelled_by_driver','2013-10-1');
INSERT INTO trips VALUES(3,3,12,6,'completed','2013-10-1');
INSERT INTO trips VALUES(4,4,13,6,'cancelled_by_client','2013-10-1');
INSERT INTO trips VALUES(5,1,10,1,'completed','2013-10-2');
INSERT INTO trips VALUES(6,2,11,6,'completed','2013-10-2');
INSERT INTO trips VALUES(7,3,12,6,'completed','2013-10-2');
INSERT INTO trips VALUES(8,2,12,12,'completed','2013-10-3');
INSERT INTO trips VALUES(9,3,10,12,'completed','2013-10-3');
INSERT INTO trips VALUES(10,4,13,12,'cancelled_by_driver','2013-10-3');


CREATE TABLE users
(user_id  INT NOT NULL,
banned VARCHAR(4),
role VARCHAR(60));
 
INSERT INTO users VALUES(1,'No','client');
INSERT INTO users VALUES(2,'YES','client');
INSERT INTO users VALUES(3,'No','client');
INSERT INTO users VALUES(4,'No','client');
INSERT INTO users VALUES(10,'No','driver');
INSERT INTO users VALUES(11,'No','driver');
INSERT INTO users VALUES(12,'No','driver');
INSERT INTO users VALUES(13,'No','driver');

SELECT request_at,SUM(CASE WHEN status LIKE 'cancelled%' THEN 1 ELSE 0 END) / COUNT(*) ratio
FROM trips INNER JOIN users
ON trips.client_id = users.user_id
WHERE banned <> 'Yes'
GROUP BY request_at;


本文链接: http://www.dtmao.cc/news_show_550370.shtml

附件下载

相关教程

    暂无相关的数据...

共有条评论 网友评论

验证码: 看不清楚?