sql按时间取去重最近_sql server 窗口函数去重和MOSS数据库获取

select *

from(

SELECT ROW_NUMBER() over (partition by a.cameraip,a.carPlate order by a.cameraip,a.intime) num,*

FROM [Camera].[dbo].[truck_Gate] a with(nolock)

where intime>‘2019-02-27‘

)a

where a.num = 1

order by a.cameraip,a.intime

按摄像头类型，时间来去掉重复

//MOSS数据获取考勤sql

with t1 as (

select

case when cast(tp_ContentTypeId as int)=-135314910 then N‘出差培训‘

when cast(tp_ContentTypeId as int)=-339275661 then tp_ColumnSet.value(N‘data(/nvarchar6)[1]‘, ‘nvarchar(30)‘)

when cast(tp_ContentTypeId as int)=1808594290 then N‘加班‘

when cast(tp_ContentTypeId as int) =-888033353 then N‘补休‘

else N‘值班‘

end type1 ,

b.tp_Title,[tp_Created] ,CONVERT(varchar(10), [tp_Created], 23) as 日期,tp_ColumnSet

, SUBSTRING(tp_ColumnSet.value(‘data(/datetime1)[1]‘, ‘varchar(30)‘),0,11) as 日期1,

tp_ColumnSet.value(N‘data(/nvarchar6)[1]‘, ‘nvarchar(30)‘) as 假期

from WSS_Content_80_XXJS.[dbo].[AllUserData] a left join [WSS_Content_80_XXJS].[dbo].[UserInfo] b on a.tp_Author=b.tp_ID

where

[tp_ListId]=‘4516AEC6-1278-4A7D-BC46-521A1F112F7A‘ and dateadd(HOUR,8,[tp_Created]) >‘2019-06-01‘

and dateadd(HOUR,8,[tp_Created])

--and tp_Author in(57,376)

)

select b.empcname,b.attrulename,b.schdate,t1.type1 from [AS0801_OnLine].[dbo].[V_SchResultInfoAll] b

left join t1 on t1.tp_Title=b.empcname collate Chinese_PRC_90_CI_AI and t1.日期1=b.schdate collate Chinese_PRC_90_CI_AI

where b.schdate>‘2019-06-01‘ and b.schdate

原文：https://www.cnblogs.com/mlwork/p/11528733.html