mysql 2个表交叉,在mysql中加入两个表?

I have two tables

studentdemo:

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

Using the query

SELECT *

FROM `studentdemo`

RIGHT JOIN spdemo

ON spdemo.date = studentdemo.date

WHERE spdemo.date BETWEEN "2013-12-28"

AND "2013-12-30"

results in null values for the date 2013-12-29:

NULL NULL NULL 2013-12-29 WD

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

解决方案

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