常用SQL语句

一、查找上一周的日期范围

DECLARE @TodayDayOfWeek INTDECLARE @EndOfPrevWeek DateTimeDECLARE @StartOfPrevWeek DateTime SET @TodayDayOfWeek = datepart(dw, GetDate())SET @EndOfPrevWeek = DATEADD(dd, -@TodayDayOfWeek, GetDate())SET @StartOfPrevWeek = DATEADD(dd, -(@TodayDayOfWeek+6), GetDate())SELECT * FROM DishType WHERE CreateTime  BETWEEN CONVERT(VARCHAR, @StartOfPrevWeek,7)ANDCONVERT(VARCHAR, @EndOfPrevWeek+1,7)

 二、SQL拆分函数

USE [HHChainSystemDB]GO/****** Object:  UserDefinedFunction [dbo].[Split]    Script Date: 12/22/2011 20:09:19 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOcreate FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))        returns @temptable TABLE (items varchar(8000))        as        begin            declare @idx int            declare @slice varchar(8000)                   select @idx = 1                if len(@String)<1 or @String is null  return                   while @idx!= 0            begin                set @idx = charindex(@Delimiter,@String)                if @idx!=0                   set @slice = left(@String,@idx - 1)                else                    set @slice = @String                           if(len(@slice)>0)               insert into @temptable(Items) values(@slice)                  set @String = right(@String,len(@String) - @idx)                if len(@String) = 0 break            end    return        end  select * from dbo.split('suzhi,张三,王五',',')

三、查询数据库里表行由大到小

SELECT     t.NAME AS TableName,    i.name as indexName,    p.[Rows],    sum(a.total_pages) as TotalPages,     sum(a.used_pages) as UsedPages,     sum(a.data_pages) as DataPages,    (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB,     (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB,     (sum(a.data_pages) * 8) / 1024 as DataSpaceMBFROM     sys.tables tINNER JOIN          sys.indexes i ON t.OBJECT_ID = i.object_idINNER JOIN     sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_idINNER JOIN     sys.allocation_units a ON p.partition_id = a.container_idWHERE     t.NAME NOT LIKE 'dt%' AND    i.OBJECT_ID > 255 AND       i.index_id <= 1GROUP BY     t.NAME, i.object_id, i.index_id, i.name, p.[Rows]ORDER BY     p.[Rows] desc

使用SQLServer 2008的CDC功能实现数据变更捕获