首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >根据最后一个条目获取列值(非null)

问根据最后一个条目获取列值(非null)
EN

Stack Overflow用户
提问于 2013-11-19 23:57:06
回答 3查看 120关注 0票数 2

我在MySQL中有一张桌子,里面有对话。这些会话由消息组成。单个对话如下表所示。

Importance和eId只是偶尔设置的,我想从表中获取的是对话中的最后一条消息(messageId = 4),但是最后一条消息是importance,最后一条消息是eId。

所以,从这张表中

代码语言:javascript
复制
+----------------+-----------+----------+----------+------------+-------+---------+
| conversationId | messageId | time     | status   | importance | eId   | message |
+----------------+-----------+----------+----------+------------+-------+---------+
| 25             | 4         | 11:00:00 | feedback | NULL       | NULL  | d..     |
+----------------+-----------+----------+----------+------------+-------+---------+
| 25             | 3         | 10:00:00 | open     | MEDIUM     | NULL  | c..     |
+----------------+-----------+----------+----------+------------+-------+---------+
| 25             | 2         | 09:00:00 | feedback | NULL       | 123   | b...    |
+----------------+-----------+----------+----------+------------+-------+---------+
| 25             | 1         | 08:00:00 | open     | HIGH       | NULL  | a...    |
+----------------+-----------+----------+----------+------------+-------+---------+

我需要得到这个结果

代码语言:javascript
复制
+----------------+-----------+----------+----------+------------+-------+---------+
| conversationId | messageId | time     | status   | importance | eId   | message |
+----------------+-----------+----------+----------+------------+-------+---------+
| 25             | 4         | 11:00:00 | feedback | MEDIUM     | 123   | d..     |
+----------------+-----------+----------+----------+------------+-------+---------+

我无法执行查询。任何帮助都会得到重视。谢谢。

EN

回答 3

Stack Overflow用户

发布于 2013-11-20 01:12:07

这不一定是最好的解决方案,因为我不得不在我本地的SQL Server中创建它(SQL fiddle在尝试时关闭了,我没有安装MySQL ),还有一个“快速而肮脏”的查询(意思是:可能有更好的解决方案来解决你的问题),但由于我渴望帮助我发布它。

如果您碰巧在等待来自社区的另一个可能更好的解决方案,我不会感到生气:)这就是我想说的:)

代码语言:javascript
复制
SELECT TOP 1 conversationId,messageId,RecTime,ConvStatus,
       (
            SELECT TOP 1 importance
            FROM Conversations
            WHERE importance IS NOT NULL
            ORDER BY messageId DESC
       ) AS importance,
       (
            SELECT TOP 1 eId
            FROM Conversations
            WHERE eId IS NOT NULL
            ORDER BY messageId DESC
       ) AS eId,
UsrMessage
FROM Conversations
ORDER BY messageId DESC
GO

​

(不要忘记,您可能需要更改“格式化项目”才能在MySQL中工作)

​

票数 0
EN

Stack Overflow用户

发布于 2013-11-20 03:21:16

如果表中有多个conversationId,并且您希望同时获得所有conversationIds的所需结果,那么我认为您需要在子查询中联接3个子查询。类似于下面的3条:

代码语言:javascript
复制
SELECT messages.conversationId, messages.messageId, messages.time, messages.status, messages.message
FROM messages
JOIN  (
  SELECT conversationId, MAX(messageId) as messageId
  FROM messages
  GROUP BY conversationId) as m2
ON (messages.messageId = m2.messageId);


SELECT messages.conversationId, messages.importance
FROM messages
JOIN  (
  SELECT conversationId, MAX(messageId) as messageId
  FROM messages
  WHERE importance IS NOT NULL     
  GROUP BY conversationId) as m3
ON (messages.messageId = m3.messageId);

SELECT messages.conversationId, messages.eId
FROM messages
JOIN (
  SELECT conversationId, MAX(messageId) as messageId
  FROM messages
  WHERE eId IS NOT NULL
  GROUP BY conversationId) as m4
ON (messages.messageId = m4.messageId);

连接将如下所示:

代码语言:javascript
复制
SELECT 
main.conversationId, 
main.messageId, 
main.time, 
main.status, 
importance.importance, 
eId.eId, 
main.message
FROM (
  SELECT messages.conversationId, messages.messageId, messages.time, messages.status, messages.message
  FROM messages
  JOIN  (
    SELECT conversationId, MAX(messageId) AS messageId
    FROM messages
    GROUP BY conversationId) AS m2
    ON (messages.messageId = m2.messageId)
        ) AS main
JOIN (
  SELECT messages.conversationId, messages.importance
  FROM messages
  JOIN  (
    SELECT conversationId, MAX(messageId) AS messageId
    FROM messages
    WHERE importance IS NOT NULL
    GROUP BY conversationId) AS m3
    ON (messages.messageId = m3.messageId)
        ) AS importance
JOIN (
  SELECT messages.conversationId, messages.eId
  FROM messages
  JOIN (
    SELECT conversationId, MAX(messageId) AS messageId
    FROM messages
    WHERE eId IS NOT NULL
    GROUP BY conversationId) AS m4
    ON (messages.messageId = m4.messageId)
      ) AS eId
ON (
  main.conversationId = importance.conversationId
  AND main.conversationId = eId.conversationId
    );

这里有一个sqlfiddle:http://sqlfiddle.com/#!2/857aa/38。这假设messageId是唯一的。如果它不是唯一的,那么你需要在messageId和conversationId上加入。

票数 0
EN

Stack Overflow用户

发布于 2013-11-20 08:39:11

可能有点老套,但应该能做到:

代码语言:javascript
复制
SELECT c.conversationId as convId, max(c.messageId), t.time, s.status, i.importance, e.eId, c.message
  FROM convs c,
   (SELECT conversationId, max(time) AS time FROM convs) t,
   (SELECT conversationId, status FROM convs WHERE status IS NOT NULL ORDER BY messageId DESC) s,
   (SELECT conversationId, importance FROM convs WHERE importance IS NOT NULL ORDER BY messageId DESC) i,
   (SELECT conversationId, eId FROM convs WHERE eId IS NOT NULL ORDER BY messageId DESC) e
 WHERE 1=1
   AND t.conversationId = c.conversationId
   AND s.conversationId = c.conversationId
   AND i.conversationId = c.conversationId
   AND e.conversationId = c.conversationId
 GROUP BY c.conversationId

SQL Fiddle demo

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/20076159

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档