在mysql中加入两个表? [英] Join two tables in 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 .

这篇关于在mysql中加入两个表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！