游标例子
----------------------------------------------------------------------------------------------------------
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