数据库 ·

个人常用语句笔记-SQL

where in 查询

转至:where in与join 查询

时间判断

判断是否为今天是否存在记录

Oracle

使用trunc(时间字段) 函数,获取当前日期用sysdate

(
	SELECT
		COUNT (*)
	FROM
		ugc_activity_vote_record uavr
	WHERE
		uavr.vote_id = o.vote_id
	AND uavr.option_id = uavo. ID
	AND uavr.created_by = # { USER . ID }
	AND TRUNC (uavr.created_date) = TRUNC (SYSDATE)
) is_voted

MySQL

使用to_days(时间字段)函数,获取当前日期用now()

(
	SELECT
		COUNT (*)
	FROM
		ugc_activity_vote_record uavr
	WHERE
		uavr.vote_id = o.vote_id
	AND uavr.option_id = uavo. ID
	AND uavr.created_by = # { USER . ID }
	AND to_days (uavr.created_date) = to_days (now())
) is_voted,

空值补全

Oracle

使用nvl(uavo.votes,0)函数

NVL (uavo.votes, 0) AS votes,
 (
	SELECT
		COUNT (*)
	FROM
		ugc_activity_vote_option vo1
	WHERE
		vo1.votes > NVL (uavo.votes, 0)
	AND vo1.vote_id = o.vote_id
) + 1 RANK

MySQL

使用IFNULL(uavo.votes,0)函数

IFNULL (uavo.votes, 0) AS votes,
 (
	SELECT
		COUNT (*)
	FROM
		ugc_activity_vote_option vo1
	WHERE
		vo1.votes > IFNULL (uavo.votes, 0)
	AND vo1.vote_id = o.vote_id
) + 1 RANK

 

 

参与评论