用vs与dw做网站,山东省优质高职院校建设网站,网站高端设计公司,wordpress single page目录 难度#xff1a;简单1. 组合两个表2. 第二高的薪水3. 第N高的薪水4. 分数排名5. 连续出现的数字6. 超过经理收入的员工7. 重新8. 寻找用户推荐人9. 销售员10. 排名靠前的旅行者11. 患某种疾病的患者12. 修复表中的名字13. 求关注者的数量14. 可回收且低脂的产品15. 计算特… 目录 难度简单1. 组合两个表2. 第二高的薪水3. 第N高的薪水4. 分数排名5. 连续出现的数字6. 超过经理收入的员工7. 重新8. 寻找用户推荐人9. 销售员10. 排名靠前的旅行者11. 患某种疾病的患者12. 修复表中的名字13. 求关注者的数量14. 可回收且低脂的产品15. 计算特殊奖金16. 丢失信息的雇员17. 每个产品在不同商店的价格18. 文章浏览19. 上升的温度20. 按日期分组销售产品21. 员工奖金22. 使用唯一标识码替换员工Id23. 订单最多的客户24. 判断三角形25. 只出现一次的最大数字26. 平均售价27. 查找拥有有效邮箱的用户28. 查询结果的质量和占比29. 列出指定时间段内所有的下单产品30. 最高薪水差异31. 总旅行距离32. 自行车的最后使用时间33. 统计 Spotify 排行榜上艺术家出现次数34. 查询员工当前薪水35. 把名字和职业联系起来36. 形成化学键37. 整理奥运表38. 每位教师所教授的科目种类的数量39. 联赛的所有比赛39. 产品销售分析 ⑤40. 净现值查询 难度简单
1. 组合两个表
表1Person PersonId 是上表主键 表2 Address AddressId 是上表主键 编写一个 SQL 查询满足条件无论 person 是否有地址信息都需要基于上述两表提供 person 的以下信息 FirstName, LastName, City, State
select a.FirstName, a.LastName, b.City, b.State
from Person a
left join Address b
on a.PersonID b.PersonID2. 第二高的薪水
编写一个 SQL 查询获取 Employee 表中第二高的薪水Salary 。 例如上述 Employee 表SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水那么查询应返回 null。 方法一 因为排序可能会出现薪资相同的情况
select max(Salary) as SecondHighestSalary
from (
select Salary, row_number() over (order by Salary desc) as rnk
from employee b
group by Salary
) a
where a.rnk 2
方法二 通过取最大值再去排除最大值去找到第二高的薪水。
select max(Salary) as SecondHighestSalary
from Employee
where Salary (select max(Salary) from Employee)
3. 第N高的薪水
有如下两张表T 编写一个 SQL 查询获取 Employee 表中第 n 高的薪水Salary。
例如上述 Employee 表n 2 时应返回第二高的薪水 200。如果不存在第 n 高的薪水那么查询应返回 null。 方法一
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT AS
BEGIN
RETURN (
select Salary as getNthHighestSalary
from (select Salary ,dense_rank() over(order by Salary desc) as rnk
from Employee
group by Salary) a
where rnk N );
END方法二
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT AS
BEGIN
RETURN ( select distinct Salary
from Employee
order by Salary desc
Offset N-1 rows
Fetch next 1 rows only);
END4. 分数排名
编写一个 SQL 查询来实现分数排名。 如果两个分数相同则两个分数排名Rank相同。请注意平分后的下一个名次应该是下一个连续的整数值。换句话说名次之间不应该有“间隔”。 例如根据上述给定的 Scores 表你的查询应该返回按分数从高到低排列
select Score,DENSE_RANK() OVER(ORDER BY Score desc) as Rank
from Scores5. 连续出现的数字
表Logs
编写一个 SQL 查询查找所有至少连续出现三次的数字。 返回的结果表中的数据可以按 任意顺序 排列。 查询结果格式如下面的例子所示 方法一 如果是连续100次1000次数值相同那么这种方法就不适用了
select distinct a.Num as ConsecutiveNums
from Logs a
inner join Logs b
on a.ID B.ID 1 and a.NUm b.Num
inner join Logs c
on a.ID C.ID 2 and b.Num c.Num 方法二
SELECT DISTINCT Num as ConsecutiveNums
FROM (SELECT Num,COUNT(1) as SerialCount
FROM (SELECT Id,Num,row_number() over(order by id) -ROW_NUMBER() over(partition by Num order by Id) as SerialNumberSubGroup
FROM Logs) as Sub
GROUP BY Num,SerialNumberSubGroup HAVING COUNT(1) 3) as Result6. 超过经理收入的员工
Employee 表包含所有员工他们的经理也属于员工。每个员工都有一个 Id此外还有一列对应员工的经理的 Id。 给定 Employee 表编写一个 SQL 查询该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中Joe 是唯一一个收入超过他的经理的员工。
7. 重新
if object_id(department,u) is not null drop table department
create table department (id int,revenue INT,MONTH VARCHAR(10))
INSERT INTO DEPARTMENT(id,REVENUE,MONTH)
VALUES(1,8000 , Jan )
,(2,9000 , Jan )
,(3,10000 , Feb )
,(1,7000 , Feb )
,(1,6000 , Mar )
select id
,sum(case when month Jan then revenue else null end) as jan_revenue
,sum(case when month Feb then revenue else null end) as Feb_revenue
,sum(case when month Mar then revenue else null end) as Mar_revenue
,sum(case when month Apr then revenue else null end) as Apr_revenue
,sum(case when month May then revenue else null end) as May_revenue
,sum(case when month Jun then revenue else null end) as Jun_revenue
,sum(case when month Jul then revenue else null end) as Jul_revenue
,sum(case when month Aug then revenue else null end) as Aug_revenue
,sum(case when month Sep then revenue else null end) as Sep_revenue
,sum(case when month Oct then revenue else null end) as Oct_revenue
,sum(case when month Nov then revenue else null end) as Nov_revenue
,sum(case when month Dec then revenue else null end) as Dec_revenue
from DEPARTMENT
group by id8. 寻找用户推荐人
给定表 customer 里面保存了所有客户信息和他们的推荐人。 写一个查询语句返回一个客户列表列表中客户的推荐人的编号都 不是 2。
对于上面的示例数据结果为 --方法一执行耗时852ms
select name
from customer
where isnull(referee_id,0) 2--方法二执行耗时1038ms
select name
from customer
where id not in (select id from customer where referee_id 2
)
9. 销售员 编写一个SQL查询报告没有任何与名为 “RED” 的公司相关的订单的所有销售人员的姓名。
以 任意顺序 返回结果表。 --方法一运行耗时903msSELECT s.name
FROM salesperson s
WHERE s.sales_id NOT IN (SELECTo.sales_idFROM orders oLEFT JOIN company c ON o.com_id c.com_idWHERE c.name RED)
;10. 排名靠前的旅行者
表Users 表Rides 写一段 SQL , 报告每个用户的旅行距离。
返回的结果表单以 travelled_distance 降序排列 如果有两个或者更多的用户旅行了相同的距离, 那么再以 name 升序排列 。
查询结果格式如下例所示。
select name,travelled_distance from (
select b.id,b.name,isnull(sum(distance),0) as travelled_distance
from users b
left join rides a
on a.user_id b.id
group by b.id,b.name ) a
order by travelled_distance desc,name asc11. 患某种疾病的患者
患者信息表 Patients 写一条 SQL 语句查询患有 I 类糖尿病的患者 ID patient_id、患者姓名patient_name以及其患有的所有疾病代码conditions。I 类糖尿病的代码总是包含前缀 DIAB1 。
按 任意顺序 返回结果表。
select *
from patients
where conditions like DIAB1%
or conditions like % DIAB1%12. 修复表中的名字
表 Users 编写一个 SQL 查询来修复名字使得只有第一个字符是大写的其余都是小写的。
返回按 user_id 排序的结果表。
select user_id,
concat(upper(left(name, 1)), lower(right(name, len(name) - 1))) name
from Users
order by user_id13. 求关注者的数量
表 Followers 写出 SQL 语句对于每一个用户返回该用户的关注者数量。
按 user_id 的顺序返回结果表。
select user_id ,isnull(count(*),0) as followers_count
from Followers
group by user_id14. 可回收且低脂的产品
表Products 写出 SQL 语句查找既是低脂又是可回收的产品编号。
返回结果 无顺序要求 。
select product_id from Products
where low_fats Y and recyclable Y15. 计算特殊奖金
表: Employees 写出一个SQL 查询语句计算每个雇员的奖金。如果一个雇员的id是奇数并且他的名字不是以’M’开头那么他的奖金是他工资的100%否则奖金为0。
Return the result table ordered by employee_id.
返回的结果集请按照employee_id排序。
select employee_id
,case when employee_id % 2 1 and left(name ,1) M
then salary else 0 end as bonus
from Employees
order by employee_id16. 丢失信息的雇员
表: Employees 表: Salaries 写出一个查询语句找到所有 丢失信息 的雇员id。当满足下面一个条件时就被认为是雇员的信息丢失
雇员的 姓名 丢失了或者 雇员的 薪水信息 丢失了或者 返回这些雇员的id employee_id 从小到大排序 。
select employee_id from
(select employee_id from Employeesunion allselect employee_id from Salaries
)as t
group by employee_id
having count(employee_id) 1
order by employee_id17. 每个产品在不同商店的价格
表Products 请你重构 Products 表查询每个产品在不同商店的价格使得输出的格式变为(product_id, store, price) 。如果这一产品在商店里没有出售则不输出这一行。
输出结果表中的 顺序不作要求 。
select *from (
select product_id,store,price
from Products
unpivot(price for store in(store1 ,store2,store3 )) c)a
where price is not null18. 文章浏览
请编写一条 SQL 查询以找出所有浏览过自己文章的作者结果按照 id 升序排列。
查询结果的格式如下所示
--distinct 去重
select distinct author_id as id
from Views
where author_id viewer_id
order by author_id --group by 去重
select author_id as id
from Views
where author_id viewer_id
group by author_id
order by author_id 19. 上升的温度
编写一个 SQL 查询来查找与之前昨天的日期相比温度更高的所有日期的 id 。 返回结果 不要求顺序 。 查询结果格式如下例。
select a.id from weather a
left join weather b
on a.recordDate dateadd(day,1,b.recordDate)
where a.temperature b.temperature20. 按日期分组销售产品
编写一个 SQL 查询来查找每个日期、销售的不同产品的数量及其名称。 每个日期的销售产品名称应按词典序排列。 返回按 sell_date 排序的结果表。 查询结果格式如下例所示。
--MS SQL SERVER
select sell_date ,count(distinct product) as num_sold
,STUFF((select distinct ,product
from activities B
where A.sell_date B.sell_date
FOR XML PATH()),1,1,) as products
from activities a
group by sell_date--MySQL
select sell_date, count(distinct product) as num_sold, group_concat(distinct product order by product separator ,) as products
from Activities
group by sell_date
order by sell_date;
21. 员工奖金
选出所有 bonus 1000 的员工的 name 及其 bonus。
--建表
if object_id(Employee,u) is not null drop table Employee
go
create table Employee(empId int, name varchar(20), supervisor int , salary int
)
go
insert into Employee
values(1 ,John ,3 , 1000 )
,(2 ,Dan ,3 , 2000 )
,(3 ,Brad ,null , 4000 )
,(4 ,Thomas ,3 , 4000 )
go
if object_id(Bonus,u) is not null drop table Bonus
go
create table Bonus (empId int , bonus int
)
go
insert into Bonus
values(2 ,500 )
,(4 ,2000)
go
--查询
select a.name,b.bonus
from employee a
left join bonus b
on a.empid b.empid
where isnull(b.bonus,0) 100022. 使用唯一标识码替换员工Id
写一段SQL查询来展示每位用户的 唯一标识码unique ID 如果某位员工没有唯一标识码使用 null 填充即可。 你可以以 任意 顺序返回结果表。
--建表
if object_id(Employees,u) is not null drop table Employees
go
create table Employees (id int
, name varchar(20)
)
go
insert into Employees
values
(1 ,Alice )
,(7 ,Bob )
,(11 ,Meir )
,(90 ,Winston )
,(3 ,Jonathan )
go
if object_id(EmployeeUNI,u) is not null drop table EmployeeUNI
go
create table EmployeeUNI(id int
, unique_id int
)
go
insert into EmployeeUNI
values(3 , 1 )
,(11 , 2 )
,(90 , 3 )
go
--查询
select b.unique_id,a.name
from Employees a
left join EmployeeUNI b
on a.id b.id
23. 订单最多的客户
编写一个SQL查询为下了 最多订单 的客户查找 customer_number 。 测试用例生成后 恰好有一个客户 比任何其他客户下了更多的订单。
--建表
if object_id(Orders,u) is not null drop table Orders
go
create table Orders(order_number int
, customer_number int
)
go
insert into Orders
values(1 ,1 )
,(2 ,2 )
,(3 ,3 )
,(4 ,3 )
go
--查询
--方法一
select customer_number from (
select *,row_number() over(order by cnt desc ) as rnk
from (select customer_number ,count(distinct order_number) as cntfrom Ordersgroup by customer_number ) a ) a
where rnk 1
--方法二
select top 1 customer_number
from orders
group by customer_number
order by count(order_number) desc24. 判断三角形
写一个SQL查询每三个线段报告它们是否可以形成一个三角形。 以 任意顺序 返回结果表。
--建表
if object_id(Triangle,u) is not null drop table Triangle
go
create table Triangle(x int
,y int
,z int
)
go
insert into Triangle
values( 13, 15 ,30 )
,( 10, 20 ,15 )
go
--查询
select *,case when xyz and xzy and yz x then Yes else No END as triangle
FROM Triangle25. 只出现一次的最大数字
单一数字 是在 MyNumbers 表中只出现一次的数字。 请你编写一个 SQL 查询来报告最大的 单一数字 。如果不存在 单一数字 查询需报告 null 。
if object_id(MyNumbers,u) is not null drop table MyNumbers
go
create table MyNumbers (num int
)
go
insert into MyNumbers
values( 8 )
,( 8 )
,( 3 )
,( 3 )
,( 1 )
,( 4 )
,( 5 )
,( 6 )
go
--查询
select max(num) as num
from (select numfrom MyNumbersgroup by numhaving count(*) 1 ) a26. 平均售价
编写SQL查询以查找每种产品的平均售价。 average_price 应该四舍五入到小数点后两位。
--建表
if object_id(Prices,u) is not null drop table Prices
go
create table Prices(product_id int
, start_date date
, end_date date
, price int
)
go
insert into Prices
values(1 ,2019-02-17,2019-02-28, 5 )
,(1 ,2019-03-01,2019-03-22, 20 )
,(2 ,2019-02-01,2019-02-20, 15 )
,(2 ,2019-02-21,2019-03-31, 30 )
go
if object_id(UnitsSold,u) is not null drop table UnitsSold
go
create table UnitsSold (product_id int
,purchase_date date
, units int
)
go
insert into UnitsSold
values(1 ,2019-02-25, 100)
,(1 ,2019-03-01, 15 )
,(2 ,2019-02-10, 200)
,(2 ,2019-03-22, 30 )
go
--查询select product_id ,cast(sum(price * units ) * 1.0 /sum(units) as decimal(19,2)) average_price
from (
select a.*,b.units
from Prices a
left join UnitsSold b
on a.product_id b.product_id
and b.purchase_date between a.start_date and a.end_date ) a
group by product_id
27. 查找拥有有效邮箱的用户
写一条 SQL 语句查询拥有有效邮箱的用户。 有效的邮箱包含符合下列条件的前缀名和域名 前缀名是包含字母大写或小写、数字、下划线 ‘_’、句点 ‘.’ 和/或横杠 ‘-’ 的字符串。前缀名必须以字母开头。 域名是 ‘leetcode.com’ 。 按任意顺序返回结果表。
--建表
if object_id(Users,u) is not null drop table Users
go
create table Users (user_id int
, name varchar(100)
, mail varchar(100)
)
go
insert into Users
values( 1 ,Winston ,winstonleetcode.com )
,( 2 ,Jonathan ,jonathanisgreat )
,( 3 ,Annabelle ,bella-leetcode.com )
,( 4 ,Sally ,sally.comeleetcode.com )
,( 5 ,Marwan ,quarz#2020leetcode.com )
,( 6 ,David ,david69gmail.com )
,( 7 ,Shapiro ,.shapoleetcode.com )
go
--查询
SELECT *
FROM Users
WHERE mail LIKE [A-Za-z]%leetcode.com AND mail NOT LIKE %[^A-Za-z0-9._/-]%%
28. 查询结果的质量和占比 将查询结果的质量 quality 定义为 各查询结果的评分与其位置之间比率的平均值。
将劣质查询百分比 poor_query_percentage 为 评分小于 3 的查询结果占全部查询结果的百分比。
编写一组 SQL 来查找每次查询的名称(query_name)、质量(quality) 和 劣质查询百分比(poor_query_percentage)。 质量(quality) 和劣质查询百分比(poor_query_percentage) 都应四舍五入到小数点后两位。
--建表
if object_id(Queries,u) is not null drop table Queries
go
create table Queries(query_name varchar(100)
,result varchar(100)
,position int
,rating int
)
go
insert into Queries
values(Dog, Golden Retriever ,1 ,5 )
,(Dog, German Shepherd ,2 ,5 )
,(Dog, Mule ,200 ,1 )
,(Cat, Shirazi ,5 ,2 )
,(Cat, Siamese ,3 ,3 )
,(Cat, Sphynx ,7 ,4 )
go
--查询
select query_name, cast(avg(rating *1.0 / position ) as decimal(19,2)) as Quality
,cast(sum(case when rating 3 then 1 else 0 end ) * 100.0 /count(*) as decimal(19,2)) as poor_query_percentage
from Queries
group by query_name29. 列出指定时间段内所有的下单产品
写一个解决方案要求获取在 2020 年 2 月份下单的数量不少于 100 的产品的名字和数目。 返回结果表单的 顺序无要求 。
--建表
if object_id(Products,U) is not null drop table Products
go
create table Products(product_id int,product_name varchar(100),product_category varchar(100)
)
go
insert into Products
values(1 ,Leetcode Solutions ,Book )
,(2 ,Jewels of Stringology ,Book )
,(3 ,HP ,Laptop )
,(4 ,Lenovo ,Laptop )
,(5 ,Leetcode Kit ,T-shirt )
go
if object_id(Orders,u) is not null drop table Orders
go
create table Orders(product_id int,order_date date,unit int
)
go
insert into Orders
values( 1 ,2020-02-05,60 )
,( 1 ,2020-02-10,70 )
,( 2 ,2020-01-18,30 )
,( 2 ,2020-02-11,80 )
,( 3 ,2020-02-17,2 )
,( 3 ,2020-02-24,3 )
,( 4 ,2020-03-01,20 )
,( 4 ,2020-03-04,30 )
,( 4 ,2020-03-04,60 )
,( 5 ,2020-02-25,50 )
,( 5 ,2020-02-27,50 )
,( 5 ,2020-03-01,50 )
go
--查询
select a.Product_name,sum(b.Unit) as Unit
from Products a
left join orders b
on a.product_id b.product_id
where year(order_date ) 2020 and month(order_date) 2
group by a.product_name
having sum(b.Unit) 10030. 最高薪水差异 编写一个解决方案计算 市场部门 和 工程部门 中 最高 工资之间的差异。输出工资的绝对差异。 返回结果表。 返回结果格式如下示例所示。
--建表
if object_id(Salaries,u) is not null drop table Salaries
go
create table Salaries(emp_name varchar(20)
, department varchar(20)
, salary int
)
go
insert into Salaries
values( Kathy ,Engineering ,50000 )
,( Roy ,Marketing ,30000 )
,( Charles ,Engineering ,45000 )
,( Jack ,Engineering ,85000 )
,( Benjamin ,Marketing ,34000 )
,( Anthony ,Marketing ,42000 )
,( Edward ,Engineering ,102000 )
,( Terry ,Engineering ,44000 )
,( Evelyn ,Marketing ,53000 )
,( Arthur ,Engineering ,32000 )
go
--查询
select ABS(max(a.salary) - max(b.salary)) as salary_difference
from salaries a
full outer join salaries b
on b.department Marketing
where a.department Engineering31. 总旅行距离 编写一个解决方案计算每个用户的旅行距离 distance 。如果有用户没有任何旅行记录那么他们的 distance 应被视为 0 。输出 user_id, name 和总旅行距离 traveled distance 。 按 升序排序 的 user_id 返回结果表。 结果格式如下示例。
--建表
if object_id(Users,u) is not null drop table Users
go
create table Users(
user_id int
, name varchar(20)
)
go
insert into Users
values( 17 ,Addison )
,( 14 ,Ethan )
,( 4 ,Michael )
,( 2 ,Avery )
,( 10 ,Eleanor )
go
if object_id(Rides,u) is not null drop table Rides
go
create table Rides (
ride_id int
,user_id int
,distance int
)
go
insert into Rides
values( 72 ,17 ,160 )
,( 42 ,14 ,161 )
,( 45 ,4 ,59 )
,( 32 ,2 ,197 )
,( 15 ,4 ,357 )
,( 56 ,2 ,196 )
,( 10 ,14 ,25 )
go
--查询
select a.user_id ,a.name
, isnull(sum(b.distance ),0) as [traveled distance]
from Users a
left join Rides b
on a.user_id b.user_id
group by a.user_id ,a.name
order by a.user_id32. 自行车的最后使用时间 编写一个解决方案找出每辆自行车 最近一次被使用 的时间。 返回结果表按 最近被使用 的自行车进行排序。 返回结果的格式如下所示
--建表
if object_id(Bikes ,u) is not null drop table Bikes
go
create table Bikes(ride_id int
, bike_number varchar(20)
, start_time datetime
, end_time datetime
)
go
insert into Bikes
values( 1,W00576, 2012-03-25 11:30:00, 2012-03-25 12:40:00)
,( 2,W00300, 2012-03-25 10:30:00, 2012-03-25 10:50:00)
,( 3,W00455, 2012-03-26 14:30:00, 2012-03-26 17:40:00)
,( 4,W00455, 2012-03-25 12:30:00, 2012-03-25 13:40:00)
,( 5,W00576, 2012-03-25 08:10:00, 2012-03-25 09:10:00)
,( 6,W00576, 2012-03-28 02:30:00, 2012-03-28 02:50:00)
go
--查询
select bike_number
,max(end_time ) as end_time
from Bikes
group by bike_number
order by end_time desc33. 统计 Spotify 排行榜上艺术家出现次数 编写解决方案来查找每个艺术家在Spotify排行榜上出现的次数。 返回结果表其中包含艺术家的名称以及相应的出现次数按出现次数 降序 排列。如果出现次数相等则按艺术家名称 升序 排列。 返回结果格式如下所示
--建表
if object_id(Spotify,u) is not null drop table Spotify
go
create table Spotify(id int
, track_name varchar(20)
, artist varchar(20)
)
go
insert into Spotify
values( 303651 ,Heart Wont Forget ,Sia )
,( 1046089 ,Shape of you ,Ed Sheeran)
,( 33445 ,Im the one ,DJ Khalid )
,( 811266 ,Young Dumb Broke ,DJ Khalid )
,( 505727 ,Happier ,Ed Sheeran)
go
--查询
select artist ,count(id ) as occurrences
from Spotify
group by artist
order by count(id ) desc,artist34. 查询员工当前薪水 找出每个员工的当前薪水假设薪水每年增加。输出他们的 emp_id 、firstname 、lastname 、salary 和 department_id 。 按 emp_id 升序排序 返回结果表。 返回结果格式如下所示。
--建表
if object_id(Salary,u) is not null drop table Salary
go
create table Salary(emp_id int
, firstname varchar(20)
, lastname varchar(20)
, salary varchar(20)
, department_id varchar(20)
)
go
insert into Salary
values( 1, Todd ,Wilson ,110000 ,D1006)
,( 1, Todd ,Wilson ,106119 ,D1006)
,( 2, Justin ,Simon ,128922 ,D1005)
,( 2, Justin ,Simon ,130000 ,D1005)
,( 3, Kelly ,Rosario ,42689 ,D1002)
,( 4, Patricia ,Powell ,162825 ,D1004)
,( 4, Patricia ,Powell ,170000 ,D1004)
,( 5, Sherry ,Golden ,44101 ,D1002)
,( 6, Natasha ,Swanson ,79632 ,D1005)
,( 6, Natasha ,Swanson ,90000 ,D1005)
go
--查询select
emp_id , firstname, lastname , salary ,department_id
from (selectemp_id , firstname, lastname , salary ,department_id,rank() over(partition by emp_id order by salary desc ) as rnkfrom Salary ) a
where rnk 1
order by emp_id35. 把名字和职业联系起来 编写一个解决方案报告每个人的名字后面是他们职业的第一个字母用括号括起来。 返回按 person_id 降序排列 的结果表。 返回结果格式示例如下。
--建表
if object_id(Person ,u) is not null drop table Person
go
create table Person (person_id int
, name varchar(20)
, profession varchar(20)
)
go
insert into Person
values( 1 ,Alex ,Singer )
,( 3 ,Alice ,Actor )
,( 2 ,Bob ,Player )
,( 4 ,Messi ,Doctor )
,( 6 ,Tyson ,Engineer )
,( 5 ,Meir ,Lawyer )
go
--查询
select person_id, name ( left(profession ,1) ) as name
from Person
order by person_id desc36. 形成化学键 如果一个元素是 ‘Metal’另外一个元素是 ‘Nonmetal’ 那么它们可以形成键。 编写一个解决方案找出所有可以形成键的元素对。 以 任意顺序 返回结果表。 查询结果格式如下所示。
--建表
if object_id(Elements,u) is not null drop table Elements
go
create table Elements(symbol varchar(20)
, type varchar(20)
, electrons int
)
go
insert into Elements
values( He ,Noble ,0 )
,( Na ,Metal ,1 )
,( Ca ,Metal ,2 )
,( La ,Metal ,3 )
,( Cl ,Nonmetal ,1 )
,( O ,Nonmetal ,2 )
,( N ,Nonmetal ,3 )
go
--查询
select a.symbol as metal,b.symbol as nonmetal
from Elements a
cross join (select distinct symbol from Elementswhere type Nonmetal) b
where a.type Metal
37. 整理奥运表 奥运名次表的排序规则如下:
金牌越多的国家排名第一。如果金牌数持平银牌多的国家排名第一。如果银牌数量持平铜牌数量最多的国家排名第一。如果铜牌中出现并列那么并列的国家将按照字典的升序进行排序。 写一个解决方案对奥运表进行排序
返回结果格式示例如下。
--建表
if object_id(Olympic,u) is not null drop table Olympic
go
create table Olympic (country varchar(20)
, gold_medals int
, silver_medals int
, bronze_medals int
)
go
insert into Olympic
values( China , 10 , 10 ,20 ),( South Sudan , 0 , 0 ,1 ),( USA , 10 , 10 ,20 ),( Israel , 2 , 2 ,3 ),( Egypt , 2 , 2 ,2 )go
--查询
select country ,gold_medals , silver_medals , bronze_medals
from (
select *
,rank() over(order by gold_medals desc ) as gold_rank
,rank() over(order by silver_medals desc ) as silver_rank
,rank() over(order by bronze_medals desc) as bronze_rank
from Olympic ) a
order by gold_rank ,silver_rank ,bronze_rank ,country
38. 每位教师所教授的科目种类的数量 查询每位老师在大学里教授的科目种类的数量。 以 任意顺序 返回结果表。 查询结果格式示例如下。
--建表
if object_id(Teacher,u) is not null drop table Teacher
go
create table Teacher(teacher_id int
, subject_id int
, dept_id int
)
go
insert into Teacher
values( 1 ,2 ,3 )
,( 1 ,2 ,4 )
,( 1 ,3 ,3 )
,( 2 ,1 ,1 )
,( 2 ,2 ,1 )
,( 2 ,3 ,1 )
,( 2 ,4 ,1 )
go
--查询
select teacher_id ,count(distinct subject_id ) as cnt
from Teacher
group by teacher_id
39. 联赛的所有比赛 编写解决方案获取联赛中所有比赛。每两支球队进行两场比赛其中一支球队是主队 home_team 另一支是客场队 away_team。 按 任意顺序 返回结果表。 返回结果格式如下例所示。 --建表
if object_id(Teams,u) is not null drop table Teams
go
create table Teams(team_name varchar(20)
)
go
insert into Teams
values( Leetcode FC )
,( Ahly SC )
,( Real Madrid )
go
--查询
select a.team_name as home_team ,b.team_name as away_team
from Teams a
cross join Teams b
where a.team_name b.team_name39. 产品销售分析 ⑤ 编写解决方案获取每个用户的消费额。 按用户消费额 spending 递减的顺序返回结果。在消费额相等的情况下以 user_id 递增的顺序将其排序。 结果的格式如下面例子所示
在这里插入代码片40. 净现值查询 编写解决方案找到 Queries 表中每一次查询的净现值。 结果表 没有顺序要求 。 查询结果的格式如下所示:
--建表
if object_id(NPV,u) is not null drop table NPV
go
create table NPV(id int
,year int
,npv int
)
go
insert into NPV
values( 1 ,2018 , 100 )
,( 7 ,2020 , 30 )
,( 13 ,2019 , 40 )
,( 1 ,2019 , 113 )
,( 2 ,2008 , 121 )
,( 3 ,2009 , 12 )
,( 11 ,2020 , 99 )
,( 7 ,2019 , 0 )
go
if object_id(Queries,u) is not null drop table Queries
go
create table Queries(id int
, year int
)
go
insert into Queries
values(1 , 2019 )
,(2 , 2008 )
,(3 , 2009 )
,(7 , 2018 )
,(7 , 2019 )
,(7 , 2020 )
,(13 , 2019 )
go
--查询
select a.*,isnull(b.npv,0) npv
from Queries a
left join NPV b
on a.id b.id and a.year b.year
