个人常用语句笔记-SQL
where in 查询
时间判断
判断是否为今天是否存在记录
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
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
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,
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
空值补全
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
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
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
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
预览
除特别注明外，本站所有文章均为 竹风清语 原创，转载请注明出处来自： gerenchangyongyujubiji-sql
Loading comments...
预览
暂无数据