可能是一个重复的问题。。。这里有两个表tasks_0_tasks_staff和tasks_0_stafff_time_management:
tasks_0_tasks_staff+----+---------+---------+|id |任务id |用户id|+----+---------+---------+|1|1 | 348||2|1|350||3|2|350||4|2|351||5 | 2 | 357|+----+---------+---------+
和
tasks_0_staff_time_management+----+---------+---------+-------------+-------+|id |用户id |任务id |星期几|小时|+----+---------+---------+-------------+-------+|1|350|1|2|2||2|350|1|3|3|+----+---------+---------+-------------+-------+
当我执行下面的查询时,它在左联接表中显示了重复的数据。。。如何显示null(实际情况是这样的)。。。
选择tasks_0_tasks_staff`task_id `,tasks_0_stafff_time_management.day_of_week,tasks_0_staff_time_management.hoursFROM tasks_0_tasks_staffLEFT加入`tasks_0_staff_time_management`ON`tasks_0-staff_time.management``user_id `=`tasks_0_tasks_staff``user_id`何处`tasks_0_tasks_staff``user_id`=350按`tasks_0_tasks_staff`订购`task_id`;结果:+---------+-------------+-------+|task_id |星期几|小时|+---------+-------------+-------+|1|2|2||1|3|3||2|2|2||2|3|3|+---------+-------------+-------+
为什么结果中的最后两行显示day_of_week和hours列值,因为它们在左联接表中不存在?这两行如何显示NULL值?
我预计结果会像这个
+---------+-------------+-------+|task_id |星期几|小时|+---------+-------------+-------+|1|2|2||1|3|3||2|NULL |NULL||2|NULL |NULL|+---------+-------------+-------+
你必须像这个一样更改你的代码
SELECT tasks_0_tasks_staff.`task_id`,
tasks_0_staff_time_management.day_of_week,
tasks_0_staff_time_management.hours
FROM tasks_0_tasks_staff
LEFT JOIN `tasks_0_staff_time_management` ON `tasks_0_staff_time_management`.`user_id`= `tasks_0_tasks_staff`.`user_id`
WHERE `tasks_0_tasks_staff`.`user_id`= 350 AND `tasks_0_tasks_staff`.`task_id` = tasks_0_staff_time_management.`task_id`
ORDER BY `tasks_0_tasks_staff`.`task_id` ;
您应该添加task_id
作为加入的条件:
SELECT tasks_0_tasks_staff.`task_id`,
tasks_0_staff_time_management.day_of_week,
tasks_0_staff_time_management.hours
FROM tasks_0_tasks_staff
LEFT JOIN `tasks_0_staff_time_management` ON `tasks_0_staff_time_management`.`user_id`= `tasks_0_tasks_staff`.`user_id` AND `tasks_0_staff_time_management`.`task_id`= `tasks_0_tasks_staff`.`task_id`
WHERE `tasks_0_tasks_staff`.`user_id`= 350
ORDER BY `tasks_0_tasks_staff`.`task_id` ;
或者使用一些更语义化的SQL:
SELECT tasks_0_tasks_staff.`task_id`,
tasks_0_staff_time_management.day_of_week,
tasks_0_staff_time_management.hours
FROM tasks_0_tasks_staff
LEFT JOIN `tasks_0_staff_time_management` USING (`task_id`, `user_id`)
WHERE `tasks_0_tasks_staff`.`user_id`= 350
ORDER BY `tasks_0_tasks_staff`.`task_id` ;
这里有一个概念验证SQL fiddle:
http://sqlfiddle.com/#!9/2a2425/1
您需要在where中添加一个条件,以便它只匹配具有相同task_id的记录。
请注意,重复的结果意味着不同的相关行是相同的,这是而不是您的情况。
您应该使用"INNER JOIN"。
SELECT tasks_0_tasks_staff.`task_id`,
tasks_0_staff_time_management.day_of_week,
tasks_0_staff_time_management.hours
FROM tasks_0_tasks_staff
INNER JOIN `tasks_0_staff_time_management` ON `tasks_0_staff_time_management`.`user_id`= `tasks_0_tasks_staff`.`user_id`
WHERE `tasks_0_tasks_staff`.`user_id`= 350
ORDER BY `tasks_0_tasks_staff`.`task_id` ;