MySql - DISTINCT、HAVING[WHERE] 原创

-----------------------------------
-----------------------------------

-----------------------------------
-----------------------------------

-- CREATE TABLE `user_types` (
--   `id` int NOT NULL AUTO_INCREMENT,
--   `title` varchar(20) NOT NULL DEFAULT '' COMMENT '类型名称',
--   `status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '状态',
--   PRIMARY KEY (`id`)
-- ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3 COMMENT='用户类型表';

-- CREATE TABLE `users` (
--   `id` int unsigned NOT NULL AUTO_INCREMENT,
--   `username` varchar(20) DEFAULT NULL,
--   `status` tinyint(1) DEFAULT '0',
--   `create_time` int NOT NULL DEFAULT '0',
--   `integral` int DEFAULT '0' COMMENT '积分',
--   `type_id` int NOT NULL,
--   PRIMARY KEY (`id`),
--   KEY `fk_user_type` (`type_id`),
--   CONSTRAINT `fk_user_type` FOREIGN KEY (`type_id`) REFERENCES `user_types` (`id`)
-- ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb3 COMMENT='用户表';

-- CREATE TABLE `orders` (
--   `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '订单主键',
--   `user_id` int unsigned NOT NULL DEFAULT '0' COMMENT '用户主键ID',
--   `goods_id` int unsigned NOT NULL DEFAULT '0' COMMENT '商品主键ID',
--   `amount` int unsigned NOT NULL DEFAULT '0' COMMENT '订单价格[单位：分]',
--   `pay_amount` int unsigned NOT NULL DEFAULT '0' COMMENT '订单实际支付金额[单位：分]',
--   `status` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '订单状态：1 未支付 2 已支付 3 已退款 4 已评价 5 已取消',
--   `pay_time` int unsigned NOT NULL DEFAULT '0' COMMENT '订单支付时间',
--   `create_time` int unsigned NOT NULL DEFAULT '0' COMMENT '订单创建时间',
--   PRIMARY KEY (`id`),
--   KEY `user_id` (`user_id`),
--   KEY `status` (`status`)
-- ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb3 COMMENT='用户订单表';


-- DISTINCT 对表中一个或者多个字段重复的数据进行过滤，只返回其中的一条数据，只可以在select中使用
-- HAVING 通常与GROUP BY语句联合使用，用来过滤由GROUP BY语句返回的记录集。

-- DISTINCT
-- distinct的原理[https://blog.csdn.net/zhangzehai2234/article/details/88361586]
-- distinct进行去重的主要原理是通过先对要进行去重的数据进行分组操作，然后从分组后的每组数据中去一条返回给客户端，在这个分组的过程可能会出现两种不同的情况：
    -- 1、distinct 依赖的字段全部包含索引：
    --      该情况mysql直接通过操作索引对满足条件的数据进行分组，然后从分组后的每组数据中去一条数据。

    -- 2、distinct 依赖的字段未全部包含索引：
    --      该情况由于索引不能满足整个去重分组的过程，所以需要用到临时表，mysql首先需要将满足条件的数据放到临时表中，然后在临时表中对该部分数据进行分组，然后从临时表中每个分组的数据中去一条数据，在临时表中进行分组的过程中不会对数据进行排序。
-- 在对字段进行去重的时候，要保证distinct在所有字段的最前面
-- 如果distinct关键字后面有多个字段时，则会对多个字段进行组合去重，只有多个字段组合起来的值是相等的才会被去重
-- DISTINCT * 等于对所有字段进行去重

SELECT DISTINCT username, status from users; -- 相当于把username和status的值拼接到一块之后再进行去重
SELECT COUNT(DISTINCT username, `status`) FROM users;


-- HAVING
-- HAVING语句通常与GROUP BY语句联合使用，用来过滤由GROUP BY语句返回的记录集。
-- HAVING语句的存在弥补了WHERE关键字不能与聚合函数联合使用的不足。
-- 执行完聚合函数之后执行的数据过滤语句

SELECT `status`,AVG(integral) as avg_num FROM users WHERE id > 1 GROUP BY `status` HAVING avg_num > 1 ORDER BY avg_num DESC LIMIT 2;

-- WHERE与HAVING
-- 1、where 后不能跟聚合函数，因为where执行顺序大于聚合函数。
-- 2、where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数，使用where条件显示特定的行。
-- 3、having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件显示特定的组，也可以使用多个分组标准进行分组。
-- 4、WHERE 可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件
-- 5、HAVING 必须要与 GROUP BY 配合使用，可以把分组计算的函数和分组字段作为筛选条件
-- 6、如果需要通过连接从关联表中获取需要的数据，WHERE 是先筛选后连接，而 HAVING 是先连接后筛选。