mysql group by select with condition value

In my messages table I have following rows for example,

在我的消息表中,我有以下行,例如,

|----|---------|--------------|------|
| id | user_id | message      |status|
|====|=========|==============|======|
| 1  |  2      | msgs 11      |  r   |
|----|---------|--------------|------|
| 2  |  3      | msgs 12      |  r   |
|----|---------|--------------|------|
| 3  |  2      | msgs 13      |  r   |
|----|---------|--------------|------|
| 4  |  3      | msgs 14      |  u   |
|----|---------|--------------|------|

Now, I need to know two things for each user_id

现在,我需要为每个user_id知道两件事

• Whether it has any status u or not.

它是否具有任何状态。

• How many messages are there

那里有多少条消息

For example, a query like below select user_id, status, count(*) as totalMsg from messages group by user_id Would brought me following rows

例如,像下面这样的查询选择user_id,status,count(*)作为来自user_id的消息组中的totalMsg将带给我以下行

| user_id | status| totalMsg |
|=========|=======|==========|
|  2      |   r   |    2     |
|---------|-------|----------|
|  3      |   r   |    2     | 
              ^
              |------> I need this value to be 'u' because user 3 has a message u

My current query doesnt really gurantee that it will look for a u in the status column. Is that possible to do? If so how?

我当前的查询并没有真正保证它会在状态列中查找你。这可能吗?如果是这样的话?

1 个解决方案

#1

MAX() will work on this since r is the least value based on the lexicographical order.

MAX()将对此起作用,因为r是基于字典顺序的最小值。

SELECT  user_ID,
        MAX(status) status,
        COUNT(*) totalMsg
FROM    messages
GROUP   BY user_ID