MYSQL(特定条件下的SELECT)

Question

我是一个 MYSQL 初学者，我有一个查询问题:

我有两个表(calls，callsfailed)。

1. calls 有这些列( called_number, duration )。
2. callsfailed 有这些列(called_number、release_reason)

让我们说电话表是:

+---------------+----------+
| called_number | duration |
+---------------+----------+
| 1010101       | 13       |
| 1010101       | 18       |
| 1010101       | 20       |
| 2020202       | 50       |
| 2020202       | 20       |
| 3030303       | 10       |
| 4040404       | 30       |
+---------------+----------+

callsfailed 表是:

+---------------+----------------+
| called_number | release_reason |
+---------------+----------------+
| 1010101       | -1             |
| 1010101       | -1             |
| 2020202       | -1             |
| 3030303       | 406            |
| 4040404       | 503            |
| 5050505       | -1             |
| 5050505       | -1             |
| 6060606       | -1             |
+---------------+----------------+

我想选择 called_number > 1，当它使持续时间小于 25 时，或者它使 release_reason = -1。超过 1 次。

BUT If the caller_number made a duration more than 25, don't select it even when it has a release_reason = -1.

所以结果将是:

+---------------+-------+
| called_number | count |
+---------------+-------+
| 1010101       | 3     |
| 5050505       | 2     |
+---------------+-------+

我的代码是:

(  SELECT called_number, duration, COUNT(*) count 
   FROM calls 
   GROUP BY called_number 
   Having COUNT(called_number) > 1 and duration < 25 
) 

UNION 

( SELECT called_number, release_reason, COUNT(*) count 
  FROM callsfailed 
  GROUP BY called_number 
  Having COUNT(called_number) > 1 and release_reason = -1 
)

Answer 1

可能是您正在寻找加入

  select t1.called_number, t1.duration,  t1.count 
  from 

  (  SELECT called_number, duration, COUNT(*) count 
     FROM calls 
     GROUP BY called_number 
     Having COUNT(called_number) > 1 and duration < 25 
  ) t1

  left join 

  ( SELECT called_number, release_reason, COUNT(*) count 
    FROM callsfailed 
    GROUP BY called_number 
    Having COUNT(called_number) > 1 and release_reason = -1 
  )  t2 on t1.called_number = t2.called_number

但是对于工会来说，你可能需要 where for duration e release_reason

      SELECT called_number, duration, COUNT(*) count 
      FROM calls 
      WHERE duration < 25 
      GROUP BY called_number 
      Having COUNT(called_number) > 1 

      union 

      SELECT called_number, release_reason, COUNT(*) count 
      FROM callsfailed 
      GROUP BY called_number 
      where release_reason = -1 
      Having COUNT(called_number) > 1