MySQL在日常开发中遇到常见的问题

MySQL在日常开发中遇到常见的问题

• 行转列----常规做法：group by + sum(if()函数)
  数据表以及数据

  DROP TABLE IF EXISTS `tabel1`;
  CREATE TABLE `tabel1` (
    `year` varchar(255) DEFAULT NULL,
    `month` varchar(255) DEFAULT NULL,
    `amount` varchar(255) DEFAULT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  
  -- ----------------------------
  -- Records of tabel1
  -- ----------------------------
  INSERT INTO `tabel1` VALUES ('1991', '1', '1.5');
  INSERT INTO `tabel1` VALUES ('1992', '3', '3.5');
  INSERT INTO `tabel1` VALUES ('1992', '2', '1.4');
  INSERT INTO `tabel1` VALUES ('1992', '1', '6.9');
  INSERT INTO `tabel1` VALUES ('1992', '1', '0.1');
  INSERT INTO `tabel1` VALUES ('1991', '1', '4.6');
  INSERT INTO `tabel1` VALUES ('1991', '3', '0.8');
  INSERT INTO `tabel1` VALUES ('1991', '2', '0.7');
  INSERT INTO `tabel1` VALUES ('1991', '3', '0.7');
  
   #实现SQL
  SELECT t.`year`,
  SUM(IF(t.`month`=1,t.amount,0)) as 一月,
  SUM(IF(t.`month`=2,t.amount,0)) as 二月,
  SUM(IF(t.`month`=3,t.amount,0)) as 三月
   FROM test.tabel1 t
  GROUP BY t.`year`;
  

• 查询连续登陆3天的用户

  DROP TABLE IF EXISTS `login`;
  CREATE TABLE `login` (
    `date` date DEFAULT NULL,
    `name` varchar(255) DEFAULT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
  
  -- ----------------------------
  -- Records of login
  -- ----------------------------
  INSERT INTO `login` VALUES ('2022-08-01', '张三');
  INSERT INTO `login` VALUES ('2022-08-02', '张三');
  INSERT INTO `login` VALUES ('2022-08-03', '张三');
  INSERT INTO `login` VALUES ('2022-08-04', '张三');
  INSERT INTO `login` VALUES ('2022-08-05', '张三');
  INSERT INTO `login` VALUES ('2022-08-06', '张三');
  INSERT INTO `login` VALUES ('2022-08-07', '张三');
  INSERT INTO `login` VALUES ('2022-08-09', '张三');
  INSERT INTO `login` VALUES ('2022-08-02', '张三');
  INSERT INTO `login` VALUES ('2022-08-06', '张三');
  INSERT INTO `login` VALUES ('2022-08-01', '李四');
  INSERT INTO `login` VALUES ('2022-08-04', '李四');
  INSERT INTO `login` VALUES ('2022-08-06', '李四');
  INSERT INTO `login` VALUES ('2022-08-03', '李四');
  INSERT INTO `login` VALUES ('2022-08-07', '李四');
  INSERT INTO `login` VALUES ('2022-08-09', '李四');
  

  #实现SQL
  #方式一
  WITH t as (
      #去重
    SELECT DISTINCT `date`,`name` FROM test.login
  ),
  t2 AS(
      #排序，加上编号
    SELECT * ,row_number() over ( PARTITION BY name ORDER BY date) as rn
    FROM t
  ),
  t3 AS(
      #构建一个临时时间，如果临时时间相同，证明是连续的
   SELECT *,DATE_SUB(date,INTERVAL rn DAY ) as temp FROM t2
  ),
  t4 as (
      #筛选符合条件的数据
    SELECT name,temp,COUNT(1) FROM t3 GROUP BY name,temp HAVING COUNT(1)>=3
  )
  SELECT DISTINCT name FROM t4;
  
  #方式二：转换成子查询
  .......