sql总结

 一个字　爽
create procedure select_use_module @staff_id varchar(8)
as
select distinct rtrim(a.sub_sys_id),rtrim(c.sub_sys_name),rtrim(a.module_id),rtrim(a.module_name),rtrim(isnull(b.if_grant,'n')),rtrim(isnull(b.if_write,'n')),
case
when b.staff_id=null
 then 'no'
else 'yes'
end
from sys_module as a,sys_staff_mod as b,sys_sub_sys as c
where
a.sub_sys_id = c.sub_sys_id
AND
b.module_id =* a.module_id
AND
b.staff_id = @staff_id order by a.sub_sys_id,a.module_id  

 

 用户信息批量修改
太安逸了，淘汰游标简直爽呆了
开始用游标把服务器都拖死了呵呵
update table_1 set tabl_1.cow_1 = tble2.cow1 from table1,tble2 where tabl_1.cow_1 = tble2.cow1

 

1.select top 25 * from tb_name where cow_id not　in (select 25*n cow_id from tb_name) order by cow_id
sybase 12.5不支持　mssql 可以
2.  mssql 利用其自带函数也可以

 

sybase 15.0出来就爽了免得再用正则表达式　简直就是一个郁闷
create function tiger_datetime (
@pstrin datetime)
returns varchar(10)
as
begin
declare @pstrout nvarchar(20)

select @pstrout = datename(yy,@pstrin)+"-"+datename(mm,@pstrin)+"-"+datename(dd,@pstrin)
return @pstrout
end

create procedure select_class_List @dept_id char(6)
as
SELECT  a.class_id, 
        a.class_name,
        dbo.tiger_datetime(a.start_course_time),  
        dbo.tiger_datetime(a.end_course_time),
   case when b.free_sum = null  then 0 else b.free_sum end,
        a.class_free_note
FROM  tb_class as a,
   (select class_id,
  sum(free) as free_sum 
 from tb_class_free
 where dept_id = @dept_id
 group by class_id,dept_id)  as b
where  a.dept_id = @dept_id
 AND a.class_id *= b.class_id
GO