sql收藏

<不断更新中>

--一组有关（NULL）值处理的函数 [添加时间:2005-09-30]
  coalesce( expr1, expr2, ... expr_n )返回第一个非NULL值
  NVL( string1, replace_with )：如果string1为NULL则返回replace_with
  NVL2( string1, value_if_NOT_null, value_if_null )
  NANVL( value, replace_with )：replace_with is the value returned if value is Nan (not a number).

--嵌套case ..when ..then..else..end  [添加时间:2005-09-30]
  select case 1 when 1 then (case 0 when 0 then 'd' else 'e' end)  end
  --伪代码示例
  select
  case ISNUMERIC(数量一)*ISNUMERIC(价格)
     when 1 then a.gs*dj
     when 0 then
        --下一行可不用小括号括起来，效果是一样的
        (case ISNUMERIC(数量二)*ISNUMERIC(价格) when 1 then 数量二*价格 end )
     end as 总价 
  from table_name

--use tables of another database
select * from master.dbo.sysmessages
select * from master..sysmessages

--create a new table which has another table's structure
select * into target_table from source_table where 0=1

--to see a table or procedure existing or not
IF exists(select name from sysobjects where name='tablename' and type='U')

--
insert into target_table select * from source_table

--define output parameter and set default value
@affect_rows integer=0 output

--use procedure
create procedure  up_test 
@out_val integer=0 output
as
begin
 set @out_val=1
 return 9;
end

declare @ret_val int
declare @output_val int
exec @ret_val=up_test @out_val=@output_val output
select @ret_val,@output_val

--use transaction
begin tran
 delete from tablename
if @@error<>0
begin
 rollback tran
else
 commit tran

--define cursor
declare cur_tablename cursor FORWARD_ONLY | SCROLL
for select * from tablename
for update  [OF column_name [,...n]]

update tablename set column_name='r' where current of cur_tablename

--format string '050330' as '2005-03-30'
select '20'+SubString('050330',1,2) +'-'+ SubString('050330',3,2) +'-'+ SubString('050330',5,2)

--字段AA為字符型﹐它的內容為X+Y+Z。其中X和Z都為任意個字母字符﹐Y為任意個數字字符
--現我要得到X+Y﹐即去掉后而的Z。for example:'BR0021GD '-->'BR0021'
reverse(substring(reverse(AA),patindex('%[^0-9]%',reverse(AA)),len(AA))
replace(AA,right(AA,patindex('%[0-9]%',reverse(AA))),'')

--using variable as tablename(partly or fully)
declare @tablename varchar(20)
set @tablename='ment'
if EXISTS (SELECT name FROM sysobjects WHERE name = 'depart'+@tablename AND type = 'U')
begin
 print 'table department exists'
 exec('selcect * from depart'+@tablename)
end
else
 print 'table department donot exists'

--显示文章、提交人和最后回复时间
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate
 from table where table.title=a.title) b 　

--一个表中的Id有多个记录，把所有这个id的记录查出来，并显示共有多少条记录数。
select d_id,count(*) from department group by d_id WITH ROLLUP
select d_id, Count(*) from department group by d_id compute count(d_id)

--add check constraint
alter table people add constraint ccme_people_sex
 check (p_sex='m' or p_sex='w');
alter table people add constraint ccme_people_birthday
 check(p_birthday is null or p_birthday like '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]');
alter table people with nocheck
 add constraint cfme_people_dept foreign key(p_dept) references department(d_id);

--use case
select p_familyname+p_firstname as '姓名',
 case p_sex when 'm' then '男' when 'w' then '女' end as '性别' from tablename

--import_date is smalldatetime,type as yyyy-mm-dd
convert(varchar(10),import_date,20)
convert(varchar(10),import_date,121)