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]) <'2019-07-01'

--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<'2019-07-01'