我需要使用一个数据可视化工具,该工具只能查询给定图表的单个来源。 我有三个表,其中包含我需要可视化的数据。 因此,我需要将它们合并到单个视图或输出表中。以下是表架构:
MySQL [bdCaloriesNeeded]> desc activity;
+---------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+----------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | text | YES | | NULL | |
| Gender | text | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| length | text | YES | | NULL | |
| weight | int(11) | YES | | NULL | |
| exercise | int(11) | YES | | NULL | |
| food_consumed | int(11) | YES | | NULL | |
| date | datetime | YES | | NULL | |
+---------------+----------+------+-----+---------+-------+
MySQL [bdCaloriesNeeded]> desc exercise;
+---------------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+---------+------+-----+---------+-------+
| Gender | text | YES | | NULL | |
| Min_Age | int(11) | YES | | NULL | |
| Max_Age | int(11) | YES | | NULL | |
| min_exercise_hours | int(11) | YES | | NULL | |
| med_exercise_hours | int(11) | YES | | NULL | |
| high_exercise_hours | int(11) | YES | | NULL | |
+---------------------+---------+------+-----+---------+-------+
MySQL [bdCaloriesNeeded]> desc food;
+---------------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+---------+------+-----+---------+-------+
| size | text | YES | | NULL | |
| min_pounds | int(11) | YES | | NULL | |
| max_pounds | int(11) | YES | | NULL | |
| min_food_oz_per_day | int(11) | YES | | NULL | |
| max_food_oz_per_day | int(11) | YES | | NULL | |
+---------------------+---------+------+-----+---------+-------+
以下是上表中的实际源数据:
MySQL [bdCaloriesNeeded]> select * from activity;
+------+----------+--------+------+--------+--------+----------+---------------+---------------------+
| id | name | Gender | age | length | weight | exercise | food_consumed | date |
+------+----------+--------+------+--------+--------+----------+---------------+---------------------+
| 14 | spot | M | 2 | 2'7" | 13 | 5 | 13 | 2017-10-08 00:00:00 |
| 67 | princess | F | 6 | 3'3" | 75 | 3 | 15 | 2017-09-05 00:00:00 |
+------+----------+--------+------+--------+--------+----------+---------------+---------------------+
MySQL [bdCaloriesNeeded]> select * from exercise
+--------+---------+---------+--------------------+--------------------+---------------------+
| Gender | Min_Age | Max_Age | min_exercise_hours | med_exercise_hours | high_exercise_hours |
+--------+---------+---------+--------------------+--------------------+---------------------+
| M | 1 | 2 | 1 | 4 | 6 |
| M | 3 | 7 | 1 | 3 | 4 |
| M | 8 | 15 | 1 | 2 | 2 |
| F | 1 | 2 | 1 | 4 | 6 |
| F | 3 | 7 | 1 | 3 | 5 |
| F | 8 | 15 | 1 | 2 | 2 |
+--------+---------+---------+--------------------+--------------------+---------------------+
MySQL [bdCaloriesNeeded]> select * from food;
+--------+------------+------------+---------------------+---------------------+
| size | min_pounds | max_pounds | min_food_oz_per_day | max_food_oz_per_day |
+--------+------------+------------+---------------------+---------------------+
| small | 1 | 10 | 12 | 18 |
| medium | 11 | 30 | 15 | 30 |
| large | 31 | 100 | 25 | 50 |
+--------+------------+------------+---------------------+---------------------+
这是我正在执行的 SQL:
SELECT activity.id, activity.name, activity.Gender, activity.age, activity.weight, activity.exercise, activity.date, exercise.min_exercise_hours, exercise.high_exercise_hours, food.size, food.min_food_oz_per_day, food.max_food_oz_per_day
from activity, exercise, food
where (
activity.exercise between exercise.min_exercise_hours and exercise.high_exercise_hours
)
and
(
activity.weight between food.min_pounds and food.max_pounds
)
and
(
activity.Gender = exercise.Gender
)
这是我得到的意外结果:
+------+----------+--------+------+--------+----------+---------------------+--------------------+---------------------+--------+---------------------+---------------------+
| id | name | Gender | age | weight | exercise | date | min_exercise_hours | high_exercise_hours | size | min_food_oz_per_day | max_food_oz_per_day |
+------+----------+--------+------+--------+----------+---------------------+--------------------+---------------------+--------+---------------------+---------------------+
| 14 | spot | M | 2 | 13 | 5 | 2017-10-08 00:00:00 | 1 | 6 | medium | 15 | 30 |
| 67 | princess | F | 6 | 75 | 3 | 2017-09-05 00:00:00 | 1 | 6 | large | 25 | 50 |
| 67 | princess | F | 6 | 75 | 3 | 2017-09-05 00:00:00 | 1 | 5 | large | 25 | 50 |
+------+----------+--------+------+--------+----------+---------------------+--------------------+---------------------+--------+---------------------+---------------------+
我给公主弄两排。 我需要每只狗一排。 期望的结果应该使用公主的体重来查找每天正确的食物范围,并使用她的性别和年龄来查找正确的运动范围。
我已经敲打了几个小时,看不出这里做错了什么。
有趣的是,您的问题说表是不相关的,但它们实际上是相关的,这就是关系数据库的重点,基于这些关系连接数据。
问题是您的锻炼表仅在锻炼时间使用"之间"连接,因此公主匹配锻炼表中的第 4 行和第 5 行。(第一个 where 子句也与第 1 行和第 2 行匹配,但后一个 where 子句限制性别)
在我看来,您还应该将锻炼桌上的比赛限制为年龄以及exercise
和gender
所以添加
and (activity.age between exercise.min_age and exercise.max_age)
就我个人而言,我也喜欢使用 JOIN 子句而不是 WHERE - 它将所有东西放在一起。
SELECT activity.id,
activity.name,
activity.Gender,
activity.age,
activity.weight,
activity.exercise,
activity.date,
exercise.min_exercise_hours,
exercise.high_exercise_hours,
food.size,
food.min_food_oz_per_day,
food.max_food_oz_per_day
FROM activity
JOIN exercise
ON activity.exercise BETWEEN exercise.min_exercise_hours AND exercise.high_exercise_hours
AND activity.Gender = exercise.Gender
AND activity.age BETWEEN exercise.min_age AND exercise.max_age
JOIN food
ON activity.weight BETWEEN food.min_pounds AND food.max_pounds
由于您正在寻找可能超出建议范围的东西,您可能需要考虑锻炼和食物表上的 LEFT JOIN,以便活动表上超出任何范围的狗仍然会显示(另一个表的缺失数据为 NULL 值。 只需将连接行更改为左连接,如下所示:
LEFT JOIN exercise
LEFT JOIN food
另请参阅:"内部连接"和"外部连接"有什么区别?