sql多表连接查询实例

select
a.ShortName,
c.CustomerCode,
CASE WHEN ISNULL(c.OtherName,'')='' THEN c.CustomerName ELSE c.OtherName END as 'CustomerName',
d.ContactName,
d.EMail,b.StartTime   
from TB_Department a 
left join TB_Application b 
on a.CompanyCode=b.CompanyCode 
left join TB_Customer c
on b.CustomerCode =c.CustomerCode  
left join TB_CustomerContact d
on c.TransCode  =d.TransCode
where d.EMail is not null and d.EMail <> ''
and (datediff(mm,b.StartTime ,GETDATE()) between 0 and 24)
order by b.StartTime desc

备注：

1.DATEDIFF() 函数,计算时间差。

例如：datediff(dd,时间字段,GETDATE())=365 ；

datediff(mm,b.JobStart ,GETDATE()) between 0 and 24；

datediff(yy,c.JobStart ,GETDATE())=1

2.ISNULL() 函数，使用指定的替换值替换 NULL。

例如：ISNULL(userName,’’)=’’，如果传入的userName为空，则输出’’来代替空值，否则输出userName。

此处无论userName=’’或者userName为null都可以查询到