回答編集履歴
3
調整
answer
CHANGED
|
@@ -36,8 +36,8 @@
|
|
|
36
36
|
```SQL
|
|
37
37
|
SELECT * FROM `tbl_message_pre`
|
|
38
38
|
WHERE id in(
|
|
39
|
-
SELECT max(id) FROM `tbl_message_pre`
|
|
39
|
+
SELECT max(id) FROM `tbl_message_pre`
|
|
40
|
-
|
|
40
|
+
WHERE board_id IN (SELECT id FROM `tbl_board` WHERE user_name_1='田中' OR user_name_2='田中')
|
|
41
41
|
GROUP BY board_id
|
|
42
42
|
)
|
|
43
43
|
ORDER BY created_at DESC;
|
2
typo
answer
CHANGED
|
@@ -34,8 +34,8 @@
|
|
|
34
34
|
|
|
35
35
|
maxのメッセージidから全データを抜き出してソートするのがこう
|
|
36
36
|
```SQL
|
|
37
|
-
SELECT * FROM `tbl_message_pre`
|
|
37
|
+
SELECT * FROM `tbl_message_pre`
|
|
38
|
-
|
|
38
|
+
WHERE id in(
|
|
39
39
|
SELECT max(id) FROM `tbl_message_pre` WHERE 1
|
|
40
40
|
AND board_id IN (SELECT id FROM `tbl_board` WHERE user_name_1='田中' OR user_name_2='田中')
|
|
41
41
|
GROUP BY board_id
|
1
追記
answer
CHANGED
|
@@ -17,4 +17,29 @@
|
|
|
17
17
|
(tbl_board.user_name_1='田中' OR tbl_board.user_name_2='田中')
|
|
18
18
|
) AS sub
|
|
19
19
|
ORDER BY created_at DESC;
|
|
20
|
-
```
|
|
20
|
+
```
|
|
21
|
+
# 検証
|
|
22
|
+
ざっと検証しました
|
|
23
|
+
まず、田中を含むboard_idを取るのはこう
|
|
24
|
+
```SQL
|
|
25
|
+
SELECT id FROM `tbl_board` WHERE user_name_1='田中' OR user_name_2='田中';
|
|
26
|
+
```
|
|
27
|
+
該当するboard_id毎のmaxのメッセージidをとるのがこう
|
|
28
|
+
```SQL
|
|
29
|
+
SELECT board_id,max(id) FROM `tbl_message_pre`
|
|
30
|
+
WHERE board_id IN (SELECT id FROM `tbl_board` WHERE user_name_1='田中' OR user_name_2='田中')
|
|
31
|
+
GROUP BY board_id;
|
|
32
|
+
```
|
|
33
|
+
※ただし、実際のほしいのはmax(id)だけ
|
|
34
|
+
|
|
35
|
+
maxのメッセージidから全データを抜き出してソートするのがこう
|
|
36
|
+
```SQL
|
|
37
|
+
SELECT * FROM `tbl_message_pre` WHERE 1
|
|
38
|
+
and id in(
|
|
39
|
+
SELECT max(id) FROM `tbl_message_pre` WHERE 1
|
|
40
|
+
AND board_id IN (SELECT id FROM `tbl_board` WHERE user_name_1='田中' OR user_name_2='田中')
|
|
41
|
+
GROUP BY board_id
|
|
42
|
+
)
|
|
43
|
+
ORDER BY created_at DESC;
|
|
44
|
+
|
|
45
|
+
```
|