MySQL Query 整合来自 3 个表的信息(有很多障碍)

Question

背景:在一项实验中，蜜蜂的背上粘有数字标签，这些数字标签以及它们在实验室中的选择都会被记录下来。没有足够的数字标签(2 位数字和一些颜色选项)，需要重复使用。然而，标签只有在携带标签的人死亡后才能重复使用。因此，在数据结构中我们偶尔会看到蜜蜂标识符，但知道它是否来自同一只蜜蜂的唯一方法是通过查看另一个表来查看蜜蜂是否死亡。

表格:蜜蜂做出的选择

CREATE TABLE `exp8` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `bee_id` varchar(255) DEFAULT NULL,
  `date_time` datetime DEFAULT NULL,
  `choice` varchar(255) DEFAULT NULL,
  `hover_duration` int(11) DEFAULT NULL,
  `antennate_duration` int(11) DEFAULT NULL,
  `land_duration` int(11) DEFAULT NULL,
  `landing_position` varchar(255) DEFAULT NULL,
  `remarks` longtext,
  `validity` int(11) DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=264;

LOCK TABLES `exp8` WRITE;
/*!40000 ALTER TABLE `exp8` DISABLE KEYS */;

INSERT INTO `exp8` (`id`, `bee_id`, `date_time`, `choice`, `hover_duration`, `antennate_duration`, `land_duration`, `landing_position`, `remarks`, `validity`)
VALUES
    (1,NULL,'2013-05-14 15:38:31','right',1,0,0,NULL,NULL,1),
    (2,NULL,'2013-05-18 10:27:15','left',1,0,0,NULL,NULL,1),
    (3,'G5','2013-05-18 11:44:44','left',0,0,4,'yellow',NULL,1),
    (4,'G5','2013-06-01 10:00:00','left',0,0,4,'yellow',NULL,1);

出生和死亡时间标签

CREATE TABLE `tags` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `bee_id` varchar(255) DEFAULT NULL,
  `tag_date` date DEFAULT NULL,
  `colony_id` int(11) DEFAULT NULL,
  `events` varchar(255) DEFAULT NULL,
  `worker_age` varchar(255) DEFAULT NULL,
  `tagged_by` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) TYPE=InnoDB AUTO_INCREMENT=406;

LOCK TABLES `tags` WRITE;
/*!40000 ALTER TABLE `tags` DISABLE KEYS */;

INSERT INTO `tags` (`id`, `bee_id`, `tag_date`, `colony_id`, `events`, `worker_age`, `tagged_by`)
VALUES
    (1,'G5','2013-05-08',1,'birth','Adult','ET'),
    (2,'G5','2013-05-20',NULL,'death','Adult','ET'),
    (3,'G5','2013-05-29',1,'birth','Adult','ET');

以及实验室中展示的刺激

CREATE TABLE `stimuli_schedule` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `left_side` varchar(255) DEFAULT NULL,
  `right_side` varchar(255) DEFAULT NULL,
  `start_datetime` datetime DEFAULT NULL,
  `scheduled` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) TYPE=InnoDB AUTO_INCREMENT=50;

LOCK TABLES `stimuli_schedule` WRITE;
/*!40000 ALTER TABLE `stimuli_schedule` DISABLE KEYS */;

INSERT INTO `stimuli_schedule` (`id`, `left_side`, `right_side`, `start_datetime`, `scheduled`)
VALUES
    (1,'LS1','LS2','2013-05-14 12:00:00',NULL),
    (2,'LS2','LS1','2013-05-15 11:44:00',NULL),
    (3,'LS1','LS2','2013-05-30 11:09:00',NULL);

所需的输出是这样的:

bee_id     CHOICE_DATETIME     LEFT_SIDE     RIGHT_SIDE     CHOICE
===================================================================
NULL       2013-05-14 15:38:31     LS1          LS2           right
G5         2013-05-18 10:27:15     LS2          LS1           left
G5         2013-06-01 10:00:00     LS1          LS2           left

感谢 @GordonLinoff 和 @jcsanyi 的慷慨帮助，有两个相关的 MySQL 查询实现了部分解决方案:

该位显示每只蜜蜂的选择，假设蜜蜂的 ID 是唯一的:

select bee_id, count(case when choice="left" then 1 else NULL end) as leftCount, count(case when choice="right" then 1 else NULL end) as rightCount
  from exp8 e
  left join stimuli_schedule ss on ss.start_datetime <= e.date_time
  left join stimuli_schedule ss2 on ss2.start_datetime <= e.date_time
  where (bee_id IS NOT NULL) AND (ss2.left_side IN ('LA1','HS1') AND ss2.right_side IN('HS1','LA1'))
  group by bee_id

该位能够显示蜜蜂的生命长度，并区分重复使用的标签:

select t.bee_id, (case when t.death_date is null then 'Alive' else 'Dead' end) as status, 
        t.tag_date, t.death_date, (case when t.death_date is not null then timediff(t.death_date,t.tag_date) else timediff(NOW(),t.tag_date) end) as age
from (select t.*,
             (select t2.tag_date
              from tags t2
              where t2.bee_id = t.bee_id and
                    t2.events = 'death' and
                    t2.tag_date >= t.tag_date
              limit 1
             ) as death_date
      from tags t
      where t.events = 'birth'
     ) t
group by t.bee_id, t.tag_date;

我无法组合两个查询来生成所需的输出。这是我的尝试:

select t.bee_id, count(case when choice="left" then 1 else NULL end) as leftCount,
       count(case when choice="right" then 1 else NULL end) as rightCount, 
       (case when t.death_date is null then 'Alive' else 'Dead' end) as status, 
       t.tag_date, t.death_date, 
       (case when t.death_date is not null 
             then timediff(t.death_date,t.tag_date) 
             else timediff(NOW(),t.tag_date) end) as "age (hours)"
from exp8 e, (select t.*,
             (select t2.tag_date
              from tags t2
              where t2.bee_id = t.bee_id and
                    t2.events = 'death' and
                    t2.tag_date >= t.tag_date
              limit 1
             ) as death_date
      from tags t
      where t.events = 'birth'
     ) t
left join stimuli_schedule ss on ss.start_datetime <= e.date_time
left join stimuli_schedule ss2 on ss2.start_datetime <= e.date_time
where (e.bee_id IS NOT NULL)
group by t.bee_id, t.tag_date;

出于我无法理解的原因，左侧的 e.date_time 部分导致了“未知列”错误。

任何帮助将不胜感激!

Answer 1

现在的情况是，JOIN 运算符与派生表 t 相关，而不是像您显然想要的那样与 exp8 相关。这就是通过混合两种不同的连接语法得到的结果。我猜你还想将 t 加入到 bee_id 上的 exp8 中。