sql-server - SQL Server 2012 查询日期

Question

如何在 where 子句中为这样的内容返回值:

获取table1.de1,table2.de2,table3.de3,table4.de3中存在的所有记录

select * 
from table1
inner join table2
on table2.carID = table1.carID
inner join table3
on table3.carID = table1.carID
inner join table4
on table4.driverID = table1.driverID

table2 中存在一个 recietrecord 并且它的 paydate 已经过去 20 天了，将它与 TODAYS 日期进行比较并在名为 从假设司机付款之日起经过的天数

Answer 1

首先，请使用table aliases .此解决方案适用于 SQL Server:

如评论中所写，您可以使用 DATEDIFF将 paydate 与 GETDATE 进行比较的函数.

select  *,
        DATEDIFF(day,t2.paydate,GETDATE()) as [Days Passed From The Day Driver Was Suppose To Pay]
from table1 t1
inner join table2 t2
    on t2.carID = t1.carID
inner join table3 t3
    on t3.carID = t1.carID
inner join table4 t4
    on t4.driverID = t1.driverID
WHERE DATEDIFF(day,t2.paydate,GETDATE()) > 20

或者更好地使用分钟:

DATEDIFF(minute,t2.paydate,GETDATE()) > 28800 --60 minutes * 24 hours * 20 days