【高频SQL基础50题】26-30

力扣周赛被打懵了，只能刷点SQL缓解一下了。。。

目录

1.连续出现的数字

2.餐馆营业额变化增长 

3. 第二高的薪水

4.无效的推文 

5.每台机器的进程平均运行时间 

1.连续出现的数字

高级查询题。

解题

# Write your MySQL query statement below
#按照num分组，相同的num分为同一组，然后按照id列排序分配行号
SELECT DISTINCT a.num as ConsecutiveNums
FROM (SELECT id,num,id+1-ROW_NUMBER() OVER (PARTITION BY num ORDER BY id) AS sub FROM Logs) AS a
GROUP BY a.num,a.sub
HAVING COUNT(*) >=3

2.餐馆营业额变化增长 

子查询。

# Write your MySQL query statement below
SELECT DISTINCT visited_on, sum_amount AS amount,
                ROUND(sum_amount/7,2) AS average_amount
FROM (SELECT visited_on,SUM(amount) OVER (ORDER BY visited_on ROWS 6 PRECEDING) AS sum_amount
        #计算每天金额总量
      FROM (
        SELECT visited_on,SUM(amount) AS amount
        FROM Customer
        GROUP BY visited_on
          )TT
      )LL
#手动覆盖完整7天数据
WHERE DATEDIFF(visited_on,(SELECT MIN(visited_on)FROM Customer))>=6

 

3. 第二高的薪水

 

 

 

题解参考

# Write your MySQL query statement below
SELECT ifNull(
    (SELECT DISTINCT salary FROM Employee ORDER BY Salary DESC limit 1,1),null
) AS SecondHighestSalary;

 

4.无效的推文 

 

查询题。

char_length()函数用来计算字符串长度。

# Write your MySQL query statement below
SELECT  tweet_id
FROM Tweets
WHERE char_length(content)>15

 

5.每台机器的进程平均运行时间 

连接题。

tql自连接

# Write your MySQL query statement below
SELECT a.machine_id,ROUND(AVG(a.timestamp-b.timestamp),3) AS processing_time
FROM Activity a
JOIN Activity b
ON 
a.machine_id=b.machine_id AND
a.process_id=b.process_id AND
a.activity_type='end' AND
b.activity_type='start'
GROUP BY a.machine_id;