sql

Sql code posted
created at 07 May 10:19, updated at 24 Jun 06:19

Edit | Back
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
SET @target_time = '2024-06-24 23:59:59';#対象年月末を指定

SELECT
    user.emtg_id AS 'EMTG ID',
    registered_time AS '入会日',
FROM
    user
INNER JOIN fanclub_user ON user.id = fanclub_user.user_id
INNER JOIN fanclub_user_join_log ON fanclub_user.user_id = fanclub_user_join_log.user_id
INNER JOIN (
    SELECT
        user_id,
        max(created_time) AS created_time
    FROM
        fanclub_user_join_log
    WHERE
        created_time <= @target_time
    GROUP BY
        user_id
) AS latest_join_log ON fanclub_user_join_log.user_id = latest_join_log.user_id
AND fanclub_user_join_log.created_time = latest_join_log.created_time
WHERE
    fanclub_user.deleted_time IS NULL
AND fanclub_user.user_id NOT IN (
    SELECT
        user_id
    FROM
        user_attribute_relation
    WHERE
        user_attribute_id IN (1, 4, 5, 6, 7, 10, 11, 12, 13)
) # 関係者除外
AND fanclub_user_join_log.expire_time > @target_time
GROUP BY
    fanclub_user.user_id
975 Bytes in 3 ms with coderay