具有多个条件和子选择的SQL查询

Question

我对使用联接和从多个表中进行选择相当陌生。我已经成功地找到了如何根据许多条件从两个表中获取数据。现在我的问题是，只有在满足了另一个条件时，我才需要返回一个计数。这个想法是这样的:我有一个查询，它返回特定时刻处于特定状态的项目数。我对此的查询是：

SELECT count(*),
       e.campus_id,
       e.course_id
FROM statuses_history AS sh,
     enrolments AS e
WHERE sh.date_added > '2015-08-01 00:00:00'
  AND sh.date_added < '2015-08-20 23:59:59'
  AND sh.status_id = 57
  AND sh.item_id = e.enrolment_id
  AND (e.course_id = 2
       OR e.course_id =7
       OR e.course_id = 8
       OR e.course_id = 9)
GROUP BY e.campus_id,
         e.course_id;

现在，我必须检查它是否处于不同的状态，假设sh.status_id = 50，然后是57。它也不必属于指定的数据范围。因此，我基本上需要以某种方式更改我的查询，以选择是否存在于status_id 50中，然后只有在找到这两个状态时才返回结果。sh.status_id =57将受指定日期的约束。

提前谢谢你的帮助。

Answer 1

您可以根据需要调整子subselect，但如果在数据库中找到另一个值，这将返回一行。

我不喜欢分选，但这应该管用。我还没有测试语法。

select count(*), e.campus_id, e.course_id
FROM statuses_history as sh, enrolments as e
WHERE sh.date_added > '2015-08-01 00:00:00'
      AND sh.date_added < '2015-08-20 23:59:59'
      AND sh.status_id = 57
      AND sh.item_id = e.enrolment_id
      AND (e.course_id = 2 OR e.course_id =7
          OR e.course_id = 8 OR e.course_id = 9)
      AND EXISTS (select sh2.status_id from statuses_history as sh2
                 WHERE NOT sh2.status_id = 57
                   AND sh2.item_id = e.enrolment_id)
GROUP BY e.campus_id, e.course_id;

Answer 2

我假设主键是item_id？

您只需通过主键和status_id = 50连接相同的表。

对于本例，我将使用内部联接(我的首选项)，但您可以使用FROM/WHERE子句。

select count(*), e.campus_id, e.course_id  
FROM statuses_history as sh, enrolments as e

INNER JOIN statuses_history AS oldSh ON oldSh.item_id = sh.item_id AND oldSh.status_id = 50

WHERE sh.date_added > '2015-08-01 00:00:00' AND sh.date_added < '2015-08-20 23:59:59' AND sh.status_id = 57 and sh.item_id = e.enrolment_id AND (e.course_id = 2 OR e.course_id =7 OR e.course_id = 8 OR e.course_id = 9) 
GROUP BY e.campus_id, e.course_id;

但是，如果你喜欢在哪里，你可以做以下几点：

select count(*), e.campus_id, e.course_id  
FROM statuses_history as sh, enrolments as e, statuses_history AS oldSh ON  
WHERE oldSh.item_id = sh.item_id AND oldSh.status_id = 50 AND 
sh.date_added > '2015-08-01 00:00:00' AND sh.date_added < '2015-08-20 23:59:59' AND sh.status_id = 57 and sh.item_id = e.enrolment_id AND (e.course_id = 2 OR e.course_id =7 OR e.course_id = 8 OR e.course_id = 9) 
GROUP BY e.campus_id, e.course_id;

Answer 3

添加一个左联接并使用case语句进行计数。

LEFT JOIN statuses_history sh2 ON sh2.item_id = e.enrollment_id AND sh2.status_id = 50

您需要为内部联接使用不同的格式，以便进行左联接。下面是新的查询：

select count(sh.status_id), e.campus_id, e.course_id  , SUM(CASE WHEN sh2.status_id IS NOT NULL THEN 1 ELSE 0 END) as cnt
FROM statuses_history sh
INNER JOIN enrolments e ON sh.item_id = e.enrolment_id
LEFT JOIN statuses_history sh2 ON sh2.item_id = e.enrollment_id AND sh2.status_id = 50
WHERE sh.date_added > '2015-08-01 00:00:00' AND sh.date_added < '2015-08-20 23:59:59' AND sh.status_id = 57 AND (e.course_id = 2 OR e.course_id =7 OR e.course_id = 8 OR e.course_id = 9) 
GROUP BY e.campus_id, e.course_id;