回答編集履歴
4
見やすく
test
CHANGED
|
@@ -10,9 +10,15 @@
|
|
|
10
10
|
|
|
11
11
|
|01|000001|testuser1|03|01010102|000001|cccccc|
|
|
12
12
|
|
|
13
|
+
|
|
14
|
+
|
|
13
15
|
|02|000002|testUser2|04|02020202|000002|dddddd|
|
|
14
16
|
|
|
17
|
+
|
|
18
|
+
|
|
15
19
|
|03|000003|testUser3|02|03030303|000003|bbbbbb|
|
|
20
|
+
|
|
21
|
+
|
|
16
22
|
|
|
17
23
|
|04|000004|testUser4|null|null|null|null|
|
|
18
24
|
|
3
USING使用
test
CHANGED
|
@@ -92,7 +92,7 @@
|
|
|
92
92
|
|
|
93
93
|
SELECT U.user_id, U.user_name, COUNT(T.toukou_id) as tCount FROM アカウントTBL U
|
|
94
94
|
|
|
95
|
-
LEFT JOIN 投稿TBL T
|
|
95
|
+
LEFT JOIN 投稿TBL T USING(userId)
|
|
96
96
|
|
|
97
97
|
GROUP BY U.user_id
|
|
98
98
|
|
|
@@ -102,7 +102,7 @@
|
|
|
102
102
|
|
|
103
103
|
SELECT U.userId, COUNT(I.toukouId) as iCount FROM アカウントTBL U
|
|
104
104
|
|
|
105
|
-
LEFT JOIN 投稿画像TBL I
|
|
105
|
+
LEFT JOIN 投稿画像TBL I USING(userId)
|
|
106
106
|
|
|
107
107
|
GROUP BY U.user_id
|
|
108
108
|
|
2
サンプルSQLの追加
test
CHANGED
|
@@ -81,3 +81,37 @@
|
|
|
81
81
|
|
|
82
82
|
|
|
83
83
|
と、なるわけです。
|
|
84
|
+
|
|
85
|
+
|
|
86
|
+
|
|
87
|
+
```
|
|
88
|
+
|
|
89
|
+
SELECT A.userId, A.userName, A.tCount, B.iCount FROM
|
|
90
|
+
|
|
91
|
+
(
|
|
92
|
+
|
|
93
|
+
SELECT U.user_id, U.user_name, COUNT(T.toukou_id) as tCount FROM アカウントTBL U
|
|
94
|
+
|
|
95
|
+
LEFT JOIN 投稿TBL T ON U.userId = T.userId
|
|
96
|
+
|
|
97
|
+
GROUP BY U.user_id
|
|
98
|
+
|
|
99
|
+
) as A
|
|
100
|
+
|
|
101
|
+
LEFT JOIN (
|
|
102
|
+
|
|
103
|
+
SELECT U.userId, COUNT(I.toukouId) as iCount FROM アカウントTBL U
|
|
104
|
+
|
|
105
|
+
LEFT JOIN 投稿画像TBL I ON U.userId = I.userId
|
|
106
|
+
|
|
107
|
+
GROUP BY U.user_id
|
|
108
|
+
|
|
109
|
+
) AS B
|
|
110
|
+
|
|
111
|
+
USING(userId)
|
|
112
|
+
|
|
113
|
+
```
|
|
114
|
+
|
|
115
|
+
|
|
116
|
+
|
|
117
|
+
汚いSQLですみませんが、こんな感じで期待値です。
|
1
見やすく
test
CHANGED
|
@@ -40,9 +40,15 @@
|
|
|
40
40
|
|
|
41
41
|
|01|000001|testuser1|03|01010102|000001|cccccc|03|01010102|000001|img3|
|
|
42
42
|
|
|
43
|
+
|
|
44
|
+
|
|
43
45
|
|02|000002|testUser2|04|02020202|000002|dddddd|04|02020202|000002|img4|
|
|
44
46
|
|
|
47
|
+
|
|
48
|
+
|
|
45
49
|
|03|000003|testUser3|02|03030303|000003|bbbbbb|null|null|null|null|
|
|
50
|
+
|
|
51
|
+
|
|
46
52
|
|
|
47
53
|
|04|000004|testUser4|null|null|null|null|null|null|null|null|
|
|
48
54
|
|