力扣sql中等篇练习(二十七)

1 连续两年有3个及以上订单的产品

1.1 题目内容

1.1.1 基本题目信息

1.1.2 示例输入输出

1.2 示例sql语句

# Write your MySQL query statement below
WITH T as
(
    SELECT t.product_id,t.d,count(order_id) num
    FROM
    (
        SELECT order_id,product_id,date_format(purchase_date,'%Y') d
        FROM Orders
    )t
    GROUP BY t.product_id,t.d
    HAVING count(order_id)>=3
)
SELECT distinct t1.product_id
FROM T t1
INNER JOIN  T t2
ON t1.product_id=t2.product_id AND ABS(t1.d-t2.d)=1

1.3 运行截图

2 周末任务计数

2.1 题目内容

2.1.1 基本题目信息

2.1.2 示例输入输出

2.2 示例sql语句

# DAYOFWEEK使用时,1代表星期日,7代表星期六
WITH T as
(
  SELECT task_id,assignee_id,
         case when DAYOFWEEK(submit_date)=1 OR DAYOFWEEK(submit_date)=7 then 1
         else 0
         end flag
  FROM Tasks
)
SELECT
(
    SELECT count(task_id)
    FROM T
    WHERE flag=1
)weekend_cnt,
(
    SELECT count(task_id)
    FROM T
    WHERE flag=0
)working_cnt
FROM dual

2.3 运行截图

3 按性别排列表格

3.1 题目内容

3.1.1 基本题目信息

3.1.2 示例输入输出

a 示例输入

b 示例输出

3.2 示例sql语句

# rank()和row_number函数后的结果是可以计算的
SELECT user_id,gender
FROM Genders
ORDER BY (row_number() OVER(partition by gender ORDER BY user_id asc))*3+
 IF(gender='female',1,IF(gender='other',2,3))

3.3 运行截图