sql - 需要所有匹配的值和不匹配的连接
问题描述
我正在使用连接从所有表中获取所有值,但它显示所有行中的所有匹配列。
我尝试了以下查询:
SELECT ROW_NUMBER() OVER (ORDER BY A.emp_code) AS SNo,
cast(datepart(dd,A.att_date) as int) as Attdate
A.att_date,b.emp_code as EmpNo,A.in_time,A.out_time,dbo.late_set.remark
FROM dbo.Daily_attendance_data A
LEFT OUTER JOIN dbo.employee B ON (A.emp_id = B.emp_id)
LEFT OUTER JOIN dbo.departments tt ON (B.dept_id = tt.dept_id)
LEFT OUTER JOIN dbo.groups ON (B.group_id=dbo.groups.group_id)
LEFT OUTER JOIN dbo.late_set ON (B.emp_id=dbo.late_set.emp_id)
LEFT OUTER JOIN
dbo.companies ON B.company_id= dbo.companies.company_id
where att_date between '2019-01-02'
and '2019-01-05' and( a.emp_id=2 or a.emp_id=46 or a.emp_id=28)
但我得到如下输出:
1 2019-01-03 1002 NULL NULL Due to Bus BreakDown
2 2019-01-02 1002 2019-01-02 07:06:49.000 NULL Due to Bus BreakDown
3 2019-01-04 1002 NULL NULL Due to Bus BreakDown
4 2019-01-05 1002 2019-01-05 07:00:05.000 NULL Due to Bus BreakDown
5 2019-01-05 1018 2019-01-05 07:05:45.000 NULL Sick
6 2019-01-04 1018 NULL NULL Sick
7 2019-01-03 1018 NULL NULL Sick
8 2019-01-02 1018 2019-01-02 07:04:23.000 NULL Sick
9 2019-01-03 1024 NULL NULL Casual
10 2019-01-04 1024 NULL NULL Casual
11 2019-01-05 1024 2019-01-05 07:02:35.000 NULL Casual
12 2019-01-02 1024 2019-01-02 07:05:02.000 NULL Casual
迟到表
leave_id emp_id start_date remark
4 2 2019-01-02 Due to Bus BreakDown
5 46 2019-01-05 Sick
6 28 2019-01-02 Casual
Daily_attendance_data 表:
2019-01-02 28 1024 1024 RAVEENDRAN K 2019-01-02 07:05:02.000
2019-01-02 46 1018 1018 ZAIDALAVI KODIYIL 2019-01-02 07:04:23.000
2019-01-03 28 1024 1024 RAVEENDRAN K NULL
2019-01-04 28 1024 1024 RAVEENDRAN K NULL
2019-01-05 28 1024 1024 RAVEENDRAN K 2019-01-05 07:02:35.000
2019-01-03 2 1002 1002 AJAYAN NADESAN NULL
2019-01-02 2 1002 1002 AJAYAN NADESAN 2019-01-02 07:06:49.000
2019-01-03 46 1018 1018 ZAIDALAVI KODIYIL NULL
2019-01-04 2 1002 1002 AJAYAN NADESAN NULL
2019-01-04 46 1018 1018 ZAIDALAVI KODIYIL NULL
2019-01-05 2 1002 1002 AJAYAN NADESAN 2019-01-05 07:00:05.000
2019-01-05 46 1018 1018 ZAIDALAVI KODIYIL 2019-01-05 07:05:45.000
我的预期输出为
1 2019-01-03 1002 NULL NULL -
2 2019-01-02 1002 2019-01-02 07:06:49.000 NULL Due to Bus BreakDown
3 2019-01-04 1002 NULL NULL -
4 2019-01-05 1002 2019-01-05 07:00:05.000 NULL -
5 2019-01-05 1018 2019-01-05 07:05:45.000 NULL Sick
6 2019-01-04 1018 NULL NULL -
7 2019-01-03 1018 NULL NULL -
8 2019-01-02 1018 2019-01-02 07:04:23.000 NULL -
9 2019-01-03 1024 NULL NULL -
10 2019-01-04 1024 NULL NULL -
11 2019-01-05 1024 2019-01-05 07:02:35.000 NULL -
12 2019-01-02 1024 2019-01-02 07:05:02.000 NULL Casual
我试过了,但它在所有列中给出了相同的值。
解决方案
推荐阅读
- angular7 - ng serve 命令无法在 Angular 7 中编译
- python - 如何在 python 中将 if else 与 try except 结合起来
- dart - 如何在颤振测试覆盖中排除文件?
- sql - 如何在配置单元脚本中将子查询结果设置为变量
- spring-boot - Springboot别名'Int'已经映射到值'java.lang.Integer'
- google-apps-script - 如何将 WebStorm 用于 Google 应用脚本模板化 HTML?
- docker - 将卷挂载到 Docker 容器中并查看主机系统上的更改
- python - 使用类函数数组初始化类变量
- r - scanBam(bam_fn) 中的错误:找不到函数“scanBam”
- excel - 搜索范围后如何放入变量