LeetCode---SQL刷题4

1.197. 上升的温度

SQL语句如下:

SELECT w1.id 
FROM Weather w1
INNER JOIN Weather w2 ON DATEDIFF(w1.recordDate,w2.recordDate) = 1 
AND w1.Temperature > w2.Temperature 
1
2
3
4

可参照下图进行理解:

备注:DATEDIFF(expr1,expr2)函数返回expr1-expr2的天数。

mysql> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
        -> 1
mysql> SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31');
        -> -31
1
2
3
4

2.607. 销售员

SQL语句如下:

• 解题1

SELECT s.name 
FROM SalesPerson  s
WHERE sales_id NOT IN (SELECT sales_id  FROM Orders 
WHERE com_id IN (SELECT com_id  FROM Company WHERE name = 'RED'))
1
2
3
4

• 解题2【用时更少,尽量少使用子查询】

SELECT s.name 
FROM SalesPerson  s
WHERE sales_id NOT IN (
    SELECT sales_id FROM Orders  o
    LEFT JOIN Company c ON o.com_id = c.com_id 
    WHERE name = 'RED'
);
1
2
3
4
5
6
7

3.1141. 查询近30天活跃用户数

SQL语句如下:

SELECT activity_date AS day,
COUNT(DISTINCT user_id) AS active_users  
FROM Activity 
WHERE activity_date < '2019-07-27' AND DATEDIFF('2019-07-27',activity_date) < 30
GROUP BY activity_date;
1
2
3
4
5

4.1693. 每天的领导和合伙人

SQL语句如下:

SELECT DISTINCT date_id,make_name,
COUNT(DISTINCT lead_id) AS unique_leads,
COUNT(DISTINCT partner_id)  AS unique_partners
FROM DailySales 
GROUP BY date_id,make_name
1
2
3
4
5

5.1729. 求关注者的数量

SQL语句:

SELECT  user_id,
COUNT(follower_id) AS followers_count
FROM followers
GROUP BY user_id 
ORDER BY user_id
1
2
3
4
5

6.586. 订单最多的客户

SQL语句如下:

SELECT 
customer_number
FROM Orders
GROUP BY customer_number 
ORDER BY COUNT(order_number) DESC
LIMIT 1
1
2
3
4
5
6

7.511. 游戏玩法分析 I

SQL语句如下:

SELECT player_id,
MIN(event_date) AS first_login 
FROM Activity 
GROUP BY player_id 

1
2
3
4
5