常用sql 全记录（添加中）

不念不忘少年蓝@ 2022-09-26 13:59 197阅读 0赞

\-- 数据库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][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次

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][Recipe_Name_taocan_recipename])  
      Begin  
       declare PayType\_Cursor cursor for  
       select Recipe\_Name,Quantity,BusinessDetail\_ID from \#TaoCanLuoDan where [ Recipe\_Name=@taocan\_recipename][Recipe_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][BusinessDetail_ID_BusinessDetail_ID] and [ Recipe\_Name=@taocan\_recipename][Recipe_Name_taocan_recipename]  
          end  
         else if @PayType\_Num>@taocannum  
          begin  
           update \#TaoCanLuoDan set [ Quantity=@PayType\_Num-@taocannum][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][BusinessDetail_ID_BusinessDetail_ID] and[Recipe\_Name=@taocan\_recipename][Recipe_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][BusinessDetail_ID_BusinessDetail_ID] and [ Recipe\_Name=@taocan\_recipename][Recipe_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][----_file]是条件（可以是单条件也可以是复合条件）,最后将table1 改为具体的表或视图就可以了  
as  
 begin  
  declare @returnstr varchar(1000)  
   select @returnstr=IsNull(@returnstr+',','')+convert(varchar(128),value) from table1 where[files=@file][files_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

[CustomerCode_CustomerCode]: mailto:CustomerCode=@CustomerCode
[Recipe_Name_taocan_recipename]: mailto:Recipe_Name=@taocan_recipename
[BusinessDetail_ID_BusinessDetail_ID]: mailto:BusinessDetail_ID=@BusinessDetail_ID
[Quantity_PayType_Num-_taocannum]: mailto:Quantity=@PayType_Num-@taocannum
[----_file]: mailto:----@fileS
[files_file]: mailto:files=@file