Сумма баллов для нескольких таблиц в MySQL

Я хотел бы найти сумму столбца в одном запросе с учетом соединений между несколькими таблицами.

У меня есть таблица действий и таблица, которая сопоставляет пользователей, выполняющих действия, а также таблицу, отображающую команды на выполненные действия. И пользователи, и группы могут выполнять одно и то же действие несколько раз.

Каждое действие стоит определенное количество баллов, и я хотел бы знать общее количество баллов для данного пользователя, суммируя их действия с действиями их команды.

Я пробовал различные комбинации соединений между тремя таблицами, но не могу разработать правильный запрос для суммирования баллов для данного пользователя.

Следующий SQL создаст минимальную версию этой установки:

CREATE TABLE `activity` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL DEFAULT '',
  `points` INT(10) UNSIGNED NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=INNODB;

CREATE TABLE `team_action` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `activity_id` INT(11) UNSIGNED NOT NULL,
  `date` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `team_action_ibfk_1` FOREIGN KEY (`activity_id`) REFERENCES `activity` (`id`)
) ENGINE=INNODB;

CREATE TABLE `user_action` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `activity_id` INT(11) UNSIGNED NOT NULL,
  `date` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `user_action_ibfk_1` FOREIGN KEY (`activity_id`) REFERENCES `activity` (`id`)
) ENGINE=INNODB;

INSERT INTO `activity` (`id`, `name`, `points`)
VALUES (1,'Running',10), (2,'Swimming',20), (3,'Hiking',30), (4,'Cycling',40);

INSERT INTO `team_action` (`id`, `activity_id`, `date`)
VALUES (1,2,'2012-05-22 14:32:31'), (2,4,'2012-05-22 14:32:36');

INSERT INTO `user_action` (`id`, `activity_id`, `date`)
VALUES (1,1,'2012-05-22 14:32:08'), (2,1,'2012-05-22 14:32:18'), (3,3,'2012-05-22 14:32:23');

mysql sql aggregate-functions
person Mathew Byrne    schedule 22.05.2012    source источник
comment
каковы отношения пользователя и команды? мне кажется чего-то не хватает. не могу найти связь между team_action и user_action, чтобы получить сумму для пользователя.   -  person Darshana    schedule 22.05.2012
comment
Существуют таблицы как для Пользователя, так и для Команды, и у Пользователя есть внешний ключ для Команды. Для этого примера я не считаю, что показывать эту связь строго необходимо? По сути, меня интересует только то, как я могу полностью правильно суммировать набор строк user_action и team_action. Имеет ли это смысл? Я могу расширить схему, если вы думаете, что это поможет.   -  person Mathew Byrne    schedule 22.05.2012

Ответы (1)


arrow_upward
1
arrow_downward

Из определений таблицы неясно, как пользователи связаны с командами (т. е. для пользователя, как узнать, какая команда является «их»?). Но я думаю, что ключом к суммированию баллов будет использование SUM в результате UNION ALL в подзапросе.

Что-то вроде:

SELECT SUM(points) AS total
FROM
(SELECT points
 FROM team_action JOIN activity ON(activity.id = team_action.activity_id)
 WHERE team_action.id = my_team
 UNION ALL
 SELECT points
 FROM user_action JOIN activity ON(activity.id = user_action.activity_id)
 WHERE user_action.id = my_user) me_and_team
person gcbenison    schedule 22.05.2012
comment
Спасибо, вроде работает правильно. Я намеренно опустил эту связь, чтобы упростить тестовый пример, но ваше предположение о внешних ключах в значительной степени верно. - person Mathew Byrne; 22.05.2012