SQL组超过两个加入

Question

这些是我的表格：

create table articles(
    id integer auto_increment,
    site varchar(64) not null,
    title varchar(512) not null,
    link varchar(512) not null,
    pub_date datetime not null,
    primary key(id)
);
create table entities (
    id integer auto_increment,
    entity varchar(1024) not null,
    entity_type varchar(32) not null,
    primary key(id)
);
create table articles_entities (
    article_id integer not null,
    entity_id integer not null,
    foreign key(entity_id) references entities(id),
    foreign key(article_id) references articles(id)
);

这是我要做的事情的起点：

select count(*), entity_type from entities group by entity_type;

我显然得到了看起来像这样的东西：

+----------+---------------+
| count(*) | entity_type   |
+----------+---------------+
|    15418 | locations     |
|    21789 | misc          |
|    62306 | organizations |
|   121307 | people        |
+----------+---------------+

我想要的就是这样：

+----------+---------------+------+
| count(*) | entity_type   | site |
+----------+---------------+------+
|    15418 | locations     | ABC  |
|    21789 | misc          | ABC  |
|    62306 | organizations | ABC  |
|   121307 | people        | ABC  |
|    13418 | locations     | CNN  |
|    22789 | misc          | CNN  |
|    65306 | organizations | CNN  |
|   132307 | people        | CNN  |
+----------+---------------+------+

如何设置该查询以提供这种计数？

Answer 1

您需要join：

select count(*), entity_type, site 
from entities 
left join articles_entities on entity_id = entities.id
left join articles on article_id = articles.id
group by entity_type, site;

我使用了left join，假设您可能具有与结果中包含的文章不符的实体。如果不是这种情况，则可以删除lefts。