SQL之乱笔--从基础开始

-----------------------日期星期的计算---------------------------------------declare  @Date datetime=getdate()declare  @DateString nvarchar(8)='20121225'select  convert(nvarchar(10),@Date,126) as [Date]  ,CAST(@DateString as datetime) AS DateStringToDate  ,CONVERT(datetime,@DateString,112) AS NvarcharToDate  ,LEFT(@DateString,4)+'^'+substring(@DateString,5,2)+'^'+RIGHT(@DateString,2) AS OtherDateToDate  ,datepart(weekday,@Date)-1 AS [Weekday]  ,case convert(varchar,(DATEPART(weekday,@Date)-1))   when 1 then N'星期一'  when 2 then N'星期二'  when 3 then N'星期三'  when 4 then N'星期四'  when 5 then N'星期五'  when 6 then N'星期六'  when 0 then N'星期日'  else N'世界末日'  end as [weekday]  ,case convert(varchar(10),(DatePart(weekday,CAST(@DateString AS Datetime))-1))  when 1 then N'星期一'  when 2 then N'星期二'  when 3 then N'星期三'  when 4 then N'星期四'  when 5 then N'星期五'  when 6 then N'星期六'  when 0 then N'星期日'  else N'世界末日'  end as weekday------------------临时表的简单使用方法------------------------  select b.BranchName as WideBranchName ,a.BranchName into #temp  from (select BranchCode,BranchName,WideBranchCode from Branch where WideBranchCode is not null) as a  join Branch as b on a.WideBranchCode=b.BranchCode  order by b.BranchName  select * from #temp-------------------------模糊查询-----------------------------------------select BrandCodefrom Brandwhere BrandCode like '%E%'except--但不包含select BrandCode from Brandwhere BrandCode like '%B%'select BrandCodefrom Brandwhere BrandCode like '%B%'intersect --且包含select BrandCodefrom Brandwhere BrandCode like '%E%'--------------------字符串语句的使用---------------------------------@sql的类型最好用 NVARCHAR(MAX)declare @sql varchar(400)=''set @sql='select * from brand where BrandCode like ''%B%''unionselect * from Brandwhere BrandCode like ''%E%'''execute (@sql)--------------------------------------------------select COUNT(*),COUNT(WideBranchCode) from Branch--count统计的是不为null的记录-------------------ROW_NUMBER的使用-----------------------------------select row_num,BrandCode,StyleCode,Price into #tt from(select  ROW_NUMBER() over(order by brandcode,StyleCode,Price) AS row_num ,BrandCode,StyleCode,Price from BrandPrice  where  enddate='99991231' and brandcode='bc'--- order by Price desc   ) as a  -- select row_num -- from #tt-- order by price desc--fetch first 10 ROWS ONLY  drop table #tt ---------------GROUP BY和HAVING-------------------------------- select  brandcode, COUNT(*) as num from EtcPay  group by BrandCode-- having COUNT(*)>6--having COUNT(*)=6 or COUNT(*)=2having COUNT(*) in(2,6) order by num desc  ------------------------- select 1+2 union select '1'+'2'  select '''1'+'2' -----------------------------------查询生日是星期几-------------------------------------------------- select top 10 Birthday ,CONVERT(nvarchar(8), DATEADD(MONTH,-1,Birthday),112) as a,case when (datepart(WEEKDAY,DATEADD(year,1,Birthday))-1) =1then N'星期一'when (datepart(WEEKDAY,DATEADD(year,1,Birthday))-1)=2 then N'星期二'when (datepart(WEEKDAY,DATEADD(year,1,Birthday))-1)=3 then N'星期三'when (datepart(WEEKDAY,DATEADD(year,1,Birthday))-1)=4 then N'星期四'when (datepart(WEEKDAY,DATEADD(year,1,Birthday))-1)=5 then N'星期五'when (datepart(WEEKDAY,DATEADD(year,1,Birthday))-1)=6 then N'星期六'when (datepart(WEEKDAY,DATEADD(year,1,Birthday))-1)=0 then N'星期日'else 'unkown'end as [weekday] from Employee  ---------------------------------------------------------------------------------------select nullif(StartDate,enddate) as chkdates---nullif--比较两个值如果不等价则返回第一个值，如果相等则返回第一个的类型的空值nullfromBrandPrice--------------------------------------------------------------------------------------select  BrandCode,Price,COUNT(*) --over() as num-- 对于查询结果的每一行都返回所有符合条件的行的条数。from BrandPricewhere Price>500group by BrandCode,Priceselect BrandCode,Price,COUNT(*) over(partition by brandcode) as num--表示对结果集按照BrandCode进行分区，并且计算当前行所属的组的聚合计算结果,COUNT(*) over(partition by price)from BrandPricewhere Price>500-------------------------------即用即释临时表用法---------------------------------------------with tt(BrandCode,Price)AS(select BrandCode,Price  from BrandPrice where Price>=5000)select * from tt where Price >7000SELECT COUNT(1) FROM SysColumns WHERE id=OBJECT_ID('SaleDtl') --查询一个表有多少列SELECT CASE CONVERT(VARCHAR,(DATEPART(WEEKDAY,DATEADD(YEAR,-1,GETDATE()))-1)) WHEN 1 THEN N'星期一' WHEN 2 THEN N'星期二' when 3 then N'星期三'     when 4 then N'星期四'     when 5 then N'星期五'     when 6 then N'星期六'     when 0 then N'星期日'     else N'世界末日'   END AS N'Last Year Of The Week'---去年这周是星期几------------------------------函数和表变量--------------------------------------------alter function GetDay(@StartDate datetime,@EndDate datetime)returns  @Date table(Date Datetime,[weekday] char(1))asbegin--create table #Date(Date DateTime)while @StartDate<=@EndDatebegininsert into @Date(Date,[weekday]) values(@StartDate,convert(char(1),datepart(weekday,@StartDate)))set @StartDate=DATEADD(day,1,@StartDate)endreturn end