mysql - mysql 中连接两个表？

Question

我有两张 table

学生演示:

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 获得输出？

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

Answer 1

您可以交叉连接来获取所有组合，然后将表LEFT JOIN连接到该组合；

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

An SQLfiddle to test with .