mysql - 使用来自 3 个表的数据查找总行数，MySQL

Question

我想返回一个数字，但我认为查询需要覆盖 3 个表!这是 3 个表的快速示例(切掉了几列)

table: opencall
---------------------------------
Users name | Site  | Phone      |
---------------------------------
John Smith | Leeds | 0113 1234567

table: userdb
-------------------------------------
emailAd         | site  | company   |
-------------------------------------
user@domain.com | Leeds | Yorks Post

table: company
----------------------------
pk_company_id  | division_name |
----------------------------
Yorks Post     | NORTH

现在，我想做的是找到 opencall 中“division_name”为“NORTH”的总行数，我尝试了一些查询，但返回的数字总是太大。

这是我尝试过的一个例子:

SELECT count(*) FROM opencall, company, userdb WHERE userdb.site = opencall.site AND userdb.company = company.pk_company_id AND opencall.logdatex BETWEEN 1385041200 and 1388041200 AND opencall.condition NOT IN (8,9,11,12,19) AND company.division_name = 'NORTH`'

但是，这会返回超过 8,000 的结果，就好像我只是把:

SELECT count(*) FROM opencall where logdatex BETWEEN 1385041200 and 1388041200 AND condition NOT IN (8,9,11,12,19)

这将返回 128，因此我在限制 division_name 时所追求的数字应该在 20 左右。如果对此有任何帮助，我们将不胜感激。

Answer 1

试试这个:

SELECT COUNT(DISTINCT oc.UsersName)
FROM opencall oc 
INNER JOIN userdb u ON oc.site = u.site
INNER JOIN company c ON u.company = c.pk_company_id
WHERE c.division_name = 'NORTH'AND oc.logdatex BETWEEN 1385041200 AND 1388041200 AND 
      oc.condition NOT IN (8,9,11,12,19)