在mysql中加入两个表?

本文介绍了在mysql中加入两个表?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有两个桌子

学生演示:

sid | date | status -------------------------------- 10 | 2013-12-28 | 1 11 | 2013-12-28 | 1 12 | 2013-12-28 | 1 13 | 2013-12-28 | 1 10 | 2013-12-30 | 1 11 | 2013-12-30 | 1 12 | 2013-12-30 | 1 13 | 2013-12-30 | 1

spdemo:

date | status ------------------------ 2013-12-28 | cd 2013-12-29 | wd 2013-12-30 | cd

使用查询

SELECT * FROM `studentdemo` RIGHT JOIN spdemo ON spdemo.date = studentdemo.date WHERE spdemo.date BETWEEN "2013-12-28" AND "2013-12-30"

得出日期2013-12-29的空值:

NULL NULL NULL 2013-12-29 WD

是否可以通过sid获得输出?

Is it possible to get an output with sid?

sid | date | status | date | status ------------------------------------------------------- 10 | null | null | 2013-12-29 | wd 11 | null | null | 2013-12-29 | wd 12 | null | null | 2013-12-29 | wd 13 | null | null | 2013-12-29 | wd

推荐答案

您可以交叉联接以获取所有组合，然后将表LEFT JOIN移至该组合；

You could cross join to get all combinations, and LEFT JOIN the tables to that;

SELECT x.sid, table1.date, table1.status, x.date bdate, table2.status bstatus FROM (SELECT DISTINCT table1.sid, table2.date FROM table1 CROSS JOIN table2) x LEFT JOIN table1 ON x.sid=table1.sid AND x.date = table1.date LEFT JOIN table2 ON x.date=table2.date ORDER BY bdate, sid

要测试的SQLfiddle .