回答編集履歴
4
追記
answer
CHANGED
|
@@ -38,4 +38,16 @@
|
|
|
38
38
|
on rating.post_id=name.post_id
|
|
39
39
|
and name.meta_key='wpcr3_review_name' and name.meta_value='editor'
|
|
40
40
|
WHERE rating.meta_key='wpcr3_review_rating'
|
|
41
|
+
```
|
|
42
|
+
構造的に三つのメタ属性がセットなら、結合条件と抽出条件に明確に分けた方が、可読性は良くなります。
|
|
43
|
+
```SQL
|
|
44
|
+
SELECT round(AVG(rating.meta_value),1) AS review_avg, count(rating.post_id) AS review_cnt
|
|
45
|
+
FROM postmeta post
|
|
46
|
+
inner join postmeta name
|
|
47
|
+
on post.post_id=name.post_id
|
|
48
|
+
and post.meta_key='wpcr3_review_post' and name.meta_key='wpcr3_review_name'
|
|
49
|
+
inner join postmeta rating
|
|
50
|
+
on post.post_id=rating.post_id
|
|
51
|
+
and rating.meta_key='wpcr3_review_rating'
|
|
52
|
+
WHERE post.meta_value='10' and name.meta_value='editor'
|
|
41
53
|
```
|
3
追記
answer
CHANGED
|
@@ -26,4 +26,16 @@
|
|
|
26
26
|
SELECT 1 FROM postmeta
|
|
27
27
|
WHERE post_id=p.post_id and meta_key='wpcr3_review_name' and meta_value='editor'
|
|
28
28
|
)
|
|
29
|
+
```
|
|
30
|
+
こっちの方が早いかも
|
|
31
|
+
```SQL
|
|
32
|
+
SELECT round(AVG(rating.meta_value),1) AS review_avg, count(rating.post_id) AS review_cnt
|
|
33
|
+
FROM postmeta rating
|
|
34
|
+
inner join postmeta post
|
|
35
|
+
on rating.post_id=post.post_id
|
|
36
|
+
and post.meta_key='wpcr3_review_post' and post.meta_value='10'
|
|
37
|
+
inner join postmeta name
|
|
38
|
+
on rating.post_id=name.post_id
|
|
39
|
+
and name.meta_key='wpcr3_review_name' and name.meta_value='editor'
|
|
40
|
+
WHERE rating.meta_key='wpcr3_review_rating'
|
|
29
41
|
```
|
2
推敲
answer
CHANGED
|
@@ -20,10 +20,10 @@
|
|
|
20
20
|
WHERE meta_key='wpcr3_review_rating'
|
|
21
21
|
and exists(
|
|
22
22
|
SELECT 1 FROM postmeta
|
|
23
|
-
WHERE post_id=p.
|
|
23
|
+
WHERE post_id=p.post_id and meta_key='wpcr3_review_post' and meta_value='10'
|
|
24
24
|
)
|
|
25
25
|
and exists(
|
|
26
26
|
SELECT 1 FROM postmeta
|
|
27
|
-
WHERE post_id=p.
|
|
27
|
+
WHERE post_id=p.post_id and meta_key='wpcr3_review_name' and meta_value='editor'
|
|
28
28
|
)
|
|
29
29
|
```
|
1
推敲
answer
CHANGED
|
@@ -1,5 +1,5 @@
|
|
|
1
1
|
()は正しく閉じられていますか？
|
|
2
|
-
SQL的には以下
|
|
2
|
+
SQL的には以下のはずです。
|
|
3
3
|
```SQL
|
|
4
4
|
SELECT round(AVG(meta_value),1) AS review_avg, count(meta_value) AS review_cnt
|
|
5
5
|
FROM postmeta
|