个人常用语句笔记-SQL

1
(
2
  SELECT
3
    COUNT (*)
4
  FROM
5
    ugc_activity_vote_record uavr
6
  WHERE
7
    uavr.vote_id = o.vote_id
8
  AND uavr.option_id = uavo. ID
9
  AND uavr.created_by = # { USER . ID }
10
  AND TRUNC (uavr.created_date) = TRUNC (SYSDATE)
11
) is_voted

1
(
2
  SELECT
3
    COUNT (*)
4
  FROM
5
    ugc_activity_vote_record uavr
6
  WHERE
7
    uavr.vote_id = o.vote_id
8
  AND uavr.option_id = uavo. ID
9
  AND uavr.created_by = # { USER . ID }
10
  AND to_days (uavr.created_date) = to_days (now())
11
) is_voted,

1
NVL (uavo.votes, 0) AS votes,
2
 (
3
  SELECT
4
    COUNT (*)
5
  FROM
6
    ugc_activity_vote_option vo1
7
  WHERE
8
    vo1.votes > NVL (uavo.votes, 0)
9
  AND vo1.vote_id = o.vote_id
10
) + 1 RANK

1
IFNULL (uavo.votes, 0) AS votes,
2
 (
3
  SELECT
4
    COUNT (*)
5
  FROM
6
    ugc_activity_vote_option vo1
7
  WHERE
8
    vo1.votes > IFNULL (uavo.votes, 0)
9
  AND vo1.vote_id = o.vote_id
10
) + 1 RANK