sql - 左外连接 SSMS
问题描述
我试图在两个表之间留下外部连接。
select id, startdate, name, code, email from edw.dbo.starts
id startdate name code email yearfiled
15 2/4/2018 SO 1083 sql@gmail.com 2018
17 3/4/2018 SO 1083 ssms@gmail.com 2018
19 4/4/2018 SO 1083 ssrs@gmail.com 2018
21 5/4/2018 SO 1083 ssas@gmail.com 2018
21 5/5/2017 SO 1083 who@gmail.com 2017
select customer, return_year, revenue, code from sql.dbo.paid
customer return_year revenue code
15 2018 15.00 1083
17 2018 25.00 1083
21 2018 35.00 1083
21 2017 35.00 1083
select
month(os.startdate) as startmonth
,os.name
,os.code
,coalesce(s.revenue, 0) as revenue
,count(os.email) as commission
from
edw.dbo.starts as os
left outer join
sql.dbo.paid as s
on
os.id = s.customer
and os.yearfile = s.return_year
where
os.yearfiled = 2018
and os.code = '1083'
and os.startdate is not null
group by
month(os.startdate)
,os.name
,os.code
,coalesce(s.revenue, 0);
startmonth name code revenue commission
2 SO 1083 15.00 1
3 SO 1083 25.00 1
4 SO 1083 0.00 1
5 SO 1083 0.00 1
问题:
Customer
= 21
from sql.dbo.paid 显示联接查询中的收入为零,即使它在表中报告了 35.00 美元的收入。
要求:
startmonth name code revenue commission
2 SO 1083 15.00 1
3 SO 1083 25.00 1
4 SO 1083 0.00 1
5 SO 1083 35.00 1
解决方案
请试试这个。
create table starts
(id int ,
startdate date ,
name varchar(10),
code int ,
email varchar(20),
yearfiled int )
create table paid
( customer int ,
return_year int ,
revenue decimal(10,2),
code int)
insert into starts values
(15,'2/4/2018','SO',1083,'sql@gmail.com',2018),
(17,'3/4/2018','SO',1083,'ssms@gmail.com',2018),
(19,'4/4/2018','SO',1083,'ssrs@gmail.com',2018),
(21,'5/4/2018','SO',1083,'ssas@gmail.com',2018),
(21,'5/5/2017','SO',1083,'who@gmail.com',2017)
insert into paid values
(15,2018,15.00,1083),
(17,2018,25.00,1083),
(21,2018,35.00,1083),
(21,2017,35.00,1083)
select month(a.startdate)as startmonth,a.name,a.code,
case when b.revenue is null then 0 else b.revenue end as revenue,
count(a.email) as commission
from starts a
left join paid b on a.yearfiled=b.return_year and a.id=b.customer
where a.yearfiled=2018 and a.code=1083
group by month(a.startdate),a.name,a.code,b.revenue
/*
startmonth name code revenue commission
----------- ---------- ----------- --------------------------------------- -----------
2 SO 1083 15.00 1
3 SO 1083 25.00 1
4 SO 1083 0.00 1
5 SO 1083 35.00 1
*/
推荐阅读
- python - 如何使用制表 python 包在终端中显示漂亮的表格?
- wordpress - bncert 说我的域解析为不同的 IP 地址,但事实并非如此
- xamarin - Xamarin - 如何从 AppStore 下载崩溃报告?
- database - 获取数组内容作为数组中多个对象的特征
- rust - 具有互斥的编译器重新排序部分
- gradle - 无法解析 Gradle 脚本插件中的插件(外部 Gradle 文件)
- css - 样式不适用于 Angular 中的子子路由
- javascript - 如何将 Vue .prototype 迁移到单独的文件
- javascript - Codemirror 不会对来自 textarea 的代码进行样式设置
- kotlin - 在带有 ignoreCase 的字符串数组上使用 contains