MYSQL基操-case when使用

俺们都知道mysql有case when操作可以方便条件查询，今天来介绍一个使用它来进行较为复杂查询的方式

首先我们有如下的数据库table:

CREATE TABLE `t_dynamic_main` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '用户ID',
  `d_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '分表主键id',
  `type` tinyint(1) NOT NULL DEFAULT '0' COMMENT '动态类型（0：普通用户动态，1：抖音链接，2长文）',
  `time` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '发表时间',
  `comments` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '评论数量',
  `clicks` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '点击数',
  `praise` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '点赞数量',
  `ctime` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '热度时间',
  `status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '文章状态（-1已删除,0编辑中，1已发布）',
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_udid` (`user_id`,`d_id`) USING BTREE,
  KEY `index_time` (`time`),
  KEY `index_ctime` (`ctime`),
  KEY `index_status` (`status`),
  KEY `index_comments` (`comments`)
) ENGINE=InnoDB AUTO_INCREMENT=95 DEFAULT CHARSET=utf8mb4 COMMENT='圈子动态总表';
然后我们当前的需求是这样子的:

按热度排序逻辑: 近2日，按热度时间倒序
               更早则按数值倒序 文章：按点击数倒叙，动态/视频 ：按点击数+点赞数+评论数 倒叙
按时间排序逻辑：按发布时间降序

这个查询起来就可以使用case when的方式来辅助查询
比如按时间排序(这个和普通查询一样):

SELECT
	`user_id`,
	`d_id`,
	`type`,
	`time`,
	`ctime`,
	`comments`,
	`praise`,
	`clicks`,
CASE
		
		WHEN `time` >= 1610294400 THEN
		1 ELSE 0 
	END `is_nearly`,
CASE
		
		WHEN `type` = 2 THEN
		1 ELSE 0 
	END `is_article`,
CASE
		
		WHEN `type` = 2 THEN
		clicks ELSE ( comments + praise + clicks ) 
	END AS total_factor 
FROM
	`db_tbp_community`.`t_dynamic_main` 
WHERE
	`status` = 1 
ORDER BY
	`time` DESC 
	LIMIT 0,
	25

那如果想要按照热度查询怎么搞呢:

SELECT
	`user_id`,
	`d_id`,
	`type`,
	`time`,
	`ctime`,
	`comments`,
	`praise`,
	`clicks`,
CASE
		WHEN `time` >= 1610294400 THEN
		1 ELSE 0 
	END `is_nearly`,
CASE
		
		WHEN `type` = 2 THEN
		1 ELSE 0 
	END `is_article`,
CASE
		
		WHEN `type` = 2 THEN
		clicks ELSE ( comments + praise + clicks ) 
	END AS total_factor 
FROM
	`db_tbp_community`.`t_dynamic_main` 
WHERE
	`status` = 1 
ORDER BY
	`is_nearly` DESC,
CASE
		
		WHEN `is_nearly` = 1 THEN
		`ctime` ELSE `total_factor` 
END DESC 
	LIMIT 0,
	25

可以看到后面的orderby里面，只需要根据条件排序就可以很方便的做出排序。如果还有别的复杂要求，比如分组，可以groupby之后使用having等都可以方便查询，这样子一条sql就可以完成需求，然后可以推送入缓存，5分钟刷新一次等，就可以完成文章、动态等仿照朋友圈的一个刷新方式