1 case关键字
-----case start-------------------------------------------------------------------------------------
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//*
关键字 case
case 语法
--------1-----------------------------------------------------------------------------------------------
case <表达式A>
when <Α> then <值A>
when <表达式B> then <值B>
when <表达式C> then <值C>
else <值D>
end
----------2---------------------------------------------------------------------------------------------
case when <条件表达式1> then <值A>
when <条件表达式2> then <值B>
when <条件表达式3> then <值C>
else <值D>
end
*/
create table tabCase (UID int ,Areaid varchar(10),price money)
insert into tabCase select 1, 'GD', 8
union select 2, 'GD', 10
union select 3, 'GX', 12
union select 4, 'GX', 14
union select 5, 'SD', 16
union select 6, 'SD', 18
union select 7, 'GX', 20
union select 8, 'SD', 30
union select 9, 'GX', 40
union select 10, 'GD', 50
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
select UID , Areaid , price from tabCase
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
select UID, case areaid when 'GD' then '广东'
when 'GX' then '广西'
when 'SD' then '山东'
end as AreaidName
,price
from tabCase
----------------------------------------------------------------------------------------------------
select UID, case areaid when 'GD' then '广东'
when 'GX' then '广西'
when 'SD' then '山东'
end as Areaid
,case when price <=10 then '超低价商品'
when price <=20 then '低价商品'
when price <=50 then '高价商品'
end as price
from tab
------------------------------------------------------------------------------------------
select Areaid
,GDCount = sum(case when areaid='GD' then 1 else 0 end )
,GXCount = sum(case when areaid='GX' then 1 else 0 end )
,SDCount = sum(case when areaid='SD' then 1 else 0 end )
from tab group by areaid
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
drop table tab
-----case End-------------------------------------------------------------------------------------
2 convert 关键字
----convert start----------------------------------------------------
--1 日期转换并格式化
select convert(varchar,getdate(),120) , convert(varchar,getdate(),108)
--2 其它数据类型转换
select convert(int '12'),convert(varchar,125)
----convert end----------------------------------------------------
100 豎表轉橫表
drop table tb
create table tb(UserName varchar(10) , Subject varchar(10) , Score int)
insert into tb values('張三' , '語文' , 74)
insert into tb values('張三' , '數學' , 83)
insert into tb values('張三' , '物理' , 93)
insert into tb values('李四' , '語文' , 74)
insert into tb values('李四' , '數學' , 84)
insert into tb values('李四' , '物理' , 94)
select UserName,sum(case when Subject= '物理' then Score else 0 end) [物理],sum(case when Subject= '語文' then Score else 0 end) [語文],sum(case when Subject= '數學' then Score else 0 end) [數學] from tb group by UserName
declare @sql varchar(1000)
set @sql='select UserName'
select @sql=@sql+',sum(case when Subject= ''' +Subject+ ''' then Score else 0 end) ['+Subject+']' from (select distinct Subject from tb)a
set @sql = @sql + ' from tb group by UserName'
print @sql
exec(@sql)
3 比較二個日期區(DateS1,DateE1) , (DateS2,DateE2 )間是否有交集 ,有交集返回1,否則返回0
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
Code
CREATE FUNCTION [dbo].[F_DateMixed]
(
@DateS1 datetime , --起始日期1
@DateE1 datetime, --截止日期1
@DateS2 datetime , --起始日期2
@DateE2 datetime --截止日期2
)
RETURNS tinyint AS
BEGIN
DECLARE @RET TINYINT
IF @DateS1 <= @DateE2 AND @DateE1 >= @DateS2
SET @RET = 1
ELSE
SET @RET = 0
--if(getdate()>=@DateS1 and getdate()<=@DateE1)
--SET @RET = 1
-- else
-- SET @RET = 0
RETURN @RET
END
4 SQL 分頁儲存過程
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
Code
CREATE proc [dbo].[P_PageV2000]
@sqlstr nvarchar(4000),
@curpage int,
@pagesize int
AS
begin
--select @pagecount=3,@pagesize=3 ,@sqlstr='select * from news order by distribute_date desc, ID desc'
--select @sqlstr
set nocount on
declare @P1 int,
@rowcount int, @pagecount int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
set @pagecount = ceiling(1.0*@rowcount/@pagesize)
if @curpage<=1
set @curpage =1
if @curpage>@pagecount
set @curpage=@pagecount
select @rowcount as Total, @pagecount as PageCount,@curpage as CurPage
set @curpage=(@curpage-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@curpage,@pagesize
exec sp_cursorclose @P1
end
5 根據 資料表生成新增SQL句語
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
Code
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create proc [dbo].[proc_insert] (@tablename varchar(256))
as
begin
set nocount on
declare @sqlstr varchar(4000)
declare @sqlstr1 varchar(4000)
declare @sqlstr2 varchar(4000)
select @sqlstr='select ''insert '+@tablename
select @sqlstr1=''
select @sqlstr2=' ('
select @sqlstr1= ' values ( ''+'
select @sqlstr1=@sqlstr1+col+'+'',''+' ,@sqlstr2=@sqlstr2+name +',' from (select case
-- when a.xtype =173 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'+' end'
when a.xtype =104 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(1),'+a.name +')'+' end'
when a.xtype =175 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
when a.xtype =61 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'convert(varchar(23),'+a.name +',121)'+ '+'''''''''+' end'
when a.xtype =106 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.xprec+2)+'),'+a.name +')'+' end'
when a.xtype =62 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(23),'+a.name +',2)'+' end'
when a.xtype =56 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(11),'+a.name +')'+' end'
when a.xtype =60 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(22),'+a.name +')'+' end'
when a.xtype =239 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
when a.xtype =108 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.xprec+2)+'),'+a.name +')'+' end'
when a.xtype =231 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
when a.xtype =59 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(23),'+a.name +',2)'+' end'
when a.xtype =58 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'convert(varchar(23),'+a.name +',121)'+ '+'''''''''+' end'
when a.xtype =52 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(12),'+a.name +')'+' end'
when a.xtype =122 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(22),'+a.name +')'+' end'
when a.xtype =48 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(6),'+a.name +')'+' end'
-- when a.xtype =165 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'+' end'
when a.xtype =167 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
else '''NULL'''
end as col,a.colid,a.name
from syscolumns a where a.id = object_id(@tablename) and a.xtype <>189 and a.xtype <>34 and a.xtype <>35 and a.xtype <>36
)t order by colid
select @sqlstr=@sqlstr+left(@sqlstr2,len(@sqlstr2)-1)+') '+left(@sqlstr1,len(@sqlstr1)-3)+')'' from '+@tablename
-- print @sqlstr
exec( @sqlstr)
set nocount off
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
6 獲得表指定列的最大值加一
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
Code
CREATE PROCEDURE [dbo].[P_GetTableColumnMax]
-- Add the parameters for the stored procedure here
@TableName varchar(255),
@ColumnName varchar(255)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
SET NOCOUNT ON;
declare
@strSql varchar(2000)
set @strSql=' '
set @strSql=@strSql +'declare @MaxItemNo int '
set @strSql=@strSql +'SELECT '+ ' @MaxItemNo'+' =( '
set @strSql=@strSql +'CASE WHEN EXISTS('
set @strSql=@strSql +' SELECT '+ @ColumnName+' FROM '+ @TableName +' b '
set @strSql=@strSql + ' WHERE b.'+@ColumnName +' =1 ) '
set @strSql=@strSql + ' THEN MIN('+@ColumnName+')'+ ' + 1 ELSE 1 END) '
set @strSql=@strSql +' FROM '+@TableName
set @strSql=@strSql + ' WHERE NOT '+ @ColumnName+' IN (SELECT a.'+@ColumnName+' - 1 FROM '+@TableName+' a) '
set @strSql=@strSql + 'select @MaxItemNo as ItemNo'
print @strSql
exec( @strSql)