MySQL-单表自关联查询

场景

三个字段：地区、柜员号、存款金额，不同地区只能存在一个柜员号，即柜员号唯一，柜员号下对应了其处理的存款金额，现需要处理的是：查询出每个地区中，存款金额高于该地区平均存款金额的柜员号记录。单表业务考虑方向：自关联、子查询、sql函数处理……

SQL脚本

/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 50735
 Source Host           : 127.0.0.1:3306
 Source Schema         : sgcc_audit

 Target Server Type    : MySQL
 Target Server Version : 50735
 File Encoding         : 65001

 Date: 17/09/2021 10:07:18
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for depositdeatials
-- ----------------------------
DROP TABLE IF EXISTS `depositdeatials`;
CREATE TABLE `depositdeatials`  (
  `id` int(11) NOT NULL COMMENT '记录ID',
  `region` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '所属机构（地区）',
  `tellerNO` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '所属柜员',
  `amount` int(11) NULL DEFAULT NULL COMMENT '存款金额',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of depositdeatials
-- ----------------------------
INSERT INTO `depositdeatials` VALUES (1, '南昌', '001', 40);
INSERT INTO `depositdeatials` VALUES (2, '宜春', '002', 70);
INSERT INTO `depositdeatials` VALUES (3, '南昌', '003', 60);
INSERT INTO `depositdeatials` VALUES (4, '宜春', '004', 80);
INSERT INTO `depositdeatials` VALUES (5, '南昌', '005', 55);

SET FOREIGN_KEY_CHECKS = 1;

数据结构如图：

功能SQL

先在左连接的临时表中分组查询出每个地区的平均存款金额，再联合筛选出地区相同的行记录作存款金额的比较，非常简单。

SELECT
    a.region,
    a.tellerNo
FROM
    depositDeatials a
    LEFT JOIN
        ( SELECT region, avg( amount ) AS avgamount FROM depositDeatials GROUP BY region ) b
    ON a.region = b.region
WHERE
    a.amount > b.avgamount

查询结果