我正在遇到以下问题,我想提出一个查询,以返回我的表格计划信息。
+---------+---------+------------+----------+---------------------+------------------+--------------+----------+------------+---------------+-----------+-------------+---------+
| logs_id | plan_id | workout_id | plan_day | date_made | workout_names_id | name | language | workout_id | plan_names_id | name | language_id | plan_id |
+---------+---------+------------+----------+---------------------+------------------+--------------+----------+------------+---------------+-----------+-------------+---------+
| 2 | 1 | 1 | 1 | 2017-04-30 11:40:48 | 2 | Workout GR 1 | 2 | 1 | 2 | Plan 1 GR | 2 | 1 |
+---------+---------+------------+----------+---------------------+------------------+--------------+----------+------------+---------------+-----------+-------------+---------+
1 row in set (0.00 sec)
我的问题是,当plan_id没有价值时,我什么也没得到,即使plan_id是0,我还能得到一个查询,即使plan_id是0。这是我到目前为止所做的代码:
DELIMITER $$
CREATE PROCEDURE get_logs(IN _language SMALLINT(255))
BEGIN
SELECT * FROM logs AS lo INNER JOIN workout_names AS wn ON lo.workout_id = wn.workout_id INNER JOIN plan_names AS pn ON lo.plan_id = pn.plan_id WHERE wn.language = _language AND pn.language_id = _language ORDER BY lo.logs_id;
END$$
当plan_id中没有值时,它就不返回我任何东西。
更新!
以下代码适用于部分问题:
DELIMITER $$
CREATE PROCEDURE get_logs(IN _language SMALLINT(255))
BEGIN
SELECT * FROM logs AS lo INNER JOIN workout_names AS wn ON lo.workout_id = wn.workout_id LEFT JOIN plan_names AS pn ON lo.plan_id = pn.plan_id WHERE wn.language = _language AND pn.language_id = _language ORDER BY lo.logs_id;
END$$
如果pn.language_id中的值不是null。
+---------+---------+------------+----------+---------------------+------------------+--------------+----------+------------+---------------+-----------+-------------+---------+
| logs_id | plan_id | workout_id | plan_day | date_made | workout_names_id | name | language | workout_id | plan_names_id | name | language_id | plan_id |
+---------+---------+------------+----------+---------------------+------------------+--------------+----------+------------+---------------+-----------+-------------+---------+
| 3 | 1 | 1 | 1 | 2017-04-30 12:20:47 | 1 | Workout EN 1 | 1 | 1 | 1 | Plan 1 EN | 1 | 1 |
| 3 | 1 | 1 | 1 | 2017-04-30 12:20:47 | 1 | Workout EN 1 | 1 | 1 | 2 | Plan 1 GR | 2 | 1 |
+---------+---------+------------+----------+---------------------+------------------+--------------+----------+------------+---------------+-----------+-------------+---------+
2 rows in set (0.00 sec)
基本上是我所有的语言,我只能解析一种语言,它不会返回我的null。
+---------+---------+------------+----------+---------------------+------------------+--------------+----------+------------+---------------+------+-------------+---------+
| logs_id | plan_id | workout_id | plan_day | date_made | workout_names_id | name | language | workout_id | plan_names_id | name | language_id | plan_id |
+---------+---------+------------+----------+---------------------+------------------+--------------+----------+------------+---------------+------+-------------+---------+
| 1 | 0 | 1 | 0 | 2017-04-30 10:52:24 | 1 | Workout EN 1 | 1 | 1 | NULL | NULL | NULL | NULL |
+---------+---------+------------+----------+---------------------+------------------+--------------+----------+------------+---------------+------+-------------+---------+
1 row in set (0.00 sec)
如何将它们结合起来像一个?
这就是它!
DELIMITER $$
CREATE PROCEDURE get_logs(IN _language SMALLINT(255))
BEGIN
SELECT * FROM logs AS lo INNER JOIN workout_names AS wn ON lo.workout_id = wn.workout_id LEFT JOIN plan_names AS pn ON lo.plan_id = pn.plan_id AND pn.language_id = _language WHERE wn.language = _language ORDER BY lo.logs_id;
END$$