[操作疑難] 最後回覆

carlkyo

sorry, 我想問下各位ching
除左count reply,還可唔可以顯示最後邊個人回覆
希望ching唔好嫌我煩
many thanks

1. SELECT
   
2.     cp_note.*,
   
3.     COUNT(t2.id) AS reply
   
4. FROM
   
5.     cp_note
   
6. LEFT JOIN cp_note t2 ON
   
7.     (t2.parent_id = cp_note.id)
   
8. GROUP BY
   
9.     cp_note.id

複製代碼

rkkc

SELECT MAX(cp_note.id) ...

ronstudio

select max(timestamp) ?

carlkyo

SELECT MAX(cp_note.id) ...
rkkc 發表於 2018-10-30 14:37

我係要max parent id 既record
我google 見到用 order by id desc
    https://stackoverflow.com/questi ... ft-join-with-max-id
本身個query

1. SELECT cp_note.*, cp_user.nickname, cp_c1.c1, cp_c2.c2, cp_status.status_name, IF(ISNULL(cp_reader.uid),1,2) AS isview, COUNT(t2.id) as reply FROM cp_note LEFT JOIN cp_note t2 ON (t2.pid = cp_note.id) LEFT JOIN cp_c1 ON (cp_note.c1 = cp_c1.id) LEFT JOIN cp_c2 ON (cp_note.c2 = cp_c2.id) LEFT JOIN cp_user ON (cp_note.uid = cp_user.id) LEFT JOIN cp_status ON (cp_note.status = cp_status.id) LEFT JOIN cp_reader ON(cp_note.id = cp_reader.note_id AND cp_reader.uid = 1) GROUP BY cp_note.id HAVING cp_note.pid IS NULL AND cp_note.status < 4 ORDER BY istop DESC, cp_note.created DESC

複製代碼

rkkc

SELECT
    cp_note.*,
    cp_user.nickname,
    MAX(t2.parent_id),
    COUNT(t2.id) AS reply
FROM
    cp_note
LEFT JOIN cp_note t2 ON
    (t2.parent_id = cp_note.id)
LEFT JOIN cp_user ON
    (cp_note.uid = cp_user.id)

GROUP BY
        cp_note.id

carlkyo

SELECT
    cp_note.*,
    cp_user.nickname,
    MAX(t2.parent_id),
    COUNT(t2.id) AS reply
FROM
   ...
rkkc 發表於 2018-10-30 14:54

1. SELECT
   
2.     t1.*,
   
3.     COUNT(t2.id) AS reply,
   
4.     t3.user
   
5. FROM
   
6.     cp_note t1
   
7. LEFT JOIN cp_note t2 ON(t2.pid = t1.id)
   
8. LEFT JOIN cp_note t3 ON(t3.pid = t1.id)
   
9. ORDER BY t1.id
   
10. GROUP BY t1.id

複製代碼

from cp_note t1
t3.nickname as last_replyer
left join cp_note t3 on (t3.parent_id = t1.id) <<<依度用max id 定order by id desc??
many thanks

carlkyo

1. SELECT
   
2.     t1.*,
   
3.     COUNT(t2.id) AS reply,
   
4.     max(t3.user) as last_replyer
   
5.                
   
6. FROM
   
7.     cp_note t1
   
8. LEFT JOIN cp_note t2 ON(t2.pid = t1.id)
   
9. LEFT JOIN cp_note t3 ON(t3.pid = t1.id)
   
10. GROUP BY t1.id

複製代碼

好似做到
唔知有冇錯
many thanks

rkkc

而加冇 sql console 試，想了解吓下面嘅 result：

SELECT cp_note.*, cp_user.nickname,
    MAX(cp_note.created)  // timestamp?

FROM cp_note
LEFT JOIN cp_note t2 ON (t2.pid = cp_note.id)
...
LEFT JOIN cp_user ON (cp_note.uid = cp_user.id)

GROUP BY cp_note.id

ORDER BY cp_note.created DESC

carlkyo

而加冇 sql console 試，想了解吓下面嘅 result：

SELECT cp_note.*, cp_user.nickname,
    MAX(cp_note. ...
rkkc 發表於 2018-10-30 15:43

    http://sqlfiddle.com/#!9/c45fd57/1
個count reply 錯左
其他冇事
many thanks

高原熊

Select b.id From cp_node a
Left join cp_node b on (a.id = b.pid)
Left join cp_node c on (a.id = c.pid and c.id > b.id)
Where c.id is null

Inner join,如果c無id 大得過b，者係b.id已經係最大