SQL 查询关键字用法祥解

1 case关键字  

-- ---case start-------------------------------------------------------------------------------------
/**/ /*
关键字 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

select  UID , Areaid , price  from  tabCase

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

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

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 分頁儲存過程

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句語

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 獲得表指定列的最大值加一

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)

 

转载于:https://www.cnblogs.com/brakehuang/archive/2009/06/28/1512545.html