优化技巧

• 明知只有一条查询结果，那请使用 “LIMIT 1”
  “LIMIT 1”可以避免全表扫描，找到对应结果就不会再继续扫描了。
• 尽量避免使用 “SELECT *”
  如果不查询表中所有的列，尽量避免使用 SELECT *，因为它会进行全表扫描，不能有效利用索引，增大了数据库服务器的负担，以及它与应用程序客户端之间的网络IO开销。

delete和truncate table

• 如果一个表中有自增字段，使用truncate table和没有WHERE子句的delete删除所有记录后，这个自增字段将起始值恢复成1.如果你不想这样做的话，可以在delete语句中加上永真的WHERE， delete FROM table1 WHERE 1;

总结：
delete可以通过WHERE语句选择要删除的记录。但执行得速度不快。而且还可以返回被删除的记录数。
truncate table无法删除指定的记录，而且不能返回被删除的记录。但它执行得非常快。

from>where>group（含聚合）>having>order>select

聚合语句(sum,min,max,avg,count)要比having子句优先执行，所以having后面可以使用聚合函数。而where子句在查询过程中执行优先级别优先于聚合语句(sum,min,max,avg,count)，所有where条件中不能使用聚合函数。

牛客

---

---

---

---

---

---

复购率

select t1.m, count(t1.m), count(t2.m) from(
    select userid, date_format(paidtime, '%Y-%m-01') as m 
    from orderinfo 
    where ispaid='已支付'
  group by userid, date_format(paidtime, '%Y-%m-01')) as t1
left join(
    select userid, date_format(paidtime, '%Y-%m-01') as m 
    from orderinfo 
    where ispaid='己支付'
  group by userid, date_format(paidtime, '%Y-%m-01')) as t2
on t1.userid=t2.userid and t1.m=date_sub(t2.m, interval 1 month)
group by t1.m;

SQL取出所有用户对商品的行为特征

请用一句SQL取出所有用户对商品的行为特征，特征分为已购买、购买未收藏、收藏未购买、收藏且购买（输出结果如下表）

订单事务表orders 收藏事务表favorites

有一张学生成绩表sc（sno 学号，class 课程，score 成绩），请查询出每个学生的英语、数学的成绩（行转列，一个学生只有一行记录）。

select sno,
sum(if(class='english',score,0)) as english,
sum( if(class='math',score,0) ) as math
from sc
where class in('english','math')
group by sno

假设有一张含两列(用户id、登陆日期)的表，查询每个用户连续登陆的天数、最早登录时间、最晚登录时间和登录次数。
第一步，先用row_number（）函数排序，然后用登录日期减去排名，得到辅助列日期，如果辅助列日期是相同的话，证明用户是连续登录。


第二步，用user_id和辅助列作为分组依据，分到一组的就是连续登录的用户。在每一组中最小的日期就是最早的登陆日期，最大的日期就是最近的登陆日期，对每个组内的用户进行计数就是用户连续登录的天数。

select b.user_id, max(b.date) as 最近登录日期, min(b.date) as 最早登陆日期, count(b.date) as 登陆次数
from (select a.user_id, a.date, a.排序, date_sub(a.date, interval a.排序 day) as 辅助
     from (select distinct user_id, date(login_time) as date, row_number() over (partition by user_id order bylogin_time)  as 排序 form user_login) as a) as b
group by b.user_id,  b.辅助

求解连续登录五天的用户

第一步，用lead函数进行窗口偏移，查找每个用户5天后的登陆日期是多少，如果是空值，说明他没有登录。运行的代码为

在lead函数里，为何偏移行数的参数设置为4而不是5呢，这是因为求解的是连续登录5天的用户，包括当前行在内一共是5行，所以应该向下偏移4行。运行的结果如下：

第二步，用datediff函数计算 （日期-第五次登陆日期）+1是否等于5，等于5证明用户是连续5天登录的，为空值或者大于5都不是5天连续登陆的用户。
第三步，用where设定条件，差值=5筛选连续登录的用户。

用lead函数求解连续登录的问题还有一个好处就是当表中的数据不在同一个月份时也可以完美的解决，不用再考虑月

份带来的影响。