Board logo

標題: [操作疑難] 最後回覆 [打印本頁]
作者: carlkyo    時間: 2018-10-30 14:28     標題: 最後回覆

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    時間: 2018-10-30 14:37

SELECT MAX(cp_note.id) ...
作者: ronstudio    時間: 2018-10-30 14:37

select max(timestamp) ?
作者: carlkyo    時間: 2018-10-30 14:46

本帖最後由 carlkyo 於 2018-10-30 15:01 編輯
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    時間: 2018-10-30 14:54

本帖最後由 rkkc 於 2018-10-30 15:03 編輯

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    時間: 2018-10-30 15:08

本帖最後由 carlkyo 於 2018-10-30 15:23 編輯
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    時間: 2018-10-30 15:31

本帖最後由 carlkyo 於 2018-10-30 15:33 編輯
  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    時間: 2018-10-30 15:43

而加冇 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    時間: 2018-10-30 15:49

本帖最後由 carlkyo 於 2018-10-30 15:53 編輯
而加冇 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
作者: 高原熊    時間: 2018-10-30 15:57

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已經係最大
作者: carlkyo    時間: 2018-10-30 16:02

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.pi ...
高原熊 發表於 2018-10-30 15:57



    http://sqlfiddle.com/#!9/c45fd57/1
呢個做到
但reply count錯左
第一同第二個join撞左
many thanks
作者: carlkyo    時間: 2018-10-30 16:05

http://sqlfiddle.com/#!9/c45fd57/11
  1.     SELECT
  2.         t1.*,        
  3.         max(t2.user) as last_replyer,
  4.         COUNT(t2.id) AS reply                  
  5.     FROM
  6.         cp_note t1
  7.     LEFT JOIN cp_note t2 ON(t2.pid = t1.id)
  8.     GROUP BY t1.id
複製代碼
咁好似冇事
作者: carlkyo    時間: 2018-10-30 16:13

[attach]2089788[/attach]
終於做到我要既野
many thanks all
作者: rkkc    時間: 2018-10-30 16:13

max(t2.user) 轉 min(t2.user) 會顯示最先回覆
作者: carlkyo    時間: 2018-10-30 16:26

max(t2.user) 轉 min(t2.user) 會顯示最先回覆
rkkc 發表於 2018-10-30 16:13



    我想要最後回覆
many thanks
作者: rkkc    時間: 2018-10-30 16:34

我想要最後回覆
many thanks
carlkyo 發表於 2018-10-30 16:26


太早開心，max(t2.user) 會 sort sender 名，即是 last_replyer 只是 user 名 descending alphabetical order

試改 user 名 may 變 add，即露底...
作者: rkkc    時間: 2018-10-30 16:39

done!

    SELECT
        t1.*,
        t2.user as last_replyer,
        COUNT(t2.id) AS reply                  
    FROM
        cp_note t1
    LEFT JOIN cp_note t2 ON(t2.pid = t1.id)
    GROUP BY t1.id, t2.user
作者: carlkyo    時間: 2018-10-30 16:57

done!

    SELECT
        t1.*,
        t2.user as last_replyer,
        COUNT(t2.id) AS reply       ...
rkkc 發表於 2018-10-30 16:39


many thanks
作者: carlkyo    時間: 2018-11-5 11:13

本帖最後由 carlkyo 於 2018-11-5 17:48 編輯

還有個問題
請問ching
  1. SELECT t1.*, COUNT(t2.id) AS total, min(t2.user) as last_user FROM cp_note t1 LEFT JOIN cp_note t2 ON (t2.pid = t1.id) GROUP BY t1.id
複製代碼
  1. SELECT t1.*, t2.uid as to_user FROM cp_note t1 LEFT JOIN cp_map t2 ON(mp.note_id=t1.id)
複製代碼
呢兩個query可唔可以合併到啊
many many many thanks
[attach]2090838[/attach]

===>

[attach]2090839[/attach]




歡迎光臨 電腦領域 HKEPC Hardware (https://h2.hkepc.com/forum/) Powered by Discuz! 7.2