代码之家 › 专栏 › 技术社区 › Olly

在一个SQL查询中,是否可以跨三个表选择多个条件计数?

sql

Olly · 技术社区 · 14 年前

我的SQL fu太弱了,我甚至不确定是否可以在单个SQL调用中使用。

鉴于我有以下表格:

PARTNER
+----+--------+
| id | name   |
+----+--------+
| 1  | bloggs |
| 2  | jones  |

PARTNER MANAGER
+----+--------------+------+
| id | partner_id   | name |
+----+--------------+------+
| 1  | 1            | fred |
| 2  | 2            | dave |

COMPANY
+----+--------------------+--------+----------+
| id | partner_manager_id | name   | active   |
+----+--------------------+--------+----------+
| 1  | 1                  | comp1  | true     |
| 2  | 1                  | comp2  | false    |
| 3  | 2                  | comp3  | true     |
| 4  | 2                  | comp4  | true     |
| 5  | 2                  | comp5  | true     |
| 6  | 2                  | comp6  | true     |

我想在单个SQL调用中输出以下内容:

+--------------+--------------------+----------------------+
| partner_name | n_active_companies | n_inactive_companies |
+--------------+--------------------+----------------------+
| bloggs       | 1                  | 1                    |
| jones        | 4                  | 0                    |

我可以用两张桌子把这三张桌子连起来 LEFT JOINs 但我如何汇总计数(有或没有 WHERE 条款)在逃避我。

可以说,我找错树了吗?

3 回复 | 直到 14 年前

Mark Byers 14 年前

这让你有了更多的机会:

SELECT
    partner_manager_id,
    SUM(CASE WHEN active THEN 1 ELSE 0 END) AS n_active_companies,
    SUM(CASE WHEN active THEN 0 ELSE 1 END) AS n_inactive_companies
FROM COMPANY
GROUP BY partner_manager_id

剩下的问题基本上是询问如何将这个结果连接到其余的表中。正如您指出的,要做到这一点,请使用连接。

SELECT
    PARTNER.name,
    T1.n_active_companies,
    T1.n_inactive_companies
FROM
PARTNER
LEFT JOIN PARTNER_MANAGER ON partner_id = PARTNER.id
LEFT JOIN
(
    SELECT
        partner_manager_id,
        SUM(CASE WHEN active THEN 1 ELSE 0 END) AS n_active_companies,
        SUM(CASE WHEN active THEN 0 ELSE 1 END) AS n_inactive_companies
    FROM COMPANY
    GROUP BY partner_manager_id
) T1
ON T1.partner_manager_id = PARTNER_MANAGER.id

Randy 14 年前

select p.name "Partner Name" 
, c1.cnt "n_active_companies"
, c2.cnt "n_inactive_companies"
from partner p
, (select partner_manager_id id, count(partner_manager_id) cnt from company where active = 'true' group by partner_manager_id) c1
, (select partner_manager_id id, count(partner_manager_id) cnt from company where active = 'false' group by partner_manager_id) c2
where c1.id = p.id
and c2.id = p.id

chezy525 14 年前

select p.name as 'partner_name',
       sum(case when active then 1 else 0) as 'n_active_companies',
       sum(case when active then 0 else 1) as 'n_inactive_companies'
from COMPANY c
   join PARTNER_MANAGER pm on c.partner_manager_id = pm.id
   join PARTNER p on pm.partner_id = p.id
group by p.name