回答編集履歴
6
追記
answer
CHANGED
|
@@ -10,7 +10,7 @@
|
|
|
10
10
|
) as favorite_date
|
|
11
11
|
from action_table user
|
|
12
12
|
where target_type='user' and action_name in ('favorite', 'follow') -- blockは除外
|
|
13
|
-
and exists( -- いいね('favorite')をしてい
|
|
13
|
+
and exists( -- いいね('favorite')をしているユーザー
|
|
14
14
|
select 1 from action_table
|
|
15
15
|
where target_id =user.target_id and action_name='favorite'
|
|
16
16
|
)
|
5
追記
answer
CHANGED
|
@@ -10,7 +10,7 @@
|
|
|
10
10
|
) as favorite_date
|
|
11
11
|
from action_table user
|
|
12
12
|
where target_type='user' and action_name in ('favorite', 'follow') -- blockは除外
|
|
13
|
-
and exists(
|
|
13
|
+
and exists( -- いいね('favorite')をしていないユーザはは除く
|
|
14
14
|
select 1 from action_table
|
|
15
15
|
where target_id =user.target_id and action_name='favorite'
|
|
16
16
|
)
|
4
推敲
answer
CHANGED
|
@@ -9,7 +9,7 @@
|
|
|
9
9
|
case when action_name='favorite' then action_date end
|
|
10
10
|
) as favorite_date
|
|
11
11
|
from action_table user
|
|
12
|
-
where target_type='user' and action_name in ('favorite', 'follow')
|
|
12
|
+
where target_type='user' and action_name in ('favorite', 'follow') -- blockは除外
|
|
13
13
|
and exists(
|
|
14
14
|
select 1 from action_table
|
|
15
15
|
where target_id =user.target_id and action_name='favorite'
|
3
追記
answer
CHANGED
|
@@ -1,3 +1,5 @@
|
|
|
1
|
+
group byで指定している項目以外は、集計(max()とか、count()とか)でないと通常はエラーです。
|
|
2
|
+
mysqlでは設定により、集計を指定しなくても忖度しますがその値は不定（保証されない）です。
|
|
1
3
|
```SQL
|
|
2
4
|
select target_id
|
|
3
5
|
, max(
|
2
推敲
answer
CHANGED
|
@@ -4,8 +4,8 @@
|
|
|
4
4
|
case when action_name='follow' then action_date end
|
|
5
5
|
) as follow_date
|
|
6
6
|
, max(
|
|
7
|
-
|
|
7
|
+
case when action_name='favorite' then action_date end
|
|
8
|
-
|
|
8
|
+
) as favorite_date
|
|
9
9
|
from action_table user
|
|
10
10
|
where target_type='user' and action_name in ('favorite', 'follow')
|
|
11
11
|
and exists(
|
1
追記
answer
CHANGED
|
@@ -14,4 +14,5 @@
|
|
|
14
14
|
)
|
|
15
15
|
and actor_id=1
|
|
16
16
|
group by target_id
|
|
17
|
-
```
|
|
17
|
+
```
|
|
18
|
+
※2も取得する必要があるなら、exsists()の条件を外してください。
|