1. https://leetcode-cn.com/problems/lowest-common-ancestor-of-a-binary-tree/
  2. https://leetcode-cn.com/problems/kth-largest-element-in-an-array/
  3. https://leetcode-cn.com/problems/binary-tree-level-order-traversal/
  4. https://leetcode-cn.com/problems/longest-increasing-subsequence/
  5. https://leetcode-cn.com/problems/reverse-nodes-in-k-group/
  6. https://leetcode-cn.com/problems/reorder-list/
  7. https://leetcode-cn.com/problems/reverse-linked-list-ii/
  8. https://leetcode-cn.com/problems/reverse-linked-list/
  9. https://leetcode-cn.com/problems/binary-tree-maximum-path-sum/
  10. https://leetcode-cn.com/problems/longest-substring-without-repeating-characters/
  11. https://leetcode-cn.com/problems/lru-cache/
  12. https://leetcode-cn.com/problems/OrIXps/
  13. https://leetcode-cn.com/problems/lru-cache-lcci/
  14. https://leetcode-cn.com/problems/binary-tree-zigzag-level-order-traversal/
  15. https://leetcode-cn.com/problems/binary-tree-right-side-view/
  16. https://leetcode-cn.com/problems/binary-tree-maximum-path-sum/
  17. https://leetcode-cn.com/problems/merge-k-sorted-lists/
  18. https://leetcode-cn.com/problems/min-stack/
  19. https://leetcode-cn.com/problems/spiral-matrix/
  20. https://leetcode-cn.com/problems/intersection-of-two-linked-lists/
  21. https://leetcode-cn.com/problems/sqrtx/
  22. https://leetcode-cn.com/problems/3sum/
  23. https://leetcode-cn.com/problems/best-time-to-buy-and-sell-stock/
  24. https://leetcode-cn.com/problems/palindrome-linked-list/
  25. https://leetcode-cn.com/problems/trapping-rain-water/
  26. https://leetcode-cn.com/problems/er-jin-zhi-zhong-1de-ge-shu-lcof/
  27. https://leetcode-cn.com/problems/qiu-12n-lcof/
  28. https://leetcode-cn.com/problems/shopping-offers/
  29. https://leetcode-cn.com/problems/shu-zu-zhong-zhong-fu-de-shu-zi-lcof/
  30. https://leetcode-cn.com/problems/shu-zu-zhong-chu-xian-ci-shu-chao-guo-yi-ban-de-shu-zi-lcof/
  31. https://leetcode-cn.com/problemset/all/?search=31&page=1
  32. https://leetcode-cn.com/problems/validate-binary-search-tree/

背景

这两天我在重新规划我的博客网站逐步前行STEP,将改版导航以及整体内容结构,将单纯的博客网站打造成集博客、资讯、工具、社区于一体的综合站点,这必然涉及到要重新规划原有的路由,直接修改路由将导致搜索引擎收录的链接或者访客收藏的网址失效,调整原网址必须保证旧的网址和新的网址同时可用,并且逐渐迁移到新的网址上。

思考

在这一点需求上,网站迁移和网页地址变更都是一致的,可以明确的是,应该在web服务器上考虑解决方案,我使用的是nginx,需要修改配置以达到从原有链接访问跳转到新的链接的效果,也就是URL重定向:rewrite 。以下是我的解决方案:

方案

1、首先需要增加新的路由,

如,我的博文地址从 
`https://www.hezehua.net/csdn_article-105052472`
修改为
`https://www.hezehua.net/blog/csdn_article-105052472`

2、按新旧地址的拼接规则,给nginx配置文件增加rewrite配置
(1)先简单介绍下rewrite语法:

rewrite regex replacement[flag];

根据regex(正则表达式)来匹配内容跳转到replacement,结尾是flag标记

应用位置:server、location、if

flag 参数列表:
last 本条规则匹配完成后继续向下匹配新的location URI规则
break 本条规则匹配完成后终止,不在匹配任何规则
redirect 返回302临时重定向
permanent 返回301永久重定向
(2)、设置重写规则,
如,我设置的规则是:

rewrite ^/csdn_article-([\s\S]+?)(\/|$) /blog/csdn_article-$1 permanent;

在处理关联数组时,经常需要对多维数组按指定字段分组聚合,下面封装了一个数组分组聚合方法:

/**
 * 对数组进行分组聚合
 * @param $array
 * @param $keys
 * @return $result
 */
function array_group_by($array, $keys)
{
    if(!is_array($keys) || count($keys) == 1)
    {
        $key = is_array($keys) ? array_shift($keys) : $keys;

        return array_reduce($array, function($tmp_result, $item) use ($key)
        {
            $tmp_result[$item[$key]][] = $item;

            return $tmp_result;
        });
    }
    else
    {
        $keys = array_values($keys);

        $result = array_group_by($array, array_shift($keys));

        foreach ($result as $k=>$value)
        {
            $result[$k] = array_group_by($value, $keys);
        }

        return $result;
    }
}

测试:

$arr = [
     [
       "first" => "aa",
       "second" => "ccc",
       "third" => "sdfgg",
     ],
     [
       "first" => "aa",
       "second" => "ccc",
       "third" => "dddsa",
     ],
     [
       "first" => "aa",
       "second" => "sdfsdfd",
       "third" => "sdfgg",
     ],
     [
       "first" => "bb",
       "second" => "ccc",
       "third" => "sdfgg",
     ],
     [
       "first" => "bb",
       "second" => "sdfsdfd",
       "third" => "sdfgg",
     ],
   ];
    
    print_r(array_group_by($arr, ['first']));
    /*
    [
     "aa" => [
       [
         "first" => "aa",
         "second" => "ccc",
         "third" => "sdfgg",
       ],
       [
         "first" => "aa",
         "second" => "ccc",
         "third" => "dddsa",
       ],
       [
         "first" => "aa",
         "second" => "sdfsdfd",
         "third" => "sdfgg",
       ],
     ],
     "bb" => [
       [
         "first" => "bb",
         "second" => "ccc",
         "third" => "sdfgg",
       ],
       [
         "first" => "bb",
         "second" => "sdfsdfd",
         "third" => "sdfgg",
       ],
     ],
   ]
    */
    print_r(array_group_by($arr, ['first','second']));
    /*
    [
     "aa" => [
       "ccc" => [
         [
           "first" => "aa",
           "second" => "ccc",
           "third" => "sdfgg",
         ],
         [
           "first" => "aa",
           "second" => "ccc",
           "third" => "dddsa",
         ],
       ],
       "sdfsdfd" => [
         [
           "first" => "aa",
           "second" => "sdfsdfd",
           "third" => "sdfgg",
         ],
       ],
     ],
     "bb" => [
       "ccc" => [
         [
           "first" => "bb",
           "second" => "ccc",
           "third" => "sdfgg",
         ],
       ],
       "sdfsdfd" => [
         [
           "first" => "bb",
           "second" => "sdfsdfd",
           "third" => "sdfgg",
         ],
       ],
     ],
   ]
    */

Mysql的count函数用于统计符合条件的记录数,常用的方式有:

1、count(*)
2、count(1)
3、count(id)
4、count(col)

首先需要明确一点:count函数对于返回的结果集,一行行地判断,不会统计null值。

初学者经常会纠结到底应该使用哪种方式做计数,实际上这四种计数方式是有很大差别的。
count()的原理:

    1. count(*)
      遍历整张表,不需要取出数据来计算,直接按行累计。
    1. count(1)
      遍历整张表,不需要取数,按行计数。
    2. count(id)
      遍历整张表,取出id,按行计数。
    3. count(col)
      遍历整张表,取出col,如果字段定义不为null,取出col之后,按行计数。如果字段定义可以为null,循环对col进行判断是否为null值,再计数。

    这四种计数方式遍历整张表的方式也有不同:

    1. count(*)

      会找任意较小的索引遍历,如果没有二级索引,就会直接遍历主键索引,因为主键索引包含了全表数据,所以在字段比较大的时候,可能会需要频繁去磁盘取数据,导致count(*) 效率低,耗时长,结局方案是给一个小字段加个二级索引,这样count(*) 的时候就会遍历这个二级索引,快速进行计数。
    2. count(1)

          使用索引遍历的选择和 count(*)一致。
    3. count(id)

          使用主键索引遍历
    4. count(col)

          如果col建立了二级索引,则会遍历二级索引,否则主键索引
      

    所以,性能上排序为:count(*) > count(1) > count(id) > count(col)。

    在不考虑是否对null计数得区别的前提下,性能优化的方向,除了使用count(*) 外,就是适当使用小的二级索引。

    mysql的count函数可以计算符合条件的记录条数,比如:

    select count(*) from users;

    执行结果:
    在这里插入图片描述

    上面的sql只是将查询到的记录总数输出,count函数本身还可以配合if函数实现更复杂的计数:

    select count(if(status = 1, 1, null)) from users

    注意,count会将所有非null值计数,所以if里面不符合条件应该返回null。

    如果需要按某个字段计算去重后的数量,则需使用 distinct 关键字:

    select count(distinct last_name) from users

    当需要对1:n的关联查询做统计时,以上简单的count使用方式就不足以实现需求了,比如有以下两个表:

    CREATE TABLE `articles` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `title` varchar(255) NOT NULL COMMENT '标题',
      PRIMARY KEY (`id`)
    );
    
    CREATE TABLE `posts` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `status` tinyint(4) NOT NULL ,
       `article_id` int(10) unsigned NOT NULL
      PRIMARY KEY (`id`)
    ) 

    要在一条sql中查询出所有有帖子(posts)的文章(articles)的数量has_post_cnt、无帖子的文章数量not_post_cnt
    首先必定要做连接来关联文章和帖子:

    select * from articles left join posts on posts.article_id = articles.id 

    一般的思路是连接后再按artiles.id 分组,再在外层对posts.id判断是否等于null:

    select count(if(p_id is not null, 1, null)) has_post_cnt, count(if(p_id is null, 1, null)) not_post_cnt from (
        select articles.id a_id, posts.id p_id from articles left join posts on posts.article_id = artiles.id  group by articles.id
    ) tmp 

    但这样的话子查询效率低,考虑不使用子查询的方式实现,首先就必须去掉分组,不然查询结果只能是按分组聚合的结果,出不了所需的计数,首先看这个sql:

    select count(if(posts.id is not null, 1, null)) has_post_cnt, 
        count(if(posts.id is null, 1, null)) not_post_cnt
        from articles left join posts on posts.article_id = artiles.id

    查询出来的not_post_cnt肯定是对的,因为一条没有帖子的文章和帖子表的连接也就是和null连接,肯定是1:1的,不会有重复记录。
    has_post_cnt由于没有对artiles.id做分组,所以是1:n的,这个数是文章的帖子记录数,考虑同一个文章的帖子记录的article_id字段是唯一的,所以使用distinct来做去重处理:

        select count(distinct if(posts.id is not null, posts.article_id, null)) has_post_cnt, 
        count(if(posts.id is null, 1, null)) not_post_cnt
        from articles left join posts on posts.article_id = artiles.id  group by articles.id

    这里的关键是count(distinct if(p_id is not null, posts.article_id, null)),先做了去重再做了计数,得到的结果即是有帖子记录的文章数。