-- 数据库SQL总结中...........

--SQL分类： 
(CREATE，ALTER，DROP，DECLARE) ---DDL—数据定义语言
(SELECT，DELETE，UPDATE，INSERT) ---DML—数据操纵语言
(GRANT，REVOKE，COMMIT，ROLLBACK) --DCL—数据控制语言
--设置内存选项
--设置 min server memory 配置项
EXEC sp_configure N'min server memory (MB)', 0

--设置 max server memory 配置项
EXEC sp_configure N'max server memory (MB)', 256

--使更新生效
RECONFIGURE WITH OVERRIDE
------设置内存选项

--创建数据库，指定日志文件最大大小
CREATE DATABASE karaok
 ON (NAME = N'karaok', FILENAME = N'C:/Program Files/Microsoft SQL Server/MSSQL/data/karaok.mdf' , SIZE = 200, FILEGROWTH = 10%)
 LOG ON (NAME = N'karaok_log', FILENAME = N'C:/Program Files/Microsoft SQL Server/MSSQL/data/karaok_log.LDF', SIZE = 200, FILEGROWTH = 10%, MAXSIZE = 2000)
Go
--备份数据库
USE master
exec sp_addumpdevice  'disk', 'QuickChainTY20091210', 'D:/Program Files/Microsoft SQL Server/BACK/QuickChainTY20091210.BAK'
BACKUP DATABASE QuickChainTY TO QuickChainTY20091210
--备份数据库日志
exec sp_addumpdevice  'disk', 'QuickChainTYLog20091210', 'D:/Program Files/Microsoft SQL Server/BACK/QuickChainLogTY20091210.BAK'
BACKUP LOG QuickChainTY TO QuickChainTYLog20091210

--备份 
declare  @sql  varchar(8000)  
set  @sql='backup  database  mis    to  disk=''d:/databack/mis/mis'  
+rtrim(convert(varchar,getdate(),112))+'.bak'''  
exec(@sql)    
--删除15天前备份文件  
set  @sql='del  d:/databack/mis/mis'  
+rtrim(convert(varchar,getdate()-15,112))+'.bak'''  
exec  master..xp_cmdshell  @sql
GO
--创建表、试图、存储过程、函数
create table newtable (columnid int primary key ,columnname char(20)  not null )
create table newtable (columnid int identity(1,1))
select * into newtable from oldtable
create view viewname
create procedure pro_name
(@a varchar,
 @b int =1)
create FUNCTION f_GetCustomerName(@CustomerCode varchar(32))
RETURNS varchar(128)
AS
BEGIN
 declare @Name varchar(128)
 select @Name=Name from ComCustomer where CustomerCode=@CustomerCode
 set @Name=IsNull(@Name,@CustomerCode)
 RETURN @Name
END
--删除表、试图、存储过程、函数
drop table tablename
drop view viewname
drop proc procedurename
drop function functionname
---修改表
alter table tablename add columnnew varchar(100)default ('')--添加列
alter table tablename add createdate datetime  null default  getdate() with values
alter table tablename drop columnold --删除列
alter table tablenaem add column_a  varchar(200) null constraint yueshu unique--添加约束
---填充数据或创建表
insert into a (aa,bb,cc)values (1,2,3)
insert a select 1,2,3
 ---要求目标表newtable不存在，因为在插入时会自动创建表newtable，并将oldtable中指定字段数据复制到newtable中。
select * into newtable from oldtable
select top 0 * into newtable from oldtable
--要求目标表Table2必须存在，由于目标表Table2已经存在，所以我们除了插入源表Table1的字段外，还可以插入常量
Insert into Table2(field1,field2) select value1,value2 from Table1
--删除数据
delete from tablename --记录被删除的每行数据操作日志
truncate table tablename--释放整页不记录操作日志(初始化表tablename )
--修改表中的数据
update tablename set id=232 where id=1
update tablename set quantity=(select quantity from sectable a where a.id=tablename.id )

---将aaa表中的数据更新到psy_t_markinfo表中

update psy_t_markinfo set UserDef1=(select UserDef1 from aaa where aaa.MarkID=psy_t_markinfo.MarkID)
--收缩数据库表
dump   transaction  tablename   with   no_log  

truncate,delete,drop--的异同点   
 /*
  truncate,delete,drop的异同点       
  注意:这里说的delete是指不带where子句的delete语句     
        
  相同点:truncate和不带where子句的delete,   以及drop都会删除表内的数据       
  不同点:       
  1.   truncate和   delete只删除数据不删除表的结构(定义)       
            drop语句将删除表的结构被依赖的约束(constrain),触发器(trigger),索引(index);   依赖于该表的存储过程/函数将保留,但是变为invalid状态.      
  2.delete语句是dml,这个操作会放到rollback   segement中,事务提交之后才生效;如果有相应的trigger,执行的时候将被触发.      
          truncate,drop是ddl,   操作立即生效,原数据不放到rollback   segment中,不能回滚.   操作不触发trigger.      
  3.delete语句不影响表所占用的extent,   高水线(high   watermark)保持原位置不动       
        显然drop语句将表所占用的空间全部释放       
        truncate   语句缺省情况下将空间释放到   minextents个   extent,除非使用reuse   storage;       truncate会将高水线复位(回到最开始).      
  4.速度,一般来说:   drop>   truncate   >   delete       
  5.安全性:小心使用drop   和truncate,尤其没有备份的时候.否则哭都来不及       
  使用上,想删除部分数据行用delete,注意带上where子句.   回滚段要足够大.       
  想删除表,当然用drop       
  想保留表而将所有数据删除.   如果和事务无关,用truncate即可.   如果和事务有关,或者想触发trigger,还是用delete.       
  如果是整理表内部的碎片,可以用truncate跟上reuse   stroage,再重新导入/插入数据
*/
---
--返回当前的用户。
select CURRENT_USER
select  USER_NAME()
---定义一个游标及while的使用
DECLARE abc CURSOR FOR
SELECT * FROM Shippers

OPEN abc

FETCH NEXT FROM abc
WHILE (@@FETCH_STATUS = 0)
   FETCH NEXT FROM abc

CLOSE abc
DEALLOCATE abc

---while
while (@count<100)
begin
 print @count;
 continue;
end
else
break;

----快速查看表结构和试图、存储过程索引等信息
sp_help tablename
sp_helptext viewname or procedurename
sp_helpindex tablename or viewname

---创建表和试图的索引
create index indexname on  tablename  (columnname)
create (unique) index indexname on  viewname  (columnname)
drop index indexname

/*几个高级查询运算词 
A： UNION 运算符 
UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。
B： EXCEPT 运算符 
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。
C： INTERSECT 运算符 
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。
注：使用运算词的几个查询结果行必须是一致的。
*/
--between的用法,between限制查询数据范围时包括了边界值,not between不包括 
select * from table1 where time between time1 and time2 
select a,b,c, from table1 where a not between 数值1 and 数值2

--两张关联表，删除主表中已经在副表中没有的信息 
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )

--日程安排提前五分钟提醒 
 select * from 日程安排 where datediff('minute',f开始时间,getdate())>5

--一条sql 语句搞定数据库分页 
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段

--选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) 
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)

--包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 
(select a from tableA ) except (select a from tableB) except (select a from tableC)

--随机取出10条数据 
select top 10 * from tablename order by newid()

--随机选择记录 
select newid()

--列出数据库里所有的表名 
select name from sysobjects where type='U'

--列出表里的所有的字段
select name from syscolumns where id=object_id('TableName') 
---重复行中只显示第一行的某个字段数据，其他行为空或0
SELECT 
 CASE WHEN (ROW_NUMBER() OVER (PARTITION BY JobNo ORDER BY tJobNo)) = 1 THEN InvoiceAmount ELSE 0 END AS InvoiceAmount
FROM TrkJob
-----------------------------------排序函数
---自动生成序号
select row_Number() over(order by ProjectNo desc )as RowNum from TrkJob
select row_number() over(partition by (OperateDate) order by projectno asc) from trkload
---返回结果集分区中行的排名，在排名中没有任何间断。行的排名等于所讨论行之前的所有排名数加一
select DENSE_RANK () over(order by loadno desc)as 'dense_rank' from trkload
select DENSE_RANK() over(partition by (OperateDate) order by projectno asc) from trkload
----返回结果集的分区内每行的排名。行的排名是相关行之前的排名数加一。
select RANK () over(order by loadno  desc )as 'ranks' from trkload
select RANK() over(partition by (OperateDate) order by projectno asc) from trkload

--某列值相等，其他列值不等，只在第一行显示一个相等的值，其他行为空

DECLARE @TAB TABLE ([col1] varchar(1),[col2] int,[col3] int)
insert @TAB
select 'a',1,2 union all
select 'a',2,3 union all
select 'a',3,4 union all
select 'b',4,5 union all
select 'b',5,6 union all
select 'b',6,7
--------------开始查询--------------------------
select
   case px when 1 then col1 else '' end as col1,
   col2,col3
from
   (select px=row_number()over(partition by col1 order by getdate()),* from @TAB)t
   
---显示10%的数据
select * from 
(select ntile(10) over(order by loadno desc)as ass,loadno,projectno,jobno,forwardercode,operatedate from trkload
)qq where ass=1
-----------------------------------排序函数
--日期函数
set datefirst 1
select @@datefirst--美国默认第一天为星期日 7
select datepart(year,getdate()),datepart(month,getdate()),datepart(day,getdate()),datepart(week,getdate()),
  datepart(quarter,getdate()),datepart(dayofyear,getdate()), datepart(weekday,getdate()),
  datepart(hour,getdate()),datepart(minute,getdate()),datepart(second,getdate()),datepart(millisecond,getdate())
---查询所有数据库名
select * from master.dbo.sysdatabases
---查询数据库表中的所有列（字段）
select * from syscolumns 
name id colid 
//字段名称 //表ID号. 字段ID号.
----sysobjects有用的字段名称和相关说明
select * from sysobjects 
name id xtype uid 
对象名. 对象ID 对象类型 所有者对象的用户ID。 
对象类型(xtype)。可以是下列对象类型中的一种： 
C = CHECK 约束 
D = 默认值或 DEFAULT 约束 
F = FOREIGN KEY 约束 
L = 日志 
FN = 标量函数 
IF = 内嵌表函数 
P = 存储过程 
PK = PRIMARY KEY 约束（类型是 K） 
RF = 复制筛选存储过程 
S = 系统表 
TF = 表函数 
TR = 触发器 
U = 用户表 
UQ = UNIQUE 约束（类型是 K） 
V = 视图 
X = 扩展存储过程

聚合函数:
--函数名称  函数说明  函数返回值  
Avg()  --返回组中各值的平均值  int,double,money,float  
Binary_checksum()  --返回按照表的某一行或某一组表达式计算出来的二进制表达式  二进制数字  
Checksum() -- 返回按照表的某一行或某一组表达式计算出来的效验和的值  int  
Checksum_agg()  --返回组中各值的效验和  int  
Count()  --返回组中的项数  int  
Count_big()  --返回组中的项数  int  
Grouping()  --聚合函数:当行由CUBE或ROLLUP运算符添加时,该函数将导致列的输出值为1,当行不由CUBE或ROLLUP运算符添加时,该函数将导致列的输出值为0  int 
Max()  --返回表达式的最大值    
Min()  --返回表达式的最小值    
Stdev()  --返回给定表达式中所有值的标准偏差    
Stdevp()  --返回给定表达式中所有值的总体标准偏差    
Sum()  ---返回表达式中所有值和或仅非重复值的和    
Var()  --返回表达式中所有值的方差    
Varp()  --返回表达式中所有值的总体方差    
配置函数:      
@@Datefirst  --返回SET DATAFIRST参数的当前值,该值表示为每周指定的第一天    
@@Dbts  --返回一个时间戳    
@@Langid  --返回本地语言的标识    
@@Language  --返回本地语言的名称    
@@Lock_Timeout  --返回当前会话的当前锁定超时设置(毫秒)    
@@Max_Connections  --返回同时连接的最大用户数    
@@Max_Precision  --返回浮点类型的精度级别    
@@Nestlevel  --返回当前存储过程的嵌套级别    
@@Options  --返回当前SET选项信息    
@@Remserver  --返回在远程登陆记录中显示的名称    
@@Servername  --返回本地SQL的名称    
@@Servicename  --返回SQL注册表项名称    
@@Spid  --返回当前用户进程PID    
@@Textsize  --返回SET选项的TEXTSIZE选项的当前值    
@@Version  --返回SQL的安装日期,版本,CPU类型    
游标函数:      
@@Cursor_Rows  --返回连接上打开的上一个游标中的当前限定行的数目    
Cursor_Status() -- 一个标量函数,允许存储过程调用方确定该存储过程是否为给定的参数返回结果集和游标    
@@Fetch_Status  --返回针对连接当前打开的任何游标发出的上一条游标FETCH语句的状态    
日期和时间函数      
Dateadd()  --根据日期增量,返回新的日期    
Datediff()  --返回跨两个指定日期的日期和时间边界数    
Day()  --返回日期中的日    
Getdate()  --返回日期时间    
Getutcdate()  --返回UCT的日期    
Month()  --返回日期中的月    
Year() -- 返回日期中的年    
数学函数      
Abs()  --返回绝对值    
Acos()  --返回以弧度表示的角,其余弦为给定浮点表达式    
Asin()  --返回以弧度表示的角,其正弦为给定浮点表达式    
Atan()  --返回以弧度表示的角,其正切为给定浮点表达式    
Atn2()  --返回以弧度表示的角,其正切为2给定浮点表达式的商    
Ceiling()  --返回大于等于给定数值表达式的最小整数    
Cos()  --返回给定角的三角余怰    
Cot()  --返回给定角的三角余切    
Degrees()  --如果角以弧度表示,返回以度数表示的角    
Exp()  --返回给定浮点表达式的指数值    
Floor()  --返回小于等于给定数值表达式的最大整数    
Log() -- 给定浮点表达式的自然对数    
Log10() -- 给定浮点表达式的常用对数    
Pi()  --返回圆周率    
Power()  --给定表达式的指定幂的值    
Radians()  --返回指定数值表达式的弧度    
Rand() -- 随机浮点数    
Round()  --舍入到指定精度    
Sign()  --返回指定表达式的符号    
Sin()  --三角正怰    
Sqrt()  --开方    
Square()  --给参数平方    
Tan()  --给参数正切    
元数据函数      
Col_Length()  --返回指定表中列的长度(字节)    
Col_Name()  --返回数据库表ID, 列ID的列名称    
Columnproperty()  --返回列参数,过程参数的属性值    
Databaseproperty()  --返回数据库和属性名称的属性值    
Databasepropertyex()  --返回数据库和属性名称的属性值    
Db_Id()  --返回数据库名称的标识号    
Db_Name()  --返回数据库ID的名称    
File_id()  --返回数据库中给定逻辑文件名的文件标识号    
File_Name()  --返回数据库中给定文件标识号的逻辑文件名    
Filegroup_Name()  --返回数据库中给定逻辑文件组的文件标识号    
Filegroupproperty()  --返回数据库中给定逻辑文件组和属性名称的指定属性值    
Fileproperty() -- 返回文件名,属性名称的指定属性值    
::fn_Listextendedproperty()  --返回数据库对象的扩展属性值  
Isdate()     --确定输入表达式是否为有效日期.要验证其是否为日期的表达式。expression 是 text、ntext 表达式和 image 表达式以外的任意表达式，可以隐式转换为 nvarchar
Isnull()    --如果 check_expression 不为 NULL，则返回它的值；否则，在将 replacement_value 隐式转换为 check_expression 的类型（如果这两个类型不同）后，则返回前者。 
Isnumeric() --当输入表达式的计算结果为有效的 numeric 数据类型时，ISNUMERIC 返回 1；否则返回 0    
Newid()      
Nullif()  --如果两个指定的表达式相等，则返回空值。如果两个表达式不相等，则 NULLIF 返回第一个 expression 的值。如果表达式相等，则 NULLIF 返回第一个 expression 类型的空值。   
Parsename()  --返回对象名称的指定部分。可以检索的对象部分有对象名、所有者名称、数据库名称和服务器名称.

 

-----常用字符串函数
PatIndex--函数支持使用通配符来进行搜索，然而CHARINDEX不支持通佩符,函数返回字符或者字符串在另一个字符串或者表达式中的起始位置，PATINDEX函数支持搜索字符串中使用通配符，这使PATINDEX函数对于变化的搜索字符串很有价值。
CharIndex--函数返回字符或者字符串在另一个字符串中的起始位置。

1、)CharIndex 
 CharIndex (分隔符，被查找字段[,开始位置] 
 
2、)PatIndex ( ’%pattern%’ , expression ) 
      --Pattern是你要搜索的字符串，expression是被搜索的字符串。一般情况下expression是一个表中的一个字段，pattern的前后需要用“%”标记，除非你搜索的字符串在被收缩的字符串的最前面或者最后面。
      --这里的%标记告诉PATINDEX函数去找字符串“pattern”，不管被搜索的字符串中在“pattern”的前后有多少字符！
      --假如你想知道被搜索字符串是否由特定的字符串开始，你可以省去前面的%标记。PATINDED函数就要这样写： 
      PatIndex(’pattern%’,expression) 
3、)Substring(被截取的字符串，起始位置，长度)
4、)Stuff(原字符串[常量|变量|字符列|二进制数据列],指定删除和插入的开始位置,指定要删除的字符数,将要插入的字符或串)---函数将字符串插入另一字符串。它在第一个字符串中从开始位置删除指定长度的字符；然后将第二个字符串插入第一个字符串的开始位置。
--例如：
-----从字符串中取数字          
IF OBJECT_ID('tb_test') IS NOT NULL DROP TABLE tb_test
GO
 CREATE TABLE tb_test(columnfile VARCHAR(50))
---填充测试数据
  INSERT  tb_test
     SELECT 'ADSF-123A1' UNION ALL
     SELECT 'ADDFSF - 36B133' UNION ALL
     SELECT 'AD- 555FDF3.5'
----查询     
 SELECT 
  CASE WHEN PATINDEX('%[^0-9]%',columnfile_b)=0 
    THEN columnfile_b 
    ELSE LEFT(columnfile_b,PATINDEX('%[^0-9]%',columnfile_b)-1) 
    END
 FROM (
   SELECT 
    CASE WHEN PATINDEX('%[0-9]%',columnfile)=0 
      THEN NULL 
      ELSE STUFF(columnfile,1,PATINDEX('%[0-9]%',columnfile)-1,'')
      END AS columnfile_b
   FROM tb_test
 ) AA
------从字符串中取数字

5、)Reverse(字符串)--将字符串反转
6、)Replicate(expression,n)--将指定的对象重复N次

----常用日期函数
7、)DateDiff  函数
  DateDiff (dd|yyyy|mm,当前被比较的日期字段,比较的日期)
  
8、)DateAdd(day|month|year,16|3|3,getdate()) 得到在指定的日期上增加指定的日期（天，月，年）的日期

9、)DateDiff(day|month|year, getdate(),getdate()-10) 获取两个指定日期之间的日期间隔

10、)DateName(day|month|year,getdate()) 返回表示指定日期的指定日期部分的字符串。

11、)DatePart(day|month|year,getdate())返回表示指定日期的指定日期部分的整数。

12、)CAST 和 CONVERT 将某种数据类型的表达式显式转换为另一种数据类型。CAST 和 CONVERT 提供相似的功能。
 CAST ( expression AS data_type )

 CONVERT (data_type[(length)], expression [, style])
/*参数 
data_type    表达式将转换成的数据类型。
expression    要转换的表达式。
style  转换的格式（120 代表日期的格式“yyyy-MM-dd”）
*/

-----转载联机帮助
-PARSENAME ( 'object_name' , object_piece ) ---返回对象名称的指定部分。可以检索的对象部分有对象名、所有者名称、数据库名称和服务器名称。
/*' object_name ' 
要检索其指定部分的对象的名称。object_name 的数据类型为 sysname。此参数是可选的限定对象名称。如果对象名称的所有部分都是限定的，则此名称可包含四部分：服务器名称、数据库名称、所有者名称以及对象名称。
object_piece 
要返回的对象部分。object_piece 的数据类型为 int 值，可以为下列值。
1 = 对象名称
2 = 架构名称
3 = 数据库名称
4 = 服务器名称
返回类型为nchar
*/
SELECT ParseName('AdventureWorks..Contact', 1) AS 'Object Name';
SELECT PARSENAME('AdventureWorks..Contact', 2) AS 'Schema Name';
SELECT PARSENAME('AdventureWorks..Contact', 3) AS 'Database Name;'
SELECT PARSENAME('AdventureWorks..Contact', 4) AS 'Server Name';
--转载联机帮助

----嵌套游标案例
 declare TaoCan_LuoDan_Cursor cursor for --定义一个游标，用来依次从自动匹配好为套餐中取酒菜名和数量
   select TaoCan_Quantity,BusDetaOld_RecipeName from #TaoCan_PiPeiDetail
   open TaoCan_LuoDan_Cursor
   fetch next from TaoCan_LuoDan_Cursor
   into @taocannum,@taocan_recipename
   while @@fetch_status=0
    begin--根据酒菜名相同依次循环更新从零点酒菜中匹配出去的酒菜数量后还剩下的数量（相当与实际零点就酒水数量）
     if exists (select * from #TaoCanLuoDan where Recipe_Name=@taocan_recipename)
      Begin
       declare PayType_Cursor cursor for
       select Recipe_Name,Quantity,BusinessDetail_ID from #TaoCanLuoDan whereRecipe_Name=@taocan_recipename
       open PayType_Cursor
       fetch next from PayType_Cursor into @PayType_Name ,@PayType_Num,@BusinessDetail_ID
       while @@fetch_status=0
        begin
         if @PayType_Num< @taocannum 
          begin
           set @taocannum=@taocannum-@PayType_Num
           delete from #TaoCanLuoDan where BusinessDetail_ID=@BusinessDetail_ID andRecipe_Name=@taocan_recipename
          end
         else if @PayType_Num>@taocannum
          begin
           update #TaoCanLuoDan set Quantity=@PayType_Num-@taocannum,
            SingleRecipeTotal=(@PayType_Num-@taocannum)*UnitPrice*DisRatio*0.01,
            SingleRecipeTotal_last=(@PayType_Num-@taocannum)*UnitPrice*DisRatio*0.01,
            SingleRecipeSum=(@PayType_Num-@taocannum)*UnitPrice*DisRatio*0.01 
           where BusinessDetail_ID=@BusinessDetail_ID andRecipe_Name=@taocan_recipename
           SET @taocannum=0
          end     
         else if @PayType_Num=@taocannum
          begin
           set @taocannum=@taocannum-@PayType_Num
           delete from #TaoCanLuoDan where BusinessDetail_ID=@BusinessDetail_ID andRecipe_Name=@taocan_recipename
          end
        fetch next from PayType_Cursor into @PayType_Name ,@PayType_Num,@BusinessDetail_ID
        end
       close PayType_Cursor
       deallocate PayType_Cursor 
      end
    fetch next from TaoCan_LuoDan_Cursor 
    into @taocannum,@taocan_recipename
    
   end
  close TaoCan_LuoDan_Cursor
  deallocate TaoCan_LuoDan_Cursor 
-----游标

---表值函数
Create Function f_GetAllSubNodeForNode(@DepartmentID int) returns @t_level table(DepartmentID int , level int)
as
begin
  declare @level int
  set @level = 1
  insert into @t_level select @DepartmentID , @level
  while @@ROWCOUNT > 0
  begin
    set @level = @level + 1
    insert into @t_level select a.DepartmentID , @level
    from PSY_U_Department as a , @t_Level b
    where a.HigherDepartmentID = b.DepartmentID and b.level = @level - 1
  end
  return
end

---表值函数
Create  Function f_GetUnionStr(@file varchar(32))
returns  varchar(1000)
----按某字段合并字符串
----@file是条件（可以是单条件也可以是复合条件）,最后将table1 改为具体的表或视图就可以了
as
 begin
  declare @returnstr varchar(1000)
   select @returnstr=IsNull(@returnstr+',','')+convert(varchar(128),value) from table1 wherefiles=@file
   return @returnstr
 end

 

--显示高级配置选项
exec sp_configure 'show advanced options',1
reconfigure
go
exec sp_configure ---显示所有高级配置选项
SELECT * FROM sys.configurations 
go
--启用 xp_cmdshell 
exec sp_configure 'xp_cmdshell',1
reconfigure 
--禁用 xp_cmdshell 
exec sp_configure 'xp_cmdshell',0
reconfigure 
exec sp_configure 'show advanced options',0
reconfigure
go
---拷贝（移动）文件
exec xp_cmdshell 'copy D:\g_goods_0723.bak 
\\zhaowenzhong\test\g_goods_0723.bak, no_output';
go
exec  xp_readerrorlog ----读取日志

--返回可执行文件列表
EXEC master..xp_cmdshell 'dir *.exe'

 

--删除文件
exec   master..xp_cmdshell 'del F:\test_cmd_shell\复件to_all.xls '

/*  SQL2000版本支持，但是从2005以后不支持删除扩展存储过程（所有，不仅仅是xp_cmdshell),可以将其禁用掉。
--去掉扩展存储过程xp_cmdshell   
use   master   
EXEC sp_dropextendedproc 'xp_cmdshell '  

--恢复扩展存储过程xp_cmdshell      
EXEC sp_addextendedproc 'xp_cmdshell ',   'xpsql70.dll '

*/

 

/*名称相同，数据不同，只显示一个名称。（即隐藏或不显示重复名称）*/

create table tb(name varchar(120), qty int)
insert into tb values('A', 100)
insert into tb values('A', 200)
insert into tb values('C', 200)
insert into tb values('d', 100)
insert into tb values('A', 500)
insert into tb values('C', 200)
go

select (case when qty = (select top 1 qty from tb where name = t.name) then name else '' end) name, qty
from (select top 1000 percent * from tb order by name , qty) t

--或者
 
select name=(case  when  rownumber=1 then  name else ''end  ),qty
  from (select *,rownumber=ROW_NUMBER()over( partition by name order by getdate()) from tb) t

 

--1个简单的统计每天系统访问次数及明细
create table [user](username varchar(10),taskid int)
insert into [user]
select '张三',1 union
select '王二',2 union
select '张三',3 union
select '李四',4
create table task(taskid int,loginTime datetime)
insert into task
select 1,'2012-12-23 11:30:00' union
select 2,'2012-12-23 18:30:00' union
select 3,'2012-12-25 11:30:00' union
select 4,'2012-12-23 11:00:00'

declare @bdate datetime=(select min(loginTime) from task),
        @edate datetime=(select MAX(loginTime) from task)
;with cte1 as
(
  select 日期=DATEADD(day,number,@bdate) from master.dbo.spt_values
     where type='p' and number<=DATEDIFF(DAY,@bdate,@edate)
 )
 ,cte2 as
 (
   select a.username,b.loginTime from [user] a,task b where a.taskid=b.taskid
 )
 
select 日期=convert(varchar(10),日期,120),
       人数=(select COUNT(distinct username) from cte2 where DATEDIFF(DAY,a.日期,loginTime)=0),
       登入人=isnull(stuff((select distinct ','+username from cte2 
             where DATEDIFF(DAY,a.日期,loginTime)=0 for xml path('')),1,1,''),'无')
from cte1 a    

 --消除行中 重复数据

create table t1(col1 varchar(10),col2 varchar(8000))
insert t1
select 'A' , '美国,法国,英国,美国,日本,韩国,日本' union all
select 'B' , '朝鲜,缅甸,缅甸'
go
;with cte as
(
select col1,
col2=SUBSTRING(case when right(col2,1)=',' then col2 else col2+',' end,1,charindex(',',case when right(col2,1)=',' then col2 else col2+',' end)-1),
col3=stuff(case when right(col2,1)=',' then col2 else col2+',' end,1,charindex(',',case when right(col2,1)=',' then col2 else col2+',' end),'')
 from t1
union all
select col1,col2=SUBSTRING(col3,1,charindex(',',col3)-1),col3=stuff(col3,1,charindex(',',col3),'') from cte
where CHARINDEX(',',col3)>0
)
select distinct col1,col2 from cte
order by col1