CASE WHEN的用法

 

本方法适用情景：需要多表关联，并且在关联后需要根据某字段进行排序的情景。

（1）基本用法讲解。
根据 case when 新的 sort字段排序

case 
    when t2.status = 4
    and t2.expire_time>UNIX_TIMESTAMP()
    and t2.expire_time<UNIX_TIMESTAMP(DATE_ADD(NOW(),INTERVAL 60 day)) then 4
        when `status` = 2  then 3
        when `status` = 3 then 2
        when t2.status = 4
        and t2.expire_time>UNIX_TIMESTAMP(DATE_ADD(NOW(),INTERVAL 60 day))
        and t2.expire_time<UNIX_TIMESTAMP(DATE_ADD(NOW(),INTERVAL 1 year)) then 1
    else 0
    end sort     
    $query->orderBy('sort desc ,t2.expire_time desc,t2.created_at desc');

基本形式如下： 当colume 与condition 条件相等时结果为result；当满足某一条件时，执行某一result

SELECT
CASE case_value
	WHEN when_value THEN
		statement_list
	ELSE
		statement_list
END CASE;

当满足某一条件时,执行某一result，把结果赋值到new_column_name字段中。

case
when condition then result
when condition then result
when condition then result
else result
end 
as new_column_name
```

case when 用在select 语句中，新的字段new_column_name可以用来排序，但是不能用在where中

end 后面加as new_colume_name

（2）实战应用

//表1
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `t_tab_group_ref`;
CREATE TABLE `t_tab_group_ref`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `belong_tab_templete_id` int(11) NULL DEFAULT NULL,
  `group_id` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

//表2
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `t_data_group`;
CREATE TABLE `t_data_group`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `data_group_name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `create_time` datetime(0) NULL DEFAULT NULL,
  `update_time` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

//表3
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `t_group`;
CREATE TABLE `t_group`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `templete_name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `group_type` tinyint(255) NULL DEFAULT NULL,
  `merchants_id` int(11) NULL DEFAULT NULL,
  `create_time` datetime(0) NULL DEFAULT NULL,
  `update_time` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

//表4
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `t_spe_group`;
CREATE TABLE `t_spe_group`  (
  `id` int(11) NOT NULL,
  `spe_group_name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `status` tinyint(4) NULL DEFAULT NULL,
  `create_time` datetime(0) NULL DEFAULT NULL,
  `update_time` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

//查询语句 
//表：t_tab_group_ref R；t_group G； 
//表：t_data_group D； t_spe_group S 
SELECT
CASE R.group_type
	WHEN 1 THEN
		G.templete_name
	WHEN 2 THEN
		D.data_group_name
	WHEN 3 THEN
	    S.spe_group_name
END 
AS group_name,R.id,R.group_type
FROM t_tab_group_ref R
LEFT JOIN t_group G ON R.group_type =1 && R.group_id = G.id
LEFT JOIN t_data_group D ON R.group_type =2 && R.group_id = D.id
LEFT JOIN t_spe_group S ON R.group_type =3 && R.group_id = S.id