关于建筑公司网页设计的毕业论文_互联网创业项目的效果_营销方法有哪些方式_商丘seo推广

一、排序和分组

1. 每位教师所教授的科目种类的数量

1.1. 题目描述

1.2. 解题思路

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

2.1. 题目描述

2.2. 解题思路

3. 销售分析III

3.1. 题目描述

3.2. 解题思路

4. 超过5名学生的课

4.1. 题目描述

4.2. 解题思路

5. 求关注者的数量

5.1. 题目描述

5.2. 解题思路

6. 只出现一次的最大数字

6.1. 题目描述

6.2. 解题思路

7. 买下所有产品的客户

7.1. 题目描述

7.2. 解题思路

8. 银行账户概要 II

8.1. 题目描述

8.2. 解题思路

9. 查找重复的电子邮箱

9.1. 题目描述

9.2. 解题思路

方法一：使用 GROUP BY 和临时表

方法二：使用 GROUP BY 和 HAVING 条件

10. 合作过至少三次的演员和导演

10.1. 题目描述

10.2. 解题思路

方法一

方法二

11. 消费者下单频率

11.1. 题目描述

11.2. 解题思路

12. 每天的领导和合伙人

12.1. 题目描述

12.2. 解题思路

13. 上月播放的儿童适宜电影

13.1. 题目描述

13.2. 解题思路

14. 可以放心投资的国家

14.1. 题目描述

14.2. 解题思路

二、聚合函数

1. 有趣的电影

1.1. 题目描述

1.2. 解题思路

2. 平均售价

2.1. 题目描述

2.2. 解题思路

3. 项目员工 I

3.1. 题目描述

3.2. 解题思路

4. 各赛事的用户注册率

4.1. 题目描述

4.2. 解题思路

5. 查询结果的质量和占比

5.1. 题目描述

5.2. 解题思路

6. 每月交易 I

6.1. 题目描述

6.2. 解题思路

7. 即时食物配送 II

7.1. 题目描述

7.2. 解题思路

8. 游戏玩法分析 IV

8.1. 题目描述

8.2. 解题思路

9. 2020年最后一次登录

9.1. 题目描述

9.2. 解题思路

10. 游戏玩法分析 I

10.1. 题目描述

10.2. 解题思路

11. 仓库经理

11.1. 题目描述

11.2. 解题思路

12. 订单最多的客户

12.1. 题目描述

12.2. 解题思路

分组排序取第一

dense_rank()

13. 查找每个员工花费的总时间

13.1. 题目描述

13.2. 解题思路

14. 即时食物配送 I

14.1. 题目描述

14.2. 解题思路

15. 苹果和桔子

15.1. 题目描述

15.2. 解题思路

方法一

方法二

16. 两人之间的通话次数

16.1. 题目描述

16.2. 解题思路

方法一：巧妙使用IF

方法二：自联结

三、高级字符串函数/正则表达式/子句

1. 修复表中的名字

1.1. 题目描述

1.2. 解题思路

2. 患某种疾病的患者

2.1. 题目描述

2.2. 解题思路

3. 删除重复的电子邮箱

3.1. 题目描述

3.2. 解题思路

4. 第二高的薪水

4.1. 题目描述

4.2. 解题思路

5. 按日期分组销售产品

5.1. 题目描述

5.2. 解题思路

6. 列出指定时间段内所有的下单产品

6.1. 题目描述

6.2. 解题思路

7. 查找拥有有效邮箱的用户

7.1. 题目描述

7.2. 解题思路

四、高级主题：窗口函数和公共表表达式（CTE）

1. 项目员工 III

1.1. 题目描述

1.2. 解题思路

2. 找到连续区间的开始和结束数字

2.1. 题目描述

2.2. 解题思路

方法一：窗口函数

方法二：变量

方法三：关联+筛选

3. 每位顾客最经常订购的商品

3.1. 题目描述

3.2. 解题思路

4. 访问日期之间最大的空档期

4.1. 题目描述

4.2. 解题思路

5. 向公司CEO汇报工作的所有人

5.1. 题目描述

5.2. 解题思路

6. 查找成绩处于中游的学生

6.1. 题目描述

6.2. 解题思路

7. 寻找没有被执行的任务对

7.1. 题目描述

7.2. 解题思路

8. 报告系统状态的连续日期

8.1. 题目描述

8.2. 解题思路

方法一

方法二

一、排序和分组

1. 每位教师所教授的科目种类的数量

1.1. 题目描述

1.2. 解题思路

select teacher_id,count(distinct(subject_id)) as cnt
from Teacher
group by teacher_id;

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

2.1. 题目描述

2.2. 解题思路

selectactivity_date as day,count(distinct user_id) as active_users
from Activity
group byactivity_date
havingactivity_date > subdate('2019-07-27',30) and activity_date <='2019-07-27'

3. 销售分析III

3.1. 题目描述

3.2. 解题思路

select s.product_id,p.product_name
from Sales  s
left join Product p
on s.product_id=p.product_id
group by product_id
having min(sale_date)>='2019-01-01' and max(sale_date)<='2019-03-31'

4. 超过5名学生的课

4.1. 题目描述

4.2. 解题思路

SELECT class
FROM courses
GROUP BY class
HAVING COUNT(DISTINCT student) >= 5;

5. 求关注者的数量

5.1. 题目描述

5.2. 解题思路

SELECT user_id,COUNT(*) AS followers_count
FROMFollowers
GROUP BY user_id
ORDER BY user_id;

6. 只出现一次的最大数字

6.1. 题目描述

6.2. 解题思路

select max(num) as num from 
(SELECTnumFROMMyNumbersGROUP BY numHAVING COUNT(num) = 1
) as t;

7. 买下所有产品的客户

7.1. 题目描述

7.2. 解题思路

select customer_id
from Customer
group by customer_id
having count(distinct product_key) in 
(select count(distinct product_key) from Product);

8. 银行账户概要 II

8.1. 题目描述

8.2. 解题思路

selectu.name,sum(t.amount) as balance
from Users as uleft join Transactions as ton u.account=t.account
group by u.name
having balance>10000;

9. 查找重复的电子邮箱

9.1. 题目描述

9.2. 解题思路

方法一：使用 GROUP BY 和临时表

select Email from
(select Email, count(Email) as numfrom Persongroup by Email
) as statistic
where num > 1;

方法二：使用 GROUP BY 和 HAVING 条件

select Email
from Person
group by Email
having count(Email) > 1;

10. 合作过至少三次的演员和导演

10.1. 题目描述

10.2. 解题思路

方法一

select actor_id,director_id
from (selectactor_id,director_id,count(timestamp) as ctfrom actordirectorgroup by actor_id, director_idhaving ct >= 3
) t;

方法二

selectactor_id,director_id
from actordirector
group by actor_id, director_id
having count(timestamp) >= 3;

11. 消费者下单频率

11.1. 题目描述

11.2. 解题思路

select o.customer_id,c.name
from customers c inner join orders o using(customer_id)left join product p on o.product_id = p.product_id
group by c.customer_id
having sum(if(order_date like '2020-06%', price*quantity,0))>=100 and sum(if(order_date like'2020-07%', price*quantity,0))>=100

12. 每天的领导和合伙人

12.1. 题目描述

12.2. 解题思路

select 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
order by date_id;

13. 上月播放的儿童适宜电影

13.1. 题目描述

13.2. 解题思路

SELECT DISTINCT title
FROM TVProgram AS PLEFT JOINContent AS C USING (content_id)
WHERE P.program_date BETWEEN '2020-06-01' AND '2020-06-30'AND C.Kids_content = 'Y'AND C.content_type = 'Movies';

14. 可以放心投资的国家

14.1. 题目描述

14.2. 解题思路

with country_avg_duration as
(select left(a.phone_number, 3) country_code,avg(b.duration) avg_durationfrom Person ainner join Calls bon a.id = b.caller_idor a.id = b.callee_idgroup by left(a.phone_number, 3)
)
selectb.name as country
from country_avg_duration a
inner join Country b
on a.country_code = b.country_code
where a.avg_duration > (select avg(duration) from Calls)

二、聚合函数

1. 有趣的电影

1.1. 题目描述

1.2. 解题思路

select id, movie, description, rating
from cinema
where description not like "boring" and Mod(id, 2) = 1
order by rating desc;

2. 平均售价

2.1. 题目描述

2.2. 解题思路

select distinct a.product_id,round(sum(a.units*b.price)/sum(a.units),2) as average_price
from UnitsSold as a left join prices  as b  on a.product_id=b.product_id and a.purchase_date between b.start_date and b.end_date 
group by a.product_id

3. 项目员工 I

3.1. 题目描述

3.2. 解题思路

SELECT project_id, ROUND(AVG(experience_years),2) AS average_years
FROM ProjectLEFT JOINEmployee E on Project.employee_id = E.employee_id
GROUP BY project_id;

4. 各赛事的用户注册率

4.1. 题目描述

4.2. 解题思路

select contest_id,round(count(user_id)/(select count(user_id) from Users)*100,2) as percentage
from Register 
group by contest_id
order by percentage desc,contest_id;

5. 查询结果的质量和占比

5.1. 题目描述

5.2. 解题思路

select query_name,round(avg(rating/position),2) as quality,round(avg(rating<3)*100,2) as poor_query_percentage 
from queries 
group by query_name

6. 每月交易 I

6.1. 题目描述

6.2. 解题思路

SELECT DATE_FORMAT(a.trans_date, "%Y-%m") AS month, a.country,
COUNT(a.id) AS trans_count,
COUNT(b.id) AS approved_count,
SUM(a.amount) AS trans_total_amount,
SUM(COALESCE(b.amount,0)) AS approved_total_amount 
FROM Transactions AS a
LEFT JOIN (SELECT id, country, state, amount, DATE_FORMAT(trans_date, "%Y-%m") AS month FROM TransactionsWHERE state = 'approved'
) AS b
ON a.id = b.id
GROUP BY DATE_FORMAT(a.trans_date, "%Y-%m"), a.country;

7. 即时食物配送 II

7.1. 题目描述

7.2. 解题思路

select round(sum(if(order_date=customer_pref_delivery_date,1,0))/count(*)*100, 2) immediate_percentage 
from delivery
where (customer_id, order_date) in (select customer_id, min(order_date) #这里需要注意，仔细分析from deliverygroup by customer_id
)

8. 游戏玩法分析 IV

8.1. 题目描述

8.2. 解题思路

SELECT ROUND(avg(a.event_date IS NOT NULL), 2) fraction
FROM (SELECT player_id, min(event_date) AS loginFROM activityGROUP BY player_id) p 
LEFT JOIN activity a
ON p.player_id = a.player_id AND datediff(a.event_date, p.login) = 1

9. 2020年最后一次登录

9.1. 题目描述

9.2. 解题思路

# Write your MySQL query statement below
select user_id, max(time_stamp) last_stamp
from Logins
where year(time_stamp)=2020
group by user_id

10. 游戏玩法分析 I

10.1. 题目描述

10.2. 解题思路

# Write your MySQL query statement below
select player_id,min(event_date) first_login
from Activity
group by player_id

11. 仓库经理

11.1. 题目描述

11.2. 解题思路

# Write your MySQL query statement below
selectdistinct w.name as `WAREHOUSE_NAME`,sum(t.v*w.units) as `VOLUME`
fromWarehouse as `w` join(selectproduct_id,Width*Length*Height `v`from Products) as `t` on w.product_id = t.product_id
group by w.name

12. 订单最多的客户

12.1. 题目描述

12.2. 解题思路

分组排序取第一

selectcustomer_number
from orders group by customer_number
order by count(customer_number) desc limit 1;

dense_rank()

select customer_number from (selectcustomer_number,dense_rank() over(order by count(*) desc) as rnfrom orders group by customer_number
) temp where rn = 1;

13. 查找每个员工花费的总时间

13.1. 题目描述

13.2. 解题思路

select event_day as day, emp_id as emp_id, sum(out_time - in_time) as total_time
from Employees
group by event_day, emp_id

14. 即时食物配送 I

14.1. 题目描述

14.2. 解题思路

select round((select count(*) from Delivery Where order_date = customer_pref_delivery_date  )/(select count(*) from Delivery),4)*100
as immediate_percentage

15. 苹果和桔子

15.1. 题目描述

15.2. 解题思路

方法一

select sale_date,sum( if(fruit='apples',sold_num,-sold_num)) diff
from Sales
group by sale_date`

方法二

select A.sale_date,a_sold_num-o_sold_num diff from (select sale_date,fruit apples,sold_num a_sold_num from Sales where fruit LIKE 'apples') A inner join (select sale_date,fruit oranges,sold_num o_sold_num from Sales where fruit LIKE 'oranges') O on A.sale_date = O.sale_date order by sale_date

16. 两人之间的通话次数

16.1. 题目描述

16.2. 解题思路

方法一：巧妙使用IF

SELECTIF(from_id<to_id,from_id,to_id) person1,IF(from_id>to_id,from_id,to_id) person2,COUNT(to_id ) as call_count,SUM(duration) as total_duration
FROMCalls
GROUP BY person1,person2

方法二：自联结

select from_id person1,to_id person2,count(1) call_count,sum(duration) total_duration
from (select * from Calls where from_id < to_idunion allselect to_id from_id,from_id to_id,duration from Calls where from_id > to_id) t
group by 1,2 
order by 1,2

三、高级字符串函数/正则表达式/子句

1. 修复表中的名字

1.1. 题目描述

1.2. 解题思路

select user_id , concat(UPPER(SUBSTRING(name,1,1)) , LOWER(SUBSTRING(name,2,LENGTH(name)-1))) name 
from Users 
order by user_id

2. 患某种疾病的患者

2.1. 题目描述

2.2. 解题思路

select patient_id, patient_name, conditions
from Patients
where conditions like '% DIAB1%'
or conditions like 'DIAB1%';

3. 删除重复的电子邮箱

3.1. 题目描述

3.2. 解题思路

DELETE pson1
FROM Person pson1,Person pson2
WHERE pson1.Email = pson2.Email AND pson1.Id > pson2.Id

4. 第二高的薪水

4.1. 题目描述

4.2. 解题思路

select max(Salary) as SecondHighestSalary from Employee 
where Employee.Salary not in (select max(Salary) from Employee )

5. 按日期分组销售产品

5.1. 题目描述

5.2. 解题思路

select sell_date,
count(distinct product) num_sold,
group_concat(distinct product order by product) products
from Activities
group by sell_date
order by sell_date

6. 列出指定时间段内所有的下单产品

6.1. 题目描述

6.2. 解题思路

SELECT P.product_name, sum(O.unit) as unit FROM Products P LEFT JOIN Orders O ON P.product_id = O.product_id AND left(O.order_date,7) = '2020-02' GROUP BY P.product_name HAVING sum(O.unit) >= 100

7. 查找拥有有效邮箱的用户

7.1. 题目描述

7.2. 解题思路

select*
fromUsers
wheremail regexp '^[a-zA-Z][a-zA-Z0-9\_\.\-]*@leetcode\\.com$';

四、高级主题：窗口函数和公共表表达式（CTE）

1. 项目员工 III

1.1. 题目描述

1.2. 解题思路

SELECT P.project_id, P.employee_id
FROM Project AS PLEFT JOINEmployee AS E on P.employee_id = E.employee_id
WHERE (project_id, experience_years) IN (SELECT project_id, MAX(experience_years) AS MAXFROM ProjectLEFT JOINEmployee E on Project.employee_id = E.employee_idGROUP BY project_id);

2. 找到连续区间的开始和结束数字

2.1. 题目描述

2.2. 解题思路

方法一：窗口函数

selectmin(log_id) start_id,max(log_id) end_id
from (selectlog_id,log_id - row_number() over() difffrom logs
) temp group by diff

方法二：变量

selectmin(log_id) start_id,max(log_id) end_id
from (selectlog_id,case when @id = log_id - 1 then @num:=@num else @num:=@num+1 end num,@id:=log_idfrom logs, (select @id:=null, @num:=0) init
) temp group by num;

方法三：关联+筛选

select a.log_id as START_ID ,min(b.log_id) as END_ID from 
(select log_id from logs where log_id-1 not in (select * from logs)) a,
(select log_id from logs where log_id+1 not in (select * from logs)) b
where b.log_id>=a.log_id
group by a.log_id;

3. 每位顾客最经常订购的商品

3.1. 题目描述

3.2. 解题思路

selectb.customer_id,b.product_id,c.product_name
from (selectcustomer_id,product_id,rank() over(partition by customer_id order by cn desc) rkfrom (selectcustomer_id,product_id,count(1) cnfrom Ordersgroup by customer_id,product_id)a
)b
left join Products c
on b.product_id = c.product_id
where rk = 1;

4. 访问日期之间最大的空档期

4.1. 题目描述

4.2. 解题思路

 select
user_id,max(days) biggest_window
from
(selectuser_id, datediff(lead(visit_date,1,'2021-01-01') over(partition by user_id order by visit_date),visit_date ) daysfromUserVisits
) s1
group by
user_id
order by user_id

5. 向公司CEO汇报工作的所有人

5.1. 题目描述

5.2. 解题思路

select distinct e3.employee_id
from employees e1,employees e2,employees e3
where e1.employee_id=e2.manager_idand e2.employee_id=e3.manager_idand e3.employee_id !=1 and e1.manager_id=1

6. 查找成绩处于中游的学生

6.1. 题目描述

6.2. 解题思路

select distinct(Exam.student_id) as STUDENT_ID,Student.student_name as STUDENT_NAME 
from Exam 
join Student on Student.student_id = Exam.student_idwhere Exam.student_id not in(select distinct(student_id) from Exam where (exam_id,score) in(select exam_id,max(score) as score from exam group by exam_id)  # 实验最高分or(exam_id,score) in(select exam_id,min(score) as score from exam group by exam_id)  # 实验最低分) 
order by Exam.student_id asc;

7. 寻找没有被执行的任务对

7.1. 题目描述

7.2. 解题思路

select a.task_id,convert(a.subtask_id,unsigned integer)subtask_id
from(select a.task_id,b.subtask_id from Tasks a,(select @id:=@id+1 subtask_id  from (select @id:=0) as init,Executed)bwhere a.subtasks_count>=b.subtask_idorder by a.task_id,b.subtask_id 
)a left join Executed b on a.task_id=b.task_id and a.subtask_id=b.subtask_id
where b.task_id is null

8. 报告系统状态的连续日期

8.1. 题目描述

8.2. 解题思路

方法一

select period_state,min(date_d) start_date,max(date_d) end_date 
from (select period_state,date_d,subdate(date_d,row_number() over(partition by period_state order by date_d)) rn_diff from (select 'failed' period_state,fail_date date_dfrom failed where fail_date between '2019-01-01' and '2019-12-31'union all select 'succeeded' period_state,success_date date_dfrom succeeded where success_date between '2019-01-01' and '2019-12-31') t) tt 
group by period_state,rn_diff
order by start_date

方法二

select state period_state, min(date) start_date, max(date) end_date
from (select *, row_number() over (partition by state order by date asc) rk1,row_number() over (order by date asc) rk2from (select fail_date 'date', 'failed' state from failedunion allselect success_date, 'succeeded' from succeeded ) t
) t2
where date between '2019-01-01' and '2019-12-31'
group by state, rk2-rk1