返回具有特定条件的所有行 [英] Returning all rows with certain conditions

问题描述

首先让我给你们 SQLFiddle:http://sqlfiddle.com/#!2/1d8bd/11

First of all let me give you guys the SQLFiddle: http://sqlfiddle.com/#!2/1d8bd/11

我已经为此工作了一段时间，但面临着一些程序员(和作家)的阻挠.

I've been working on this for a while and am facing some programmers (and writers) block.

这是我试图在 MySQL 中完成的示例伪代码:

This is a sample pseudo code of what I am trying to accomplish in MySQL:

Return all rows WHERE {
  IF `to_user_id` = '27' AND `to_delete` >= SUBDATE(NOW(), INTERVAL 720 HOUR) 
     RETURN to_delete as del_time, COUNT(*), MAX(subject),

      - OR -

  IF `from_user_id` = '27' AND `from_delete` >= SUBDATE(NOW(), INTERVAL 720 HOUR) )
     RETURN from_delete as del_time, COUNT(*), MAX(subject),
} GROUP BY thread_hash

我实际上是在构建一个私人消息系统，这是代表垃圾箱的查询.具有相同 thread_hash 的行代表线程中的一条消息.当用户从他们的收件箱中删除一个线程时，它会将所有 to_delete 设置为该特定 thread_hash 的 NOW().当用户从他们的发送箱中删除一个线程时，它会将所有 from_delete 设置为该特定 thread_hash 的 NOW().

I'm essentially building a private messaging system and this is the query that represents the trash bin. Rows with the same thread_hash represent a messages in a thread. When a user is deletes a thread from their inbox it sets all to_delete to NOW() for that particular thread_hash. When a user is deletes a thread from their sent-box it sets all from_delete to NOW() for that particular thread_hash.

如果 to_user_id = $user_id AND to_delete 有一个值或者如果 from_user_id =$user_id AND from_delete 有一个值，按 thread_hash 分组(即您从收件箱或发送箱中删除的线程)

The query I'm trying to build should return all rows if to_user_id = $user_id AND to_delete has a value OR if from_user_id = $user_id AND from_delete has a value, grouped by the thread_hash (ie threads you've deleted from your inbox, or sent-box)

我希望我在这里说得很清楚.请让我知道我是否可以清除其他任何东西.

I hope I'm being clear here. Please let me know if I can clear anything else up.

推荐答案

你已经接近了，一个简单的 UNION ALL 应该可以做你想做的；

You're close, a simple UNION ALL should do what you want;

SELECT MAX(del_time), COUNT(*) cnt, MAX(subject) subject FROM (
  SELECT to_delete del_time, thread_hash, subject
  FROM messages
  WHERE to_user_id = 27 AND `to_delete` >= SUBDATE(NOW(), INTERVAL 720 HOUR)
  UNION ALL
  SELECT from_delete del_time, thread_hash, subject
  FROM messages
  WHERE from_user_id = 27 AND `from_delete` >= SUBDATE(NOW(), INTERVAL 720 HOUR)
) a
GROUP BY thread_hash;

用于测试的修改后的 SQLfiddle.

这篇关于返回具有特定条件的所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！