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 BrandCode
from Brand
where BrandCode like '%E%'
except--但不包含
select BrandCode 
from Brand
where BrandCode like '%B%'

select BrandCode
from Brand
where BrandCode like '%B%'
intersect --且包含
select BrandCode
from Brand
where BrandCode like '%E%'
--------------------字符串语句的使用------------------------------
---@sql的类型最好用 NVARCHAR(MAX)
declare @sql varchar(400)=''
set @sql='select * 
from brand 
where BrandCode like ''%B%''
union
select * 
from Brand
where 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(*)=2
having 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--比较两个值如果不等价则返回第一个值，如果相等则返回第一个的类型的空值null
from
BrandPrice
--------------------------------------------------------------------------------------

select  BrandCode,Price,COUNT(*) --over() as num-- 对于查询结果的每一行都返回所有符合条件的行的条数。
from BrandPrice
where Price>500
group by BrandCode,Price

select BrandCode,Price
		,COUNT(*) over(partition by brandcode) as num--表示对结果集按照BrandCode进行分区，并且计算当前行所属的组的聚合计算结果
		,COUNT(*) over(partition by price)
from BrandPrice
where Price>500
-------------------------------即用即释临时表用法---------------------------------------------
with tt(BrandCode,Price)
AS
(
	select BrandCode,Price  from BrandPrice where Price>=5000
	)
select * from tt where Price >7000

SELECT 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))
as
begin
	--create table #Date(Date DateTime)
	
	while @StartDate<=@EndDate
		begin
		
			insert into @Date(Date,[weekday]) values(@StartDate,convert(char(1),datepart(weekday,@StartDate)))
			set @StartDate=DATEADD(day,1,@StartDate)
			
		end
	
	return 
end