mysql - 如何使用此 Select 创建动态 WHERE？

Question

您好，我在下面有这个选择，它使用固定值以便于测试。

有效和删除的选择(中间选择)分别返回 2 组数据。

有没有办法动态获取那些中间选择的结果并使用它们在我的 WHERE 中添加 BETWEENS？在纯 SQL 中？

我应该使用它们来制作 where 子句，以便进行更高的选择。中间选择的第一个值是正确进入我的位置，但我丢失了它们各自获取的第二个值，因此我的选择是返回模式数据，然后是预期的。

    select b.bug_id, SUM(b.added) as rework
from    (select distinct b.bug_id, b.added, b.bug_when
        from bugs_activity as b, 
        (select b.bug_id,b.bug_when 
              from bugs_activity as b 
                  where b.bug_id = 13131 
                  and b.fieldid = 8 
                  and b.added like '%Rework%' 
               order by b.bug_when desc limit 500) as worked,
        (select b.bug_id,b.bug_when 
              from bugs_activity as b 
                  where b.bug_id = 13131 
                  and b.fieldid = 8 
                  and b.removed like '%Rework%' 
              order by bug_when desc limit 500) as removed        
        where b.bug_when between worked.bug_when and removed.bug_when
        and b.fieldid = 45
        and b.bug_id = worked.bug_id
        and b.bug_id = removed.bug_id
        and b.bug_id = 13131
        limit 500) as b
group by b.bug_id;

我需要在哪里变成这样的东西(worked.bug_when3 只是为了解释可以是任何东西)

where b.bug_when between worked.bug_when and removed.bug_when
or
b.bug_when between worked.bug_when2 and removed.bug_when2
or
b.bug_when between worked.bug_when3 and removed.bug_when3

Answer 1

HAVING对结果集中的计算值进行操作，并且可以与别名一起使用。

在你的情况下:

SELECT b.bug_id, SUM(b.added) AS rework
FROM
    ...
    LIMIT 500) as b
GROUP BY b.bug_id    
HAVING b.bug_when > 10 AND b.bug_when < 20;

您可能需要在顶部 SELECT 中选择HAVING 所需的字段(即 SELECT b.bug_id, SUM(b.added) AS rework , b.bug_when).

编辑:

有几个中间:

HAVING 
  (b.bug_when > 10 AND b.bug_when < 20) 
  OR (b.bug_when2 > 23 AND b.bug_when2 < 41) 
  OR (b.bug_when3 > 152 AND b.bug_when3 < 241)