回答編集履歴
1
訂正を追記
answer
CHANGED
|
@@ -44,4 +44,48 @@
|
|
|
44
44
|
) AS ranking
|
|
45
45
|
GROUP BY ranking.user_id
|
|
46
46
|
ORDER BY total DESC;
|
|
47
|
+
```
|
|
48
|
+
# 訂正
|
|
49
|
+
UNION 句だと値が重複している行が削除されてしまうため、UNION ALL句を使用する必要がありました。
|
|
50
|
+
[https://dev.mysql.com/doc/refman/5.6/ja/union.html](https://dev.mysql.com/doc/refman/5.6/ja/union.html)
|
|
51
|
+
> UNION のデフォルトの動作では、重複した行が結果から削除されます。（中略）オプションの ALL キーワードを指定すると、重複した行の削除は実行されず、その結果には、すべての SELECT ステートメントからの一致するすべての行が含まれます。
|
|
52
|
+
```sql
|
|
53
|
+
SELECT ranking.user_id, SUM(ranking.creds) AS total
|
|
54
|
+
FROM (
|
|
55
|
+
(
|
|
56
|
+
SELECT c.user_id, c.creds
|
|
57
|
+
FROM
|
|
58
|
+
((wp_2_myCRED_log c
|
|
59
|
+
LEFT JOIN wp_2_comments o ON c.ref_id = o.comment_ID)
|
|
60
|
+
INNER JOIN wp_2_term_relationships r ON o.comment_post_ID = r.object_id)
|
|
61
|
+
INNER JOIN wp_2_terms t ON r.term_taxonomy_id = t.term_id
|
|
62
|
+
WHERE
|
|
63
|
+
t.name = "カテゴリ1"
|
|
64
|
+
)
|
|
65
|
+
UNION ALL
|
|
66
|
+
(
|
|
67
|
+
SELECT c.user_id, c.creds
|
|
68
|
+
FROM
|
|
69
|
+
(((wp_2_myCRED_log c
|
|
70
|
+
LEFT JOIN wp_2_posts p ON c.ref_id = p.ID)
|
|
71
|
+
INNER JOIN wp_2_postmeta m ON p.ID = m.post_id)
|
|
72
|
+
INNER JOIN wp_2_term_relationships r ON m.meta_value = r.object_id)
|
|
73
|
+
INNER JOIN wp_2_terms t ON r.term_taxonomy_id = t.term_id
|
|
74
|
+
WHERE
|
|
75
|
+
t.name = "カテゴリ1"
|
|
76
|
+
)
|
|
77
|
+
UNION ALL
|
|
78
|
+
(
|
|
79
|
+
SELECT c.user_id, c.creds
|
|
80
|
+
FROM
|
|
81
|
+
((wp_2_myCRED_log c
|
|
82
|
+
LEFT JOIN wp_2_posts p ON c.ref_id = p.ID)
|
|
83
|
+
INNER JOIN wp_2_term_relationships r ON p.ID = r.object_id)
|
|
84
|
+
INNER JOIN wp_2_terms t ON r.term_taxonomy_id = t.term_id
|
|
85
|
+
WHERE
|
|
86
|
+
t.name = "カテゴリ1"
|
|
87
|
+
)
|
|
88
|
+
) AS ranking
|
|
89
|
+
GROUP BY ranking.user_id
|
|
90
|
+
ORDER BY total DESC;
|
|
47
91
|
```
|