时间戳

首页 > 解决方案 > SQL select where multiple conditions by same id from a table with two conditions

sql - SQL select where multiple conditions by same id from a table with two conditions

问题描述

请帮助我，我有一个如下表，我想通过 id 获取数据，条件为 2：

  1. responded=> 获取 id where status ='sent'and status = ''andstatus = 'delivered'

  2. pending=> 获取 id where status ='sent'and status = 'delivered，而不是 withstatus = ''

message_thread_id 地位
229
229 发表
229 发送
229 发表
229 发表
240 发送
240 发送
1044
1044
1044
1068 发表

标签： sqlwhere-clause

解决方案

这不是一个很好的方法，但却是一个开始思考的好开始。

我会在存储过程中的临时表中考虑。但是您的问题没有具体说明，因为您没有任何尝试。

我会制作一个包含几列的表，指示用户的特定状态是真还是假，填充它，然后按查询过滤。

但无论如何，这里是“开始思考”的查询：

SELECT 
    message_thread_id, 'responded' as status 
FROM 
    table
WHERE 
    message_thread_id IN (SELECT message_thread_id 
                          FROM table WHERE status = 'sent')
    AND message_thread_id IN (SELECT message_thread_id 
                              FROM table WHERE status = '')
    AND message_thread_id IN (SELECT message_thread_id 
                              FROM table WHERE status = 'delivered')

UNION ALL

SELECT 
    message_thread_id, 'pending' as status 
FROM 
    table
WHERE 
    message_thread_id IN (SELECT message_thread_id FROM table 
                          WHERE status = 'sent')
    AND message_thread_id NOT IN (SELECT message_thread_id 
                                  FROM table WHERE status = '')
    AND message_thread_id IN (SELECT message_thread_id 
                              FROM table WHERE status = 'delivered')

推荐阅读