我应该使用哪个sql连接查询?

i'm a noob in using complex query.. so i'm a little bit confused around here..

我是使用复杂查询的新手。所以我在这里有点困惑。

here are the problem :

问题是:

i had 2 table, the first one is

我有两个桌子，第一个是

employee :

雇员:

empID name  branchID   etc
 1    ab      1        ...
 2    abc     4        ...
 3    ad      4        ...

and the second table was

第二张桌子是。

employeeAttendance :

employeeAttendance:

empID   attDate     hourIn  hourOut  etc
  1    05-06-2013    12.00   14.00   ...
  1    05-07-2013    10.00   14.00   ...
  1    05-10-2013    09.00   12.00   ...
  2    05-06-2013    08.00   14.00   ...
  2    05-10-2013    08.00   10.00   ...
  3    05-09-2013    11.00   15.00   ...

and what i'm trying to accomplish is this view :

我想要实现的是这个观点

empID name   attDate     hourIn  hourOut  etc
  1    ab  05-06-2013    12.00   14.00   ...
  2    abc 05-06-2013    08.00   14.00   ...
  3    ad  05-06-2013    null    null    ...
  1    ab  05-07-2013    10.00   14.00   ...
  2    abc 05-07-2013    null    null    ...
  3    ad  05-07-2013    null    null    ...
  1    ab  05-09-2013    null    null    ...
  2    abc 05-09-2013    null    null    ...
  3    ad  05-09-2013    11.00   15.00   ...
  1    ab  05-10-2013    09.00   12.00   ...
  2    abc 05-10-2013    08.00   10.00   ...
  3    ad  05-10-2013    null    null    ...

i'm using sql server management studio 2008, it's funny, i felt this is so easy but i couldn't make it afterall, i have tried to use LEFT OUTER JOIN, RIGHT OUTER JOIN, INNER JOIN, and even CROSS JOIN, but none of them give me the result i want..

我使用的是sql server management studio 2008，这很有趣，我觉得这很简单，但是我最终还是做不到，我尝试过使用左外连接、右外连接、内连接、甚至交叉连接，但是没有一个能给我想要的结果。

the one that nearly give me the answer was CROSS JOIN but the ID didn't match because CROSS JOIN didn't use ON clause.. and when i add WHERE, it automatically became INNER JOIN..

几乎给我答案的那个是CROSS JOIN，但是ID不匹配，因为CROSS JOIN没有使用ON子句。当我添加WHERE时，它自动变成了内部连接。

so did i miss something in here? sorry if this question is silly, and sorry for the bad english :)

我是不是漏掉了什么?如果这个问题很愚蠢，我很抱歉。

4 个解决方案

#1

 WITH DateList AS(
 SELECT DISTINCT E.EmpiD,E.Name,EA.AttDate FROM EmployeeAttendance EA
 CROSS JOIN Employee E )

 SELECT
    DL.empID,
    DL.name,
    DL.attDate,
    EA.hourIn,
    EA.hourOut,
    EA.etc
FROM DateList DL
LEFT OUTER JOIN EmployeeAttendance EA
ON DL.EmpID = EA.EmpID AND 
DL.AttDate = EA.AttDate
ORDER BY DL.AttDate,DL.EmpId

SQL Fiddle

SQL小提琴

Raj

拉吉

#2

Here you go:

给你:

SELECT e.empID, name, attDay, hourIn, hourOut
FROM employee e
CROSS JOIN (SELECT distinct attDate AS attDay FROM employeeAttendance) AS allDates
LEFT OUTER JOIN employeeAttendance att
ON e.empID = att.empID and attDay = attDate

Demo on SQLFiddle.

SQLFiddle演示。

#3

Use FULL OUTER JOIN

使用全外连接

SELECT employee.empID, employee.name,   employeeAttendance.attDate,employeeAttendance.hourIn,  employeeAttendance.hourOut,  employeeAttendance.etc 
FROM employee  
FULL OUTER JOIN employeeAttendance on employee.empID= employeeAttendance.empID

#4

Try this :

试试这个:

SQL Fiddle

SQL小提琴

Query:

查询:

select a.empID, a.name,   employeeAttendance.attDate,employeeAttendance.hourIn,
employeeAttendance.hourOut
from employeeAttendance full join 

(select empID, name,  branchID,attDate from emp
, (select distinct attDate from employeeAttendance)b)a 

on employeeAttendance.empID = a.empID  and employeeAttendance.attDate=a.attDate
order by empid,attDate desc

Results:

结果:

| EMPID | NAME |    ATTDATE | HOURIN | HOUROUT |
------------------------------------------------
|     1 |   ab | 05-10-2013 |  09.00 |   12.00 |
|     1 |   ab | 05-06-2013 |  12.00 |   14.00 |
|     1 |   ab |     (null) | (null) |  (null) |
|     2 |  abc | 05-10-2013 |  08.00 |   10.00 |
|     2 |  abc | 05-06-2013 |  08.00 |   14.00 |
|     2 |  abc |     (null) | (null) |  (null) |
|     3 |   ad | 05-09-2013 |  11.00 |   15.00 |
|     3 |   ad |     (null) | (null) |  (null) |
|     3 |   ad |     (null) | (null) |  (null) |