如何在sqlite中汇总联接表?

使用标量子查询

SELECT A.Job, A.Site, count(B.Job) HeadCount,
(select count(*) FROM tble_1 A1 where A.job=A1.job) as total
FROM tble_1 A LEFT join tble_2 B ON A.Job = B.Job AND A.Site = B.Site 
GROUP BY A.Job

我们可以尝试对每个计数使用两个单独的子查询:

SELECT
    t1.Job,
    t1.Site,
    COALESCE(t3.HeadCount, 0) AS HeadCount,
    COALESCE(t2.SiteCount, 0) AS Total
FROM tble_1 t1
LEFT JOIN
(
    SELECT Job, COUNT(*) AS SiteCount
    FROM tble_1 
    GROUP BY Job
) t2
    ON t1.Job = t2.Job
LEFT JOIN
(
    SELECT A.Job, A.Site, COUNT(*) AS HeadCount
    FROM tble_1 A
    LEFT JOIN tble_2 B
        ON A.Job = B.Job AND A.Site = B.Site
    GROUP BY A.Job, A.Site
) t3
    ON t1.Job = t3.Job AND t1.Site = t3.Site;

Demo

您的示例输出不可能来自帖子中的查询(您只对一列进行分组,结果基于对两列的分组;根据您的查询,每个作业应该只有一行),并且没有样本数据可供测试,因此,我认为这将满足您的需要(需要Sqlite 3.25或更高版本):

SELECT a.job AS Job
     , a.site AS Site
     , count(b.job) AS Headcount
     , sum(count(b.job)) OVER (PARTITION BY a.job) AS Total
FROM tble_1 AS a
LEFT JOIN tble_2 AS b ON a.job = b.job AND a.site = b.site
GROUP BY a.job, a.site;

给出以下示例数据:

CREATE TABLE tble_1(job text, site text);
INSERT INTO tble_1 VALUES('Doctor','US');
INSERT INTO tble_1 VALUES('Doctor','Japan');
INSERT INTO tble_1 VALUES('Doctor','Germany');
INSERT INTO tble_1 VALUES('Artist','Mexico');
INSERT INTO tble_1 VALUES('Actor','Russia');
INSERT INTO tble_1 VALUES('Actor','India');
INSERT INTO tble_1 VALUES('Actor','Mexico');
CREATE TABLE tble_2(job text, site text);
INSERT INTO tble_2 VALUES('Doctor','US');
INSERT INTO tble_2 VALUES('Doctor','Japan');
INSERT INTO tble_2 VALUES('Doctor','Germany');
INSERT INTO tble_2 VALUES('Artist','Mexico');
INSERT INTO tble_2 VALUES('Actor','Russia');
INSERT INTO tble_2 VALUES('Actor','India');
INSERT INTO tble_2 VALUES('Doctor','Germany');
CREATE INDEX tble_1_idx ON tble_1(job, site);
CREATE INDEX tble_2_idx ON tble_2(job, site);

这会产生:

Job         Site        Headcount   Total     
----------  ----------  ----------  ----------
Actor       India       1           2         
Actor       Mexico      0           2         
Actor       Russia      1           2         
Artist      Mexico      1           1         
Doctor      Germany     2           4         
Doctor      Japan       1           4         
Doctor      US          1           4