Mysql之case when用法详解

阿汤哥的程序之路

已于 2022-12-23 17:31:41 修改

阅读量387

点赞数

分类专栏： Mysql 文章标签： mysql 数据库 sql

于 2022-12-19 18:14:27 首次发布

本文链接：https://blog.csdn.net/tangsiqi130/article/details/128375766

版权

Mysql 专栏收录该内容

24 篇文章 0 订阅

订阅专栏

CASE WHEN条件表达式函数

格式：

CASE WHEN condition THEN result
 
[WHEN...THEN...]
 
ELSE result
 
END

condition是一个返回布尔类型的表达式，如果表达式返回true，则整个函数返回相应result的值，如果表达式皆为false，则返回ElSE后result的值，如果省略了ELSE子句，则返回NULL

记录一下
在这里插入图片描述
1.原生sql

SELECT group_attribute.group_id AS group_attribute_group_id, 
max(CASE WHEN (group_attribute.`key` = "test") THEN group_attribute.value END) AS test, 
max(CASE WHEN (group_attribute.`key` = "ad_group") THEN group_attribute.value END) AS ad_group 
FROM group_attribute GROUP BY group_attribute.group_id 
HAVING test="test" AND ad_group="group0831"

2.sqlalchemy

from sqlalchemy import func, case

@staticmethod
def load_by_attributes(search_info, **kwargs):
   group_ids = kwargs.get("group_ids")
   assert isinstance(search_info, dict)
   case_list = [GroupAttribute.group_id]
   for key, value in search_info.items():
       case_obj = func.max(case([(GroupAttribute.key == key, GroupAttribute.value)])).label(key)
       case_list.append(case_obj)
   group_attributes_query = db.session.query(*case_list)
   if group_ids:
       group_attributes_query = group_attributes_query.filter(GroupAttribute.group_id.in_(group_ids))
   group_attributes_query = group_attributes_query.group_by(GroupAttribute.group_id)
   for key, value in search_info.items():
       group_attributes_query = group_attributes_query.having(eval("text('{}=\"{}\"')".format(key, value)))
   group_attributes = group_attributes_query.all()
   return group_attributes