Sql按日周月数据查询

查询本日的记录
select count(*) from tableName where (DATEDIFF(dd, theDate, GETDATE()) = 0)
查询本周的记录
select count(*) from tableName where (DATEDIFF(wk, theDate, GETDATE()) = 0)
查询本月的记录
select count(*) from tableName where (DATEDIFF(mm, theDate, GETDATE()) = 0)
查询本季的记录
select count(*) from tableName where (DATEDIFF(qq, theDate, GETDATE()) = 0)
查询本年的记录
select count(*) from tableName where (DATEDIFF(yy, theDate, GETDATE()) = 0)

///2个参数:

intTop 取多少条

type 是取日,周或月

 

create PROCEDURE [dbo].[Product_Ranking]
 @intTop int,
    @type int
AS
    declare @sql varchar(500),
            @where varchar(70)
    if @type=1
      set  @where=' WHERE (DATEDIFF(dd, ClickDate, GETDATE()) = 0) '
    else
     begin
       if @type=2
        set   @where=' WHERE (DATEDIFF(wk, ClickDate, GETDATE()) = 0)'
       else
          begin
           if @type=3
            set  @where=' WHERE (DATEDIFF(mm, ClickDate, GETDATE()) = 0) '
           else
            set @where=' where 1=2 '
          end
      end
    set @sql='SELECT ProductID,ProductName,
(case StandPrice when 0 then
  (case AuctionStartPrice when 0 then
      (case CurrentPrice when 0 then 0 else CurrentPrice end)
      else AuctionStartPrice end)
 else StandPrice end) as Prices
 FROM  HLShop_Product WHERE
((StandPrice<>0 or AuctionStartPrice<>0 or CurrentPrice<>0) and ProductID IN('
   set @sql=@sql+'SELECT TOP '+cast(@intTop   as   varchar)+' ProductID FROM HLShop_Product_Click'
   set @sql=@sql+@where+' GROUP BY ProductID ORDER BY COUNT(ClickID) DESC))'
  exec(@sql)
这是一个动态sql的存储过程,功能是对商品进行按日,周,月来进行排行