下面是創建一個存儲過程:
USE [dnn6_1] GO /****** Object:? StoredProcedure [dbo].[WNKAdvise_UpdateItemsLog_GetTemplatesUsingInfo]??? Script Date: 2013/4/28 9:22:58 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --IF EXISTS(SELECT * FROM sysobjects WHERE name='WNKAdvise_UpdateItemsLog_GetTemplatesUsingInfo') -- DROP PROCEDURE WNKAdvise_UpdateItemsLog_GetTemplatesUsingInfo
ALTER PROCEDURE [dbo].[WNKAdvise_UpdateItemsLog_GetTemplatesUsingInfo] ? -- 定義存儲過程的參數-- ?? @TemplateType int, ?? @TemplateAddType int, ?? @StartDate DateTime, ?? @EndDate DateTime AS BEGIN ??? SET DATEFORMAT ymd; ?--定義時間變量-- ?declare @EndDate0 DateTime ?declare @StartDate0 DateTime ?declare @StartDate1 DateTime ?declare @StartDate2 DateTime
??? set @StartDate = null ??? set @EndDate = null
?--獲取系統的當前時間 ?select? @EndDate0 = getdate() ?--select? @StartDate0 = dateadd(dd,-1,getdate()) ?--120的作用是將獲取的當前時間按2006-05-16 10:57:49格式顯示 ??? select? @StartDate0 = convert(varchar(10),dateadd(hh,-24,getdate()),120)? ?--聲明將當前時間退回到七天前 ??? select? @StartDate1 = dateadd(dd,-7,getdate()) ?--聲明將當前時間回退到一個月前 ?select? @StartDate2 = dateadd(mm,-1,getdate())?
???? --當天 ?SELECT lg.TemplateId,count(lg.TemplateId) as DayCount INTO #DayAccess ?FROM dbo.WNKAdvise_UpdateItemsLog? lg ??? inner join WNKAdvise_AdviseTemplates t ON t.TemplateId = lg.TemplateId ?WHERE (CreatedTime? > @StartDate0 OR @StartDate0 is null) ??AND (CreatedTime <= @EndDate0 OR @EndDate0 is null) ??AND (TemplateType = @TemplateType OR @TemplateType=-1) ??AND (TemplateAddType = @TemplateAddType OR @TemplateAddType = -1) ?GROUP BY lg.TemplateId
?--最近一周 ?SELECT lg.TemplateId,COUNT(lg.TemplateId) as WeekCount INTO #WeekAccess ?FROM dbo.WNKAdvise_UpdateItemsLog? lg ??? inner join WNKAdvise_AdviseTemplates t ON t.TemplateId = lg.TemplateId ?WHERE ???? (CreatedTime >= @StartDate OR @StartDate is null) ??AND (CreatedTime <= @EndDate OR @EndDate is null) ??AND (TemplateType = @TemplateType OR @TemplateType=-1) ??AND (TemplateAddType = @TemplateAddType OR @TemplateAddType = -1) ?GROUP BY lg.TemplateId
??? --最近一月 ?SELECT lg.TemplateId,COUNT(lg.TemplateId) as MonthCount INTO #MonthAccess ??FROM dbo.WNKAdvise_UpdateItemsLog lg ??? inner join WNKAdvise_AdviseTemplates t ON t.TemplateId = lg.TemplateId ?WHERE ???? (CreatedTime >= @StartDate2 OR @StartDate2 is null) ??AND (CreatedTime <= @EndDate0 OR @EndDate0 is null) ??AND (TemplateType = @TemplateType OR @TemplateType=-1) ??AND (TemplateAddType = @TemplateAddType OR @TemplateAddType = -1) ?GROUP BY lg.TemplateId
?--總計 ??? SELECT? lg.TemplateId,Count(lg.TemplateId) as ToatalCount INTO #HistoryAccess ??FROM dbo.WNKAdvise_UpdateItemsLog lg ??? inner join WNKAdvise_AdviseTemplates t ON t.TemplateId = lg.TemplateId ?WHERE (TemplateType = @TemplateType OR @TemplateType=-1) ??AND (TemplateAddType = @TemplateAddType OR @TemplateAddType = -1) ?GROUP BY lg.TemplateId
?SELECT h.TemplateId,ToatalCount FROM #WeekAccess w,#HistoryAccess h
?END
執行后有很多的重復項,如何刪除重復項值保留一項!俺是第一次寫存儲過程,請大蝦門賜教!
sql 刪除重復項
白豬掌柜的
2018-12-07 07:36:37