一个复杂的2个表的连接查询

Question

部门表dept(id,name),数据如下:
id   name
1   单位1
2   单位2
3   单位3入库记录表inRecord,有4个字段:id,deptid(部门id,外键),goodsnum(商品数量),date_(时间).
数据库中的原始记录如下:
id   deptid   goodsnum     date_
1     1      130      2006-6-1
2     2      160      2006-6-1
3     2      200      2006-6-1
4     3      135      2006-6-15     1      96       2006-6-2
6     2      113      2006-6-3我要按日期分类,同时如果该日期内某部门无记录,则给它添加一条当天的空记录,结果如下:
id   deptid   goodsnum     date_
1     1      130      2006-6-1
2     2      160      2006-6-1
3     2      200      2006-6-1
4     3      135      2006-6-15     1      96       2006-6-2
null  2      null     2006-6-2
null  3      null     2006-6-2null  1      null     2006-6-3
6     2      113      2006-6-3
null  3      null     2006-6-3这个sql怎么写呢?

Answer 1

比较笨的方法
你在数据库里做一个永久表tempTable(ID int)
在里面插入N条记录
ID
1
2
3
4
……
以上是查询以外的事情。生成表的目的主要是补齐中间有缺的SQL> CREATE TABLE DEP(ID NUMBER,NAME VARCHAR2(30));
SQL> INSERT INTO DEP SELECT 1,'DW1' FROM DUAL
  2  UNION ALL SELECT 2,'DW2' FROM DUAL
  3  UNION ALL SELECT 3,'DW3' FROM DUAL;
SQL> CREATE TABLE IRECORD(
  2  ID NUMBER,
  3  DEPTID NUMBER,
  4  GOODSUM NUMBER,
  5  DATE_ DATE);Table createdSQL> INSERT INTO IRECORD SELECT 1,1,130,TO_DATE('2006-6-1','YYYY-M-DD') FROM DUAL;
SQL> INSERT INTO IRECORD SELECT 3,2,200,TO_DATE('2006-6-1','YYYY-MM-DD') FROM DUAL;
SQL> INSERT INTO IRECORD SELECT 2,2,160,TO_DATE('2006-6-1','YYYY-MM-DD') FROM DUAL;
。
SQL> INSERT INTO IRECORD SELECT 6,2,115,TO_DATE('2006-6-3','YYYY-MM-DD') FROM DUAL;
SQL> SELECT * FROM IRECORD;        ID     DEPTID    GOODSUM DATE_
---------- ---------- ---------- -----------
         1          1        130 2006-6-1
         2          2        160 2006-6-1
         3          2        200 2006-6-1
         4          3        135 2006-6-1
         5          1        135 2006-6-2
         6          2        115 2006-6-3
SQL> SELECT * FROM DEP;        ID NAME
---------- ------------------------------
         1 DW1
         2 DW2
         3 DW3SQL> select N.id,M.id,n.goodsum,M.dt as date_
  2  From (
  3         Select distinct b.id,a.date_ as DT
  4         from irecord A,tempTable B Where b.id<=(select count(1) from dep)) M
  5  Left JOIN iRecord N
  6  ON M.id=N.deptid   And M.dt=N.date_
  7  order by M.dt,M.id
  8  /        ID         ID    GOODSUM DATE_
---------- ---------- ---------- -----------
         1          1        130 2006-6-1
         2          2        160 2006-6-1
         3          2        200 2006-6-1
         4          3        135 2006-6-1
         5          1        135 2006-6-2
                    2            2006-6-2
                    3            2006-6-2
                    1            2006-6-3
         6          2        115 2006-6-3
                    3            2006-6-310 rows selected

Answer 2

各门，不用创建永久表temptable,试试这样看看
SQL> select N.id,M.id,n.goodsum,M.dt as date_
  2  From (
  3        select dep.id,b.date_ dt from dep,(select distinct date_ from irecord) b order by id,date_) M
  4  Left JOIN iRecord N
  5  ON M.id=N.deptid   And M.dt=N.date_
  6  order by M.dt,M.id
  7  ;        ID         ID    GOODSUM DATE_
---------- ---------- ---------- -----------
         1          1        130 2006-6-1
         2          2        160 2006-6-1
         3          2        200 2006-6-1
         4          3        135 2006-6-1
         5          1        135 2006-6-2
                    2            2006-6-2
                    3            2006-6-2
                    1            2006-6-3
         6          2        115 2006-6-3
                    3            2006-6-310 rows selected

Answer 3

select B.id, A.deptid, B.goodsnum, C.Date
from 
dept A

Answer 4

select B.id, A.id deptid, B.goodsnum, C.Date
from InRecord B
     left outer join dept A on B.Deptid = A.id
     left outer join (select distinct date from InRecord) C on B.Date = C.Date
order by C.Date, A.id;
  

Answer 5

select X.Id, Y.id deptid, X.Goodsnum, Y.inDate
from InRecord X,
    (select A.id, B.Indate
    from dept A, ( select distinct inDate from InRecord ) B) Y
where X.Deptid(+) = Y.Id    
and X.Indate(+) = Y.InDate注：InRecord表的Date改为InDate, 因为Date是Oracle的保留字段

Answer 6

外联就可了，如下：
select i.id,d.id,i.goodsnum,nvl(i.date_,sysdate) date_ from dept d,inRecord i 
where d.id = i.deptid(+)

Answer 7

不好意思少了日期：
select i.id,d.id,i.goodsnum,nvl(i.date_,sysdate) date_ from dept d,inRecord i 
where d.id = i.deptid(+) and i.date_(+)=sysdate用to_date把sysdate转换成date_相应的格式就可以了