设为首页 加入收藏

TOP

case用法教程(代码)
2018-04-14 06:06:28 】 浏览:139
Tags:case 用法 教程 代码

case用法教程(代码)

一、case在条件中       

         SELECT

			email_account
		FROM
			mailbox
		WHERE
			company_id = 2
			AND CASE WHEN delete_flag = 1 THEN delete_date >= 20180328
					ELSE  delete_date = 0
					END;	

二、case在select后面

                SELECT
			c1.*,
			case when c2.root_id = 0 then 
					(select u.real_name from `user` u where u.user_id = c2.sales_man) 
	    		 else 
	    		 	(select u.real_name from `user` u where u.user_id in (select c3.sales_man from channel c3 where c3.channel_id = c2.root_id))
	    	end as real_name,
			case when c2.root_id = 0 then 
						c2.channel_type
	    		 else 
	    		 	(select c3.channel_type from channel c3 where c3.channel_id = c2.root_id)
	    	end as channel_type,
			case when c2.root_id = 0 then 
						c2.region
	    		 else 
	    		 	(select c3.region from channel c3 where c3.channel_id = c2.root_id)
	    	end as region,
				c2.channel_status
		FROM
			channel_communication_record c1,
			channel c2
		WHERE
			c2.company_id = #{companyId}

			AND c1.channel_id = c2.channel_id

三、case在update语句中

/**当无法使用from语句时,可以采用连接查询进行过滤(如inner join)**/

UPDATE `user` u INNER JOIN fundinfo f ON FIND_IN_SET(u.user_id,f.im_ids) AND 
!FIND_IN_SET('1',u.jobs)
SET u.jobs = CASE WHEN (ISNULL(u.jobs) OR u.jobs = '') THEN '1' ELSE CONCAT(u.jobs,',1') END;
】【打印繁体】【投稿】【收藏】 【推荐】【举报】【评论】 【关闭】 【返回顶部
上一篇数据库查询索引实例讲解:单个索.. 下一篇mysql中的INFORMATION_SCHEMA使用..

最新文章

热门文章

Hot 文章

Python

C 语言

C++基础

大数据基础

linux编程基础

C/C++面试题目