1. Sorting out the competition list

Data import

DROP TABLE IF EXISTS competition_list; 
CREATE TABLE competition_list( 
team_name VARCHAR(8) 
) 
ENGINE = InnoDB 
DEFAULT CHARSET = utf8; 
INSERT INTO
competition_list (team_name) 
VALUE (' Who competes with the team ') 
 ,(' Winning team ') 
 ,(' The wind and waves team ') 
 ,(' Qunyinghui team ') 
 ,(' Dream team ');

competition_list surface

team_name : Parameter team name

problem : Each parameter team will have a team match with other parameter teams , It is required to output all competition combinations of two teams ( The two are teams A And the team B), And arrange them in ascending order according to the team names

Their thinking

Use the self join of the table , By... Between team names "<" Connect in a way to ensure that the team does not match itself , And sort them in ascending order according to the team name （ Knowledge points involved : Multiple table joins ）

SELECT a.team_name AS  team A, b.team_name AS  team B
FROM competition_list AS a
INNER JOIN competition_list AS b
ON a.team_name < b.team_name
ORDER BY a.team_name, b.team_name;

Result display

2. Products participating in preferential activities

Data import

DROP TABLE IF EXISTS product_promotion; 
CREATE TABLE product_promotion( 
commodity_id VARCHAR(8), 
start_date DATE, 
end_date DATE 
) 
ENGINE = InnoDB 
DEFAULT CHARSET = utf8; 
INSERT INTO 
product_promotion (commodity_id,start_date,end_date) 
VALUE ('a001','2021-01-01','2021-01-06') 
,('a002','2021-01-01','2021-01-10') 
,('a003','2021-01-02','2021-01-07') 
,('a004','2021-01-05','2021-01-07') 
,('b001','2021-01-05','2021-01-10') 
,('b002','2021-01-04','2021-01-06') 
,('c001','2021-01-06','2021-01-08') 
,('c002','2021-01-02','2021-01-04') 
,('c003','2021-01-08','2021-01-15');

product_promotion surface

commodity_id : goods ID

start_date : The start date of the product promotion

end_date : The end date of the product promotion

problem : The query in 2021 year 1 month 7 solstice 2021 year 1 month 9 Products participating in the promotion during the date

Their thinking

hypothesis 2021 year 1 month 7 Day is time a,2021 year 1 month 9 Day is time b, The start time of each offer is s, The end time is e, Then all possible sequences are "sabe"、"saeb"、"asbe"、"aseb"

SELECT commodity_id 
FROM product_promotion 
WHERE (start_date<='2021-01-09' AND start_date>='2021-01-07') 
OR (end_date>='2021-01-07' AND end_date<='2021-01-09') 
OR (end_date>='2021-01-09' AND start_date<='2021-01-07') 
OR (start_date>='2021-01-07' AND end_date<='2021-01-09');

Result display

3. Goods sold continuously

Data import

DROP TABLE IF EXISTS sold_succession; 
CREATE TABLE sold_succession( 
order_id INT, 
commodity_id VARCHAR(8) 
) 
ENGINE = InnoDB 
DEFAULT CHARSET = utf8; 
INSERT INTO 
sold_succession 
(order_id,commodity_id) 
VALUE (1,'c_001') 
 ,(2,'c_001') 
 ,(3,'c_002') 
 ,(4,'c_002') 
 ,(5,'c_002') 
 ,(6,'c_001') 
 ,(7,'c_003') 
 ,(8,'c_003') 
 ,(9,'c_003') 
 ,(10,'c_003') 
 ,(11,'c_001');

sold_succession surface

order_id : Order ID

commodity_id : Purchased goods ID

problem : find Continuous order Greater than or equal to 3 Secondary goods ID

Their thinking

Use window functions LAG(order_id,2), According to the goods ID Grouping , And expand by two lines in ascending order by default

SELECT commodity_id,
       order_id,
       LAG(order_id,2) OVER (PARTITION BY commodity_id ORDER BY order_id) AS temp 
FROM sold_succession;

  Output results

Perform sub query operation in the above output results , If it's continuous 3 The same... Appears twice commodity_id field value , Should be in the same commodity_id The... Of the field value 3 Yes, it appears order_id = temp + 2 The situation of . As shown in the figure above ,commodity_id The field values for c_002, order_id = 5 and temp = 3 This record meets that condition . Add... Outside the subquery WHERE order_id = temp + 2 Make a judgment and de duplicate the result .

Knowledge points involved : Subquery 、 Window function 、DISTINCT

SELECT DISTINCT commodity_id 
FROM 
(SELECT commodity_id,
        order_id,
        LAG(order_id,2) OVER (PARTITION BY commodity_id ORDER BY order_id) AS temp 
 FROM sold_succession )AS a 
WHERE order_id = temp + 2;

Result display