Блог

У меня есть groups_areas , groups , groups_members , groups_logs таблицы. Я хочу перечислить все группы в области с описанием и т. groups Д., А Также статистику из groups_members и groups_logs . Вот мой, у меня есть:

 SELECT name, description
FROM groups g
INNER JOIN groups_areas ga ON ga.groupid = g.id WHERE ga.area = ?; 

SELECT (SELECT COUNT(*)
FROM groups_members gm
WHERE gm.groupid = ?) as members,
      (SELECT COUNT(*) FROM groups_logs gl WHERE gl.groupid = ?) as logs;
  

Проблема, на мой взгляд, заключается в том, что мне нужно как-то соотнести идентификатор каждой группы из первого запроса с идентификатором группы второго набора подзапросов. Предоставление мне результирующего набора name, description, members, logs для каждой группы в области.

Структура таблицы (упрощенная):

Группы

 id (int) | name (text) | description (text)
1          engineering   building things
2          sociology     study of people
3          mining        digging up the earth
4          economics     study of various economic processes
  

groups_areas

 groupid (int) | area (int)
1             2
2             2
3             1
4             3
  

groups_members

 groupid (int) | member (int)
1             1
1             2
2             3
4             3
3             2
3             1
  

groups_logs

 groupid (int) | log (int)
1             1
2             4
3             2
3             3
4             5
  

Участники

 id (int) | name (text)
1          max
2          john
3          elf
  

Ожидаемый результат будет строками типа:

 engineering | building things       | 2  | 1
mining | digging up the earth | 2 | 2
...
  

Использование левых соединений и отдельных счетчиков.

 SELECT g.name, g.description, 
COUNT(DISTINCT gm.member) AS members, 
COUNT(DISTINCT gl.log) AS logs
FROM groups g
INNER JOIN groups_areas ga ON (ga.groupid = g.id)
LEFT JOIN groups_members gm ON (gm.groupid = g.id)
LEFT JOIN groups_logs gl ON (gl.groupid = g.id)
WHERE ga.area = ?
GROUP BY g.name, g.description
ORDER BY g.name, g.description;
  

Или без группы by. Присоединение к подзапросам.
Это может быть медленнее при поиске в одной области, но может быть быстрее для всех областей.

 SELECT g.name, g.description, 
gm.total as members, 
gl.total as logs
FROM groups g
INNER JOIN groups_areas ga ON (ga.groupid = g.id)
LEFT JOIN (
  select groupid, count(distinct member) as total 
  from groups_members
  group by groupid
) gm ON (gm.groupid = g.id)
LEFT JOIN (
  select groupid, count(distinct log) as total 
  from groups_logs
  group by groupid
) gl ON (gl.groupid = g.id)
WHERE ga.area = ?
ORDER BY g.name, g.description;
  

Тестовые данные

 create table groups (id int PRIMARY KEY, name text, description text);
insert into groups values (1,'engineering','building things');
insert into groups values (2,'sociology','study of people');
insert into groups values (3,'mining','digging up the earth');
insert into groups values (4,'economics','study of various economic processes');

create table groups_areas (id int AUTO_INCREMENT PRIMARY KEY, groupid int, area int);
insert into groups_areas (groupid, area) values (1,2);
insert into groups_areas (groupid, area) values (2,2);
insert into groups_areas (groupid, area) values (3,1);
insert into groups_areas (groupid, area) values (4,3);

create table groups_members (id int AUTO_INCREMENT PRIMARY KEY, groupid int, member int);
insert into groups_members (groupid, member) values (1,1);
insert into groups_members (groupid, member) values (1,2);
insert into groups_members (groupid, member) values (2,3);
insert into groups_members (groupid, member) values (4,3);
insert into groups_members (groupid, member) values (3,2);
insert into groups_members (groupid, member) values (3,1);

create table groups_logs (id int AUTO_INCREMENT PRIMARY KEY, groupid int, log int);
insert into groups_logs (groupid, log) values (1,1);
insert into groups_logs (groupid, log) values (2,4);
insert into groups_logs (groupid, log) values (3,2);
insert into groups_logs (groupid, log) values (3,3);
insert into groups_logs (groupid, log) values (4,5);