SQL查询时常用，但不熟悉的相关语句-----邮标，行号，临时表，类型转换，字符串函数,sql 判断语句,检查是不是数字,变量，sql split 函数

游标例子

----------------------------------------------------------------------------------------------------------

Declare @Age int

Declare @Name varchar(20)

Declare Cur Cursor For Select Age,Name From T_User

Open Cur

Fetch next From Cur Into @Age,@Name

While @@fetch_status=0

Begin

Update T_User Set [Name]=@Name,Age=@Age

Fetch Next From Cur Into @Age,@Name

End

Close Cur

Deallocate Cur

 

行号

----------------------------------------------------------------------------------------------------------

 

select Age,Name,RowNum=ROW_NUMBER() OVER (ORDER BY Name)

from T_User  

妙用

如取Age,Name值重复中最少一条的标识列

select Age,Name,RowNum=min(RowNum)

 from T_User  

 group by Age,Name

 

临时表

----------------------------------------------------------------------------------------------------------

创建并把数据导入到 #temp

select Age,Name,RowNum into #temp

from T_User

也可以跟创建表的方式一样来创建表

create table #temp(aa int,bb nvarchar(255))

删除临时表

drop table #temp

 

类型转换

----------------------------------------------------------------------------------------------------------

cast ([表列名] as [类型]) 或 convert([类型],[表列名])

eg. cast (age as float)

 

字符串函数

----------------------------------------------------------------------------------------------------------

检查字符串'-'在表列名第一次出来的索引号

CHARINDEX('-',[表列名])

left([表列名],2)等

 

检查是不是数字

----------------------------------------------------------------------------------------------------------

isnumeric（[表列名]）

 

sql 判断语句

----------------------------------------------------------------------------------------------------------

case

      when 条件1 then 值1

      when 条件2 then 值2

      ..... 

      else    其它值

end

 

变量

----------------------------------------------------------------------------------------------------------

声明

DECLARE @id int

设置值

set @id=1

sql split 函数

----------------------------------------------------------------------------------------------------------

CREATE FUNCTION Taurus_CommonSplit_String
(
@split_string varchar(max), --要进行分解的字符串
@tag_string varchar(10) --分解标志
)
RETURNS
@split_table TABLE
(
split_value varchar(200)
)
AS
BEGIN
declare @temp_string varchar(max)
declare @start_index int
declare @end_index int

while 1=1
begin
  set @start_index = 0
  select @end_index = CharIndex(@tag_string,@split_string,@start_index)
  if @end_index <> 0
  begin
   set @temp_string = Substring(@split_string,@start_index,@end_index)
   if @temp_string is not null and @temp_string <> ''
    insert into @split_table(split_value) values(@temp_string)
   set @start_index = @end_index + 1
   set @split_string = Substring(@split_string,@start_index,len(@split_string))
  end
  else
  begin
    if @split_string is not null and @split_string <> ''
       insert into @split_table(split_value) values(@split_string)
   break
  end
end

RETURN
END