MySQL 常规操作合集

case when 多层嵌套 exists写法

			
-- 11代表互相关注00代表两人没有任何关系 01代表我单向关注他 10代表他单向关注我						
SELECT
	uf.user_uid,
	uf.target_uid,
	( CASE WHEN uf.target_uid = '611dfe11176f42abbd62db619dad74e9' THEN 1 ELSE 0 END ) AS is_self,
	(
		CASE
			WHEN (EXISTS ( SELECT 1 FROM kws_user_followers ufollowers WHERE ufollowers.target_uid = uf.target_uid AND ufollowers.is_deleted = 0 AND ufollowers.user_uid = '611dfe11176f42abbd62db619dad74e9' ) 
			AND EXISTS ( SELECT 1 FROM kws_user_following	ufollowing WHERE ufollowing.target_uid = uf.target_uid AND ufollowing.is_deleted = 0 AND ufollowing.user_uid = '611dfe11176f42abbd62db619dad74e9' ))
			THEN
			'11'
			WHEN EXISTS ( SELECT 1 FROM kws_user_followers ufollowers WHERE ufollowers.target_uid = uf.target_uid AND ufollowers.is_deleted = 0 AND ufollowers.user_uid = '611dfe11176f42abbd62db619dad74e9' ) THEN
			'10' 
			WHEN EXISTS ( SELECT 1 FROM kws_user_following	ufollowing WHERE ufollowing.target_uid = uf.target_uid AND ufollowing.is_deleted = 0 AND ufollowing.user_uid = '611dfe11176f42abbd62db619dad74e9' ) THEN
			'01' 
			ELSE '00' 
		END 
		) AS follow_status,
		uf.id,
		uf.uid,
		uf.create_by,
		uf.create_time,
		uf.update_by,
		uf.update_time,
		uf.first_name,
		uf.last_name,
		uf.user_name,
		uf.remark,
		uf.is_deleted 
	FROM
		kws_user_followers uf 
	WHERE
		uf.is_deleted = 0 
		AND user_uid = '20643ad85bc04fe2b277102e987a524f'
						
						
						

时区操作

select 
date_add(now(), interval time_zone hour) 偏移后时区,
HOUR(date_add(now(), interval time_zone hour)) 取小时位,
time_zone 
from tsp_app_account
HAVING HOUR(date_add(now(), interval time_zone hour)) = 22
ORDER BY create_time desc