我正在嘗試編寫一個(gè)查詢，提取發(fā)送給朋友的未刪除訊息的總數(shù)。這是表結(jié)構(gòu)的螢?zāi)唤貓D：
我想要實(shí)現(xiàn)的輸出結(jié)果如下：
id messages_count --------------------------- 1 4562 2 7255
這是我迄今為止嘗試過的查詢：
SELECT u.id, count( CASE WHEN m.is_deleted = false THEN m.is_deleted END ) as messages_count FROM users u inner join messages m on u.id = m.to_id GROUP BY u.id ORDER BY u.id;
1
0
0
你嘗試過類似的事情嗎？
SELECT u.id, count( m ) as messages_count FROM users u inner join messages m on u.id = m.to_id and m.is_deleted = false GROUP BY u.id ORDER BY u.id;
