SQL Server 有用的SQL语句（删除重复记录，收缩日志）
2022-11-12 09:53:12
内容摘要
这篇文章主要为大家详细介绍了SQL Server 有用的SQL语句（删除重复记录，收缩日志），具有一定的参考价值，可以用来参考一下。
对此感兴趣的朋友，看看idc笔记做的技术笔记！删除重复记
文章正文
这篇文章主要为大家详细介绍了SQL Server 有用的SQL语句（删除重复记录，收缩日志），具有一定的参考价值，可以用来参考一下。
对此感兴趣的朋友，看看idc笔记做的技术笔记！
删除重复记录,将TABLE_NAME中的不重复记录保存到#TABLE_NAME中selectdistinct*into#table_namefromtable_namedeletefromtable_nameselect*intotable_namefrom#table_namedroptable#table_name与此相关的是“selectinto”选项，可以在数据库属性对话框中，勾起来此项，或者在QueryAnalyzer中执行executesp_dboption'db_name','selectinto','true'开启。默认值是关闭的。*******************************************************收缩事务日志(多次执行)backuplogregisterwithNO_LOGbackuplogregisterwithTRUNCATE_ONLYDBCCSHRINKDATABASE(register)更多有用的sql语句/*sql 语法学习*//*函数的学习－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－*/获取当前时间（时/分/秒）：select convert(varchar(10),getdate(),8)获取当前年月日：select convert(varchar(10),getdate(),120)获取当前年月：select convert(varchar(7),getdate(),120)获取当前年月：select convert(varchar(10),year(getdate())) + '-' + convert(varchar(10),month(getDate()))select cast(b as integer) as bb from table1 where b = '11'select a,case b when '11' then '细细' when '22' then '呵呵' else '哈哈' end as 转换,c from table1select a,b,case when c = '111' then '细细' when c = '222' then '呵呵' else '哈哈' end as 转换1 from table1获取当前时间：print current_timestamp/*－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－*/-----------------将sql查询输出到txt文本文件中-------------------------------------------EXEC master..xp_cmdshell 'bcp 数据库名.dbo.表名 out d:\1.txt -c -q -U"sa" -P"password"'------------------------------------------------------------------------------------------------------------------round的用法beigin------------------------------declare @s floatset @s = 0.1566134print round(@s,3)---------------------------round的用法end-----------------------------------------------------------------自动收缩数据库begin-----------------------------EXEC [master]..sp_dboption [Database Name], 'autoshrink', 'TRUE'--------------------------------自动收缩数据库end------------------------------------------------------------去除首尾无效的字符begin--------------------------declare @s varchar(20)set @s=',,,1->1,'while(left(@s,1)=',')set @s=stuff(@s,1,1,'')while(right(@s,1)=',')set @s=stuff(reverse(@s),1,1,'')select @s-------------------------------去除首尾无效的字符end--------------------------------------删除数据库中的重复记录（且仅保留一条有效记录）示例-----------------create table A(userID int identity(1,1),userName varchar(20),userPwd varchar(20),userEmail varchar(50))insert into A(userName,userpwd) select 'qin','qin' union all select 'qin','qin1' union all select 'qin','qin1'select * from A--method onedelete from A where userid not in(select min(userid) as userid from A group by username ,userpwd)--method twodelete from A where exists (select * from A b where a.username = b.username and a.userpwd = b.userpwd and a.userid < b.userid)--method threedelete from a where userid not in(select min(userid) from A b where a.username = b.username and a.userpwd = b.userpwd and a.userid > b.userID)select * from Adrop table A------------删除数据库中的重复记录（且仅保留一条有效记录）示例------------------------------------------------迭归的应用（找起点和终点之间的路径-----------------------------create table t(st varchar(20),ed varchar(20),km int)goinsert t values ('A','B',1000)insert t values ('A','C',1100)insert t values ('A','D',900)insert t values ('A','E',400)insert t values ('B','D',300)insert t values ('D','F',600)insert t values ('E','A',400)insert t values ('F','G',1000)insert t values ('C','B',600)go--显示插入值select * from tgo--创建函数--函数返回一个表，根据实际情况的不同一层一层的插入，可以充分利用生成的表create function f_go(@col varchar(10))returns @t table(col varchar(30),st varchar(20),ed varchar(20),km int,level int)asbegindeclare @i intset @i=1insert @t select st+'-'+ed,*,@i from t where st=@colwhile exists (select * from t a,@t b whereb.ed=a.st and b.level=@i and b.ed<>@col )beginset @i=@i+1insert @tselect b.col+'-'+a.ed,a.st,a.ed,b.km+a.km,@i from t a,@t bwhere b.level=@i-1 and b.ed=a.st and b.ed<>@colendreturnendgo--调用--select * from dbo.f_go('A')select col,km from dbo.f_go('a')--删除环境drop function f_godrop table t-------------------------------迭归的应用（找起点和终点之间的路径-------------------------------------按类别去最新的前N条记录,把同一类的放在一起,统计同一类的项的个数等-------------create table t(ClassName varchar(50),ClassCode varchar(10),ClassID int identity(1,1))insert into tselect 'cccc1','002' union allselect 'aaaa','001' union allselect 'bbbb','001' union allselect 'aaaa1','002' union allselect 'cccc','001' union allselect 'dddd','001' union allselect 'bbbb1','002' union allselect 'dddd1','002'select * from tselect ClassCode = (case when exists(select 1 from t t1 where classCode = t1.ClassCodeand ClassID < t1.ClassID)then '' else ClassCode end),ClassName from t order by ClassCode,ClassID descselect count(*),classCode from (select top 100 percent ClassCode = (case when exists(select 1 from t t1 where classCode = t1.ClassCodeand ClassID < t1.ClassID)then '' else ClassCode end),ClassName from t order by ClassCode,ClassID desc)a group by classcodeselect classCode,className from t order by classCode,classID descdrop table t--------按类别去最新的前N条记录,把同一类的放在一起,统计同一类的项的个数等--------------------------同上,按类别进行统计,把同一类的项的其他内容进行相加并发在一个字段中------------------create table tb(ProductID varchar(10),PositionID varchar(10))insert into tbselect '10001','A1'union all select '10001','B2'union all select '10002','C3'union all select '10002','D4'union all select '10002','E5'gocreate function dbo.fc_str(@ProductID varchar(10))returns varchar(100)asbegindeclare @sql varchar(1000)set @sql=''select @sql=@sql+','+cast(PositionID as varchar(20)) from tb where ProductID=@ProductIDreturn stuff(@sql,1,1,'')endgoselect ProductID,dbo.fc_str(ProductID) as PositionID from tb group by ProductIDdrop table tbdrop function dbo.fc_str-------------按类别进行统计,把同一类的项的其他内容进行相加并发在一个字段中--------------------取各个类的前n条记录(每个类都取top n条)--如果有数据库中有多个类,现在要取每个类的前n条记录,可用以下语句Create Table TEST(ID Int Identity(1,1),h_id Int)Insert TEST Select 100Union All Select 100Union All Select 100Union All Select 101Union All Select 101Union All Select 101Union All Select 100GO--方法一：Select * From TEST A Where Id In(Select TOP 3 ID From TEST Where h_id=A.h_id)--方法二：Select * From TEST A Where Not Exists (Select 1 From TEST Where h_id=A.h_id And ID<A.ID Having Count(*)>2)--方法三：Select * From TEST A Where (Select Count(*) From TEST Where h_id=A.h_id And ID<A.ID)<3GODrop Table TESTGO--分组统计,统计每个段中数据的个数--一般成绩统计可以用到这个declare @t table(id int,weight int)insert into @t select 1, 20insert into @t select 2, 15insert into @t select 3, 5insert into @t select 4, 60insert into @t select 5, 12insert into @t select 6, 33insert into @t select 7, 45insert into @t select 8, 59insert into @t select 9, 89insert into @t select 10,110declare @p intset @p=10selectrtrim(p*@p)+'-'+rtrim((p+1)*@p">p*@p)+'-'+rtrim((p+1)*@p) as p,numfrom(select (weight/@p">weight/@p) as p,count(*) as num from @t where weight between 10 and 100 group by (weight/@p">weight/@p)) a----------------------------在in语句中只用自定义排序begin--------------------------------declare @t table(id int,weight int)insert into @t select 1, 20insert into @t select 2, 15insert into @t select 3, 5insert into @t select 4, 60insert into @t select 5, 12insert into @t select 6, 33insert into @t select 7, 45insert into @t select 8, 59insert into @t select 9, 89insert into @t select 10,110--默认in语句中sql会按照id进行排序select * from @t where id in(2,4,3)--用此方法可以按照我们传入的id顺序进行显示数据select * from @t where id in(2,4,3) order by charindex(rtrim(id),',2,4,3,')----------------------------在in语句中只用自定义排序end--------------------------------注：关于SQL Server 有用的SQL语句（删除重复记录，收缩日志）的内容就先介绍到这里，更多相关文章的可以留意
代码注释
作者：喵哥笔记
