MySql8.0开窗函数

#####MySql8.0开窗函数:

测试数据表:

CREATE TABLE `school_score` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` char(1) DEFAULT NULL,
  `course` char(10) DEFAULT NULL,
  `score` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

INSERT INTO `school_score`(`id`, `name`, `course`, `score`) VALUES (1, 'A', 'Chinese', 80);
INSERT INTO `school_score`(`id`, `name`, `course`, `score`) VALUES (2, 'B', 'Chinese', 90);
INSERT INTO `school_score`(`id`, `name`, `course`, `score`) VALUES (3, 'C', 'Chinese', 70);
INSERT INTO `school_score`(`id`, `name`, `course`, `score`) VALUES (4, 'A', 'Math', 70);
INSERT INTO `school_score`(`id`, `name`, `course`, `score`) VALUES (5, 'B', 'Math', 100);
INSERT INTO `school_score`(`id`, `name`, `course`, `score`) VALUES (6, 'C', 'Math', 80);
INSERT INTO `school_score`(`id`, `name`, `course`, `score`) VALUES (7, 'A', 'English', 90);
INSERT INTO `school_score`(`id`, `name`, `course`, `score`) VALUES (8, 'B', 'English', 85);
INSERT INTO `school_score`(`id`, `name`, `course`, `score`) VALUES (9, 'C', 'English', 99);

• 开窗函数排名 row_number () (partition by ) rank 作为关键字 不能用于别名

  SELECT name,course,score score,
  row_number( ) over (PARTITION by course order by score desc)  as score_rank
  from school_score;

B	Chinese	90	1
A	Chinese	80	2
C	Chinese	70	3
C	English	99	1
A	English	90	2
B	English	85	3
B	Math	100	1
C	Math	80	2
A	Math	70	3

• 查询各科成绩第一的人 或者前两名，传统查询不易实现，或性能低下

  SELECT * from (
  select name,course,score ,row_number() over (PARTITION by course order by score) as score_rank from school_score
  ) as a  where a.score_rank in (1,2);

  C	Chinese	70	1
  A	Chinese	80	2
  B	English	85	1
  A	English	90	2
  A	Math	70	1
  C	Math	80	2

• 不公平排序 会跳过重复的序号

select name,course,rank() over (order by score) as score_rank from school_score;

  C	Chinese	1
  A	Math	1
  A	Chinese	3
  C	Math	3
  B	English	5
  B	Chinese	6
  A	English	6
  C	English	8
  B	Math	9

• 公平排序 dense_rank over ( order by socore) 序号连续 但会出现并列序号 如 1 1 2 3 3 4

  select name,course,score,dense_rank() over (order by score) as score_rank from school_score;

  C	Chinese	70	1
  A	Math	70	1
  A	Chinese	80	2
  C	Math	80	2
  B	English	85	3
  B	Chinese	90	4
  A	English	90	4
  C	English	99	5
  B	Math	100	6

• row_number 排序 不会出现并列 1 2 3

  select name,course,score, row_number() over (order by score) as score_rank from school_score;

  C	Chinese	70	1
  A	Math	70	2
  A	Chinese	80	3
  C	Math	80	4
  B	English	85	5
  B	Chinese	90	6
  A	English	90	7
  C	English	99	8
  B	Math	100	9

• ntile() 分桶，为结果集按行数分成不同的桶，比如 ntile(2) 就对结果集分分两组 nitil(3) 分三组，以此类推，如果分的组或者桶数不均，第一组或第一桶则数量加1

不均等

select name,course,score,  ntile(4) over(order by score desc) as score_rank from school_score;

		 
B	Math	100	1
C	English	99	1
B	Chinese	90	1
A	English	90	2
B	English	85	2
A	Chinese	80	3
C	Math	80	3
C	Chinese	70	4
A	Math	70	4

均等

select name,course,score,  ntile(3) over(order by score desc) as score_rank from school_score;

B	Math	100	1
C	English	99	1
B	Chinese	90	1
A	English	90	2
B	English	85	2
A	Chinese	80	2
C	Math	80	3
C	Chinese	70	3
A	Math	70	3

#####Mybatis实现批量更新：

1.Sql实现：

#批量更新
UPDATE yd_org_purchase_region
    SET
    village_name = CASE id
        WHEN 202 THEN "大兴村"
        WHEN 203 THEN "大兴村"
        WHEN 204 THEN "大兴村"
    END,
		group_name = CASE id
        WHEN 202 THEN "第一组"
        WHEN 203 THEN "第二组"
        WHEN 204 THEN "第三组"
		END
		WHERE id in(202,203,204);

2.Mybatis实现:

<update id="batchUpdateStoreHouse">
        UPDATE yd_organization
        SET org_name = CASE id
          <foreach collection="ydOrganizations" item="org">
              WHEN #{org.id} THEN #{org.orgName}
          </foreach>
              END,
        storehouse_volume = CASE id
        <foreach collection="ydOrganizations" item="org">
              WHEN #{org.id} THEN  #{org.storehouseVolume}
        </foreach>
              END,
          update_time=NOW()
          WHERE
	    id IN (
        <foreach collection="ydOrganizations" item="org" index="index" separator=",">
            #{org.id}
        </foreach>
        )
    </update>

3.Java实现:

public void batchUpdateStoreHouse(List<YdOrganization> storeHouseList) {
        try {
            if (storeHouseList != null && storeHouseList.size() > 0) {
                List<YdOrganization> list = new ArrayList<>();
                for (YdOrganization ydOrganization : storeHouseList) {
                    list.add(ydOrganization);
                    if (list.size() % 30 == 0) {
                        ydOrganizationService.batchUpdateStoreHouse(list);
                        list.clear();
                    }
                }
                ydOrganizationService.batchUpdateStoreHouse(list);
            }
        } catch (Exception e) {
            log.error("", e);
        }
  }

#####MySql分组报错：

#低版本的不严谨查询在高版本严格模式下报错
which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
#设置兼容模式,但推荐使用严格模式
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));