Question

d 希望在单项询问中找到一栏的总和在多个表格之间。

我有一份活动表和一份图表,其中列出了从事一项活动的用户,以及一份表格绘图小组,以开展各项活动。用户和团队可以多次从事同样的活动。

每个活动都值得一提几个要点,我很想知道,某个用户的总点数,将活动与团队活动结合起来。

我尝试了三个表格之间的各种组合,但无法提出准确的问询,以达到特定用户的点数。

The following will establish a minimum edition of this setup:

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 );

Answer 1

从表格中看,用户与团队的关系不明确(即对于用户来说,你知道什么是“他们的”团队?) 但我认为,各点的要点将使用<代码>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

友情链接