mysql - 来自不同表的计数补充了来自子结果的另一个计数

Question

我有 3 个表，定义如下。 (文件夹和地址簿条目，连接表中的连接作为其 1:many 表。地址簿可以在多个文件夹中)

我正在尝试获取文件夹列表，这很简单。我添加了文件夹中有多少条目的 COUNT(总计)，这也很简单。

但我还需要添加另一个 COUNT，其中有多少 COUNT(总数)的 addressbook_lastchecked 为 0

或多或少，我需要计算该文件夹中的名称总数，然后再计算其中有多少未选中...

**addressbook**
    addressbook_id
    addressbook_lastchecked
    addressbook_name

**folders**
    folder_id
    folder_name

**folders_connections**
    connection_id
    folder_id
    addressbook_id

到目前为止我的努力..我需要子选择不是吗？

SELECT f . * , c.lastchecked, COUNT( c.lastchecked ) AS entries
FROM folders AS f
LEFT JOIN (

SELECT fc.folder_id, a.lastchecked
FROM folder_connections AS fc
LEFT JOIN addressbook AS a
USING ( addressbook_id ) 
WHERE a.lastchecked =0
) AS c
USING ( folder_id ) 

我的额外努力是(这是不正确的，因为它有重复。)

SELECT f.*, COUNT(c.connection_id) AS entries, COUNT(csub.connection_id) AS unchecked  
FROM folders AS f 
LEFT JOIN folder_connections AS c USING(folder_id)
LEFT JOIN (SELECT fc.*, ab.lastchecked FROM folder_connections AS fc 
     LEFT JOIN addressbook AS ab USING(addressbook_id) WHERE ab.lastchecked=0) AS csub USING(folder_id)
GROUP BY c.folder_id ORDER BY folder_name Asc LIMIT 0, 15

通过混合两种东西解决。

SELECT f.folder_id, f.folder_name, SUM( 
CASE c.lastchecked
WHEN 0 
THEN 1 
ELSE 0 
END ) AS unchecked, COUNT( c.lastchecked ) AS entries
FROM folders AS f
LEFT JOIN (

SELECT fc.folder_id, a.lastchecked
FROM folder_connections AS fc
INNER JOIN addressbook AS a
USING ( addressbook_id )
)c
USING ( folder_id ) 
GROUP BY f.folder_id, f.folder_name
LIMIT 0 , 30

Answer 1

根据我的理解，下面是解决方案:

先取addressbook_lastchecked为0的条目

SELECT fc.folder_id, a.lastchecked
FROM folder_connections AS fc
Inner JOIN addressbook AS a // Inner join is a best option as you get all folders
USING ( addressbook_id ) 
WHERE a.lastchecked =0

//现在您拥有所有 lastchecked 为 0

的文件夹

现在通过组合两个查询来计算文件夹数和上次检查的数。

SELECT f . folder_id,f.folder_name
 , count(c.lastchecked) checkedcount, COUNT( c.lastchecked ) AS entries
FROM folders AS f left join (    SELECT fc.folder_id, a.lastchecked
    FROM folder_connections AS fc
    Inner JOIN addressbook AS a // Inner join is a best option as you get all folders
    USING ( addressbook_id ) 
    WHERE a.lastchecked =0) c
USING ( folder_id ) 
group by f . folder_id,f.folder_name

现在您有了文件夹 ID 以及相应的计数和检查计数。

如果这不是您要找的，请告诉我。