mysql数据库多表查询

Question

数据库表设计如图所示想查询的情况是：客户端会提交一个ibeacon表中的address（这个值在表中是唯一的）和个人用户表中的id，我要根据ibeacon中的address首先找到其ibeacon_id，然后根据ibeacon_id在ibeacon_in_group表中找到一个或若干个group_id，然后根据跟group_id在营销信息表中找到对应的营销信息。然后麻烦来了，由于找出的营销信息是有一定的推送规则的，也就是这条营销信息应该符合该用户的身份，具体规则是性别，年龄，社会层级和最大推送数都要合适。
对了，营销信息推送规则还有一些内在规则
向哪种性别的人推送信息，0表示任何人，1表示男，2表示女
推送信息的人所处社会阶层，0表示任何阶层，...
第一次提问弄错地方了，放到数据库报表里了，现在没多少积分了，你要是去数据库报表板块回答的话，还是有50分的

Answer 1

简单点说可以分为三步
1.根据传入的address找到与之对应的营销信息和该条消息的营销规则
2.根据传入的userID找到适合该用户的营销规则
3.根据营销规则剔除不合适的营销信息

Answer 2

我本来以为画ER图更有助于理解，那把创建表的sql语句贴出来吧，就是比较多看起来更麻烦
ibeacon表：
CREATE TABLE `ibeacon` (
  `ibeacon_id` int(32) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ibeacon唯一标识符',
  `address` char(20) NOT NULL COMMENT 'IBeacon设备物理地址，是唯一值',
  `loaction` char(255) NOT NULL DEFAULT '北环一站' COMMENT '应该和其所在组地址一致',
  `status` tinyint(3) unsigned NOT NULL DEFAULT '1' COMMENT '0表示不能工作，1表示正常',
  `add_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '添加时间',
  PRIMARY KEY (`ibeacon_id`),
  UNIQUE KEY `address` (`address`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
ibeacon_group表：
CREATE TABLE `ibeacon_group` (
  `group_id` int(32) unsigned NOT NULL AUTO_INCREMENT,
  `group_name` char(99) NOT NULL DEFAULT '默认分组',
  `location` char(99) NOT NULL DEFAULT '北环一站',
  PRIMARY KEY (`group_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
ibeacon_history_info表：
CREATE TABLE `ibeacon_history_info` (
  `statics_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ibeacon_id` int(10) unsigned DEFAULT NULL,
  `user_id` int(10) unsigned DEFAULT NULL,
  `eting_info_id` int(10) unsigned DEFAULT NULL,
  `push_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '推送时间，自动填充当前时间',
  PRIMARY KEY (`statics_id`),
  KEY `ibeacon_id` (`ibeacon_id`) USING BTREE,
  KEY `user_id` (`user_id`) USING BTREE,
  KEY `eting_info_id` (`eting_info_id`) USING BTREE,
  CONSTRAINT `ibeacon_history_info_ibfk_1` FOREIGN KEY (`ibeacon_id`) REFERENCES `ibeacon` (`ibeacon_id`),
  CONSTRAINT `ibeacon_history_info_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `ibeacon_user` (`user_id`),
  CONSTRAINT `ibeacon_history_info_ibfk_3` FOREIGN KEY (`eting_info_id`) REFERENCES `ibeacon_eting_info` (`eting_info_id`)
) ENGINE=InnoDB AUTO_INCREMENT=164 DEFAULT CHARSET=utf8;
ibeacon_in_group表：
CREATE TABLE `ibeacon_in_group` (
  `ibeacon_in_group_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ibeacon_id` int(10) unsigned DEFAULT NULL,
  `group_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`ibeacon_in_group_id`),
  KEY `ibeacon_id` (`ibeacon_id`) USING BTREE,
  KEY `group_id` (`group_id`) USING BTREE,
  CONSTRAINT `ibeacon_in_group_ibfk_1` FOREIGN KEY (`ibeacon_id`) REFERENCES `ibeacon` (`ibeacon_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `ibeacon_in_group_ibfk_2` FOREIGN KEY (`group_id`) REFERENCES `ibeacon_group` (`group_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
ibeacon_eting_info表：
CREATE TABLE `ibeacon_eting_info` (
  `eting_info_id` int(32) unsigned NOT NULL AUTO_INCREMENT COMMENT '每条营销信息的唯一标识',
  `group_id` int(32) unsigned NOT NULL,
  `title` varchar(255) NOT NULL DEFAULT '这是一个营销信息抬头' COMMENT '营销信息简介',
  `icon_url` varchar(255) NOT NULL DEFAULT 'http://pic.qiantucdn.com/58pic/15/30/52/29s58PIC2gf_1024.jpg' COMMENT '营销信息缩略图URL',
  `summary` varchar(255) NOT NULL DEFAULT '这是一个营销信息简介' COMMENT '简要描述该营销信息',
  `detail_url` varchar(255) NOT NULL DEFAULT 'https://m.baidu.com/?from=1012852s',
  `add_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加该营销信息的时间',
  `valid_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '过期时间',
  `add_operator` char(20) NOT NULL DEFAULT '管理员' COMMENT '添加该营销信息的人',
  PRIMARY KEY (`eting_info_id`),
  KEY `group_id` (`group_id`) USING BTREE,
  CONSTRAINT `ibeacon_eting_info_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `ibeacon_group` (`group_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
ibeacon_push_count表：
CREATE TABLE `ibeacon_push_count` (
  `eting_info_id` int(32) unsigned NOT NULL,
  `count` int(32) DEFAULT '0',
  PRIMARY KEY (`eting_info_id`),
  CONSTRAINT `ibeacon_push_count_ibfk_1` FOREIGN KEY (`eting_info_id`) REFERENCES `ibeacon_eting_info` (`eting_info_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ibeacon_push_rule表：
CREATE TABLE `ibeacon_push_rule` (
  `push_id` int(32) unsigned NOT NULL AUTO_INCREMENT COMMENT '消息推送规则',
  `eting_info_id` int(32) unsigned NOT NULL,
  `max_count` int(11) NOT NULL DEFAULT '0' COMMENT '该条消息的最大推送数目，0表示无限大',
  `start_time` int(11) unsigned DEFAULT '0' COMMENT '一天内消息推送的开始时间，空表示任何时间都可以',
  `end_time` int(11) unsigned DEFAULT '86400' COMMENT '一天内消息推送的截止时间，空表示任何时间都可以',
  `sex` tinyint(255) DEFAULT '0' COMMENT '向哪种性别的人推送信息，0表示任何人，1表示男，2表示女',
  `start_age` tinyint(255) unsigned DEFAULT '0' COMMENT '推送信息最小年龄',
  `end_age` tinyint(3) unsigned DEFAULT '100' COMMENT '推送信息最大年龄',
  `hierarchy` tinyint(3) unsigned DEFAULT '0' COMMENT '推送信息的人所处社会阶层，0表示任何阶层，...',
  `add_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '添加该消息时的时间',
  PRIMARY KEY (`push_id`),
  KEY `eting_info_id` (`eting_info_id`) USING BTREE,
  CONSTRAINT `ibeacon_push_rule_ibfk_1` FOREIGN KEY (`eting_info_id`) REFERENCES `ibeacon_eting_info` (`eting_info_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
ibeacon_user表：
CREATE TABLE `ibeacon_user` (
  `user_id` int(32) unsigned NOT NULL AUTO_INCREMENT,
  `age` tinyint(3) unsigned DEFAULT '0' COMMENT '用户年龄，0表示不清楚',
  `sex` tinyint(4) DEFAULT '0' COMMENT '0,表示不清楚，1表示男，2表示女',
  `hierarchy` tinyint(4) DEFAULT '0' COMMENT '0表示不清楚',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=102 DEFAULT CHARSET=utf8;

Answer 3

需求
输入：一个ibeacon地址（address）和用户id
输出：返回给用户的营销信息
我想大致可以分为这三步来做
1.根据传入的address找到与之对应的营销信息和该条消息的营销规则
2.根据传入的userID找到适合该用户的营销规则
3.根据营销规则剔除不合适的营销信息

Answer 4

问题还是在于问题表述不够清晰，或许需求本身就不够清楚吧。你用了太多的业务词汇，但是业务词汇与表的注释也匹配不上，所以作为需求规格说明书是没法用的。
我给你整理了一下：需求
输入：一个ibeacon地址（ibeacon.address）和用户id(ibeacon.userID)
输出：返回给用户的营销信息(ibeacon_eting_info)
我想大致可以分为这三步来做
1.根据传入的address找到与之对应的营销信息(ibeacon_eting_info)和该条消息的营销规则(ibeacon_push_rule)
2.根据传入的userID找到适合该用户的营销规则(ibeacon_push_rule)
3.根据营销规则剔除不合适的营销信息 <- 这里表述极不清楚，什么是合适，什么是不合适如果去掉表述不清晰的3，那么SQL大概是这个样子
select m.*
from ibeacon_eting_info m 
join ibeacon_in_group g on (m.groupID = g.groupID)
join ibeacon.address b on (b.location = g.location)
join ibeacon_push_rule r on (r.etingInfoID = m.etingInfoID)
where b.address = @address我想，你估计是想用"营销历史记录"作为条件，过滤ibeacon_eting_info吧。
这样才能用到 hist.userID = @userID 之类的条件。那"营销历史记录"关联的4张表定义在哪里？又是与其他的表如何关联的？另外，如果有这"营销历史记录"4张表，那么这个库的设计上有些问题。历史记录与master表间缺ID和条件关联。

Answer 5

我知道了主要是我的第2条没有说清楚，直接导致了第3条的误解。
第2条的意思是直接从ibeacon_user表中选出该用户的年龄、性别、社会层级，而一条营销信息的推送规则包括了面向用户的年龄段（start_age、end_age），性别，和社会层级，这三个都符合该用户时才可能向用户推送该消息。
营销历史记录和最大推送数确实也是推送条件，这两个表都是动态变化的，当向一个用户推送一条消息时，就把该消息ID（eting_info_id），该用户的ID（user_id），以及ibeacon的ID（ibeacon_id）存到营销历史记录中，把该条营销信息的推送数量加1,。通过营销历史记录里的user_id和eting_info_id我们可以确定是否向该用户推送过该消息，若推送过就不要再推送了，通过当前推送数表我们可以确定该条消息的推送数量是否已经达到了最大限制，如果达到了也不再推送。
上述合起来才是完整的推送规则。
忽然发现我好像没话最大推送数表，这个表示这样的，只有两个字段，一个是该条营销信息的ID，一个是该条营销信息的当前推送数
CREATE TABLE `ibeacon_push_count` (
  `eting_info_id` int(32) unsigned NOT NULL,
  `count` int(32) DEFAULT '0',
  PRIMARY KEY (`eting_info_id`),
  CONSTRAINT `ibeacon_push_count_ibfk_1` FOREIGN KEY (`eting_info_id`) REFERENCES `ibeacon_eting_info` (`eting_info_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;我只会用一些简单的数据库查询语句，所以现在采用的是先用传入的address找到营销信息。然后再编程一点一点的过滤上述推送规则，但我知道一定可以使用一条查询语句就把这些事全部搞定的。查询与address对应的营销信息的语句是这样写的
SELECT
b.ibeacon_id,
c.group_id,
title,
icon_url,
summary,
detail_url,
a.add_date,
valid_date
FROM
ibeacon_eting_info a,
ibeacon b,
ibeacon_in_group c
WHERE
b.address = '00:CD:FF:0E:36:1C'
AND b.ibeacon_id = c.ibeacon_id
AND c.group_id = a.group_id;