在 MySQL 中查找为每个人发送的消息数 [英] Finding the Number of Messages Sent for Every Person in MySQL

问题描述

这是我到目前为止所做的:

This is what I have done so far:

SELECT
    COUNT (message) AS 'Number Of Messages',
    person.id,
    first_name AS 'Sender\'s first name',
    last_name AS 'Sender\'s last name'
FROM person
JOIN message
    ON person.id = message.person_id;

这是个问题:

任务 14:查找为每个人发送的消息数量

Task 14: Find the Number of Messages Sent for Every Person

构造 SQL 语句以查找为每个人发送的消息数.注意:您必须使用 WHERE 子句来设置此查询的条件.显示以下列:

Construct the SQL statement to find the number of messages sent for every person. Note: You must use the WHERE clause to set the conditions for this query. Display the following columns:

-Count of messages
-Person ID
-First Name
-Last Name

这是我们正在使用的表格

here are the tables we are working with

表名:消息

+------------+-----------+-------------+--------------------------------------------+---------------------+
| message_id | sender_id | receiver_id | message                                   | send_datetime      |
+------------+-----------+-------------+--------------------------------------------+---------------------+
|         1 |        1 |          2 | Congrats on winning the 800m Freestyle!   | 2016-12-25 09:00:00 |
|         2 |        2 |          1 | Congrats on winning 23 gold medals!       | 2016-12-25 09:01:00 |
|         3 |        3 |          1 | You're the greatest swimmer ever          | 2016-12-25 09:02:00 |
|         4 |        1 |          3 | Thanks! You're the greatest sprinter ever | 2016-12-25 09:04:00 |
|         5 |        1 |          4 | Good luck on your race                    | 2016-12-25 09:05:00 |
+------------+-----------+-------------+--------------------------------------------+---------------------+

表名:人

+-----------+------------+-----------+
| person_id | first_name | last_name |
+-----------+------------+-----------+
|        1 | Michael   | Phelps   |
|        2 | Katie     | Ledecky  |
|        3 | Usain     | Bolt     |
|        4 | Allyson   | Felix    |
|        5 | Kevin     | Durant   |
|        6 | Diana     | Taurasi  |
+-----------+------------+-----------+

谁能告诉我哪里出错了?

Can anyone show me where I've gone wrong?

谢谢！

推荐答案

我可能太累了，以至于我一直盯着提供答案的人帮助我找到正确的答案.抱歉我不记得了，谢谢！

I may be so tired that I'm staring at the person who gave the answer that help me get the right one. Sorry I cant remember but thank you!

这是正确的:

SELECT 
COUNT(message.message_id) AS 'Count of messages',
person.person_id,
person.first_name AS 'First Name',
person.last_name AS 'Last Name'
FROM message
INNER JOIN person
ON message.sender_id = person.person_id
group by person.person_id,person.first_name,person.last_name;

这篇关于在 MySQL 中查找为每个人发送的消息数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！