2205,2228,2230,2238,2292

本节涉及：function函数(2205)、存储过程(2230)、自连接(2228、2292)和delimiter结束符

---

---

2205. The Number of Users That Are Eligible for Discount(function函数)

CREATE FUNCTION getUserIDs(startDate DATE, endDate DATE, minAmount INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
      select count(distinct user_id)
      from Purchases
      where time_stamp between startDate and endDate
      and amount >= minAmount
      
  );
END
1
2
3
4
5
6
7
8
9
10
11

2228. Users With Two Purchases Within Seven Days(自连接)

# Write your MySQL query statement below
select distinct b.user_id
from Purchases a,Purchases b
where a.user_id = b.user_id and
      a.purchase_id <> b.purchase_id and
      abs(datediff(a.purchase_date,b.purchase_date)) <= 7
order by b.user_id 
1
2
3
4
5
6
7

---

2230. 查找可享受优惠的用户(存储过程)

CREATE PROCEDURE getUserIDs(startDate DATE, endDate DATE, minAmount INT)
BEGIN
	# Write your MySQL query statement below.
    select distinct user_id
    from Purchases
    where time_stamp between startDate and endDate
          and amount >= minAmount
    order by user_id;
END
1
2
3
4
5
6
7
8
9

2238. Number of Times a Driver Was a Passenger

# Write your MySQL query statement below
select distinct driver_id,ifnull(zongshu,0) cnt
from Rides R
left join (
select passenger_id ,count(*) zongshu
from Rides
group by passenger_id)new_table
on R.driver_id = new_table.passenger_id

1
2
3
4
5
6
7
8
9

2292. Products With Three or More Orders in Two Consecutive Years

Write an SQL query to report the IDs of all the products that were ordered three or more times in two consecutive years.
Return the result table in any order.
The query result format is shown in the following example.
Example 1:
Input:
Orders table:
±---------±-----------±---------±--------------+
| order_id | product_id | quantity | purchase_date |
±---------±-----------±---------±--------------+
| 1 | 1 | 7 | 2020-03-16 |
| 2 | 1 | 4 | 2020-12-02 |
| 3 | 1 | 7 | 2020-05-10 |
| 4 | 1 | 6 | 2021-12-23 |
| 5 | 1 | 5 | 2021-05-21 |
| 6 | 1 | 6 | 2021-10-11 |
| 7 | 2 | 6 | 2022-10-11 |
±---------±-----------±---------±--------------+
Output:
±-----------+
| product_id |
±-----------+
| 1 |
±-----------+
Explanation:
Product 1 was ordered in 2020 three times and in 2021 three times. Since it was ordered three times in two consecutive years, we include it in the answer.
Product 2 was ordered one time in 2022. We do not include it in the answer.

with new_table as(
select product_id,count(date_format(purchase_date,'%Y')) zongshu,
date_format(purchase_date,'%Y') riqi
from Orders
group by product_id , date_format(purchase_date,'%Y')
order by product_id ,purchase_date
)
select distinct n2.product_id
from new_table n1 ,new_table n2
where n1.product_id = n2.product_id and
      n1.riqi-n2.riqi = -1 and
      n1.zongshu >=3 and 
      n2.zongshu>=3
1
2
3
4
5
6
7
8
9
10
11
12
13

---

总结

mysql:函数（function）

创建无参function函数

creat function fun_name() return int
#注意int是返回类型，可以是date
(
begin
【SQL语句】
end
)
1
2
3
4
5
6
7

创建有参function函数

creat function fun_name(age int ,name  varchar(10)) return int
#注意int是返回类型，可以是date
(
begin
【SQL语句】
end
)
1
2
3
4
5
6
7

使用function函数

SHOW CREATE FUNCTION fun_name(10,'Alice');
1

删除function函数

drop function fun_name;
1

MySQL存储过程

创建存储过程（in类型输入,out输出,inout输入即输出）

create procedure p3(in n int)
begin
    declare total int default 0;
    while n>0 do
            set total := total + n;
            set n := n -1;
        end while;
    select total;
end;

create procedure p4_11(inout score int)
begin
    declare  n int default 100;
    repeat
        set score:=score+n;
        set n:=n-1;
    until n <= 0
    end repeat;
end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

存储过程内定义变量和该表变量值

定义(declear)
declare total int default 0;
改变(set)
set a := 10;
1
2
3
4

使用存储过程

call p3(100);

set @score=0;
call p4_11(@score);
select @score;
1
2
3
4
5

删除存储过程

drop procedure p4_11;
1

存储过程注意事项（下方是自定义[delimiter]结束符）

mysql结束符是；
因为存储函数end结尾前需要有结束符 ; 并表示结束，
在linux中要自定义个其他结束符表示这个函数并没有写完，
所以要自定义结束符

DELIMITER ￥￥
1