萬盛學電腦網

 萬盛學電腦網 >> 數據庫 >> mssql數據庫 >> 有用的SQL語句(刪除重復記錄,收縮日志)

有用的SQL語句(刪除重復記錄,收縮日志)

  刪除重復記錄,將TABLE_NAME中的不重復記錄保存到#TABLE_NAME中

  select distinct * into #table_name from table_name

  delete from table_name

  select * into table_name from #table_name

  drop table #table_name

  與此相關的是“select into”選項,可以在數據庫屬性

  對話框中,勾起來此項,或者在Query Analyzer中執行

  execute sp_dboption 'db_name','select into','true'

  開啟。默認值是關閉的。

  *******************************************************

  收縮事務日志(多次執行)

  backup log register with NO_LOG

  backup log register with TRUNCATE_ONLY

  DBCC SHRINKDATABASE(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 table1

  select 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 float

  set @s = 0.1566134

  print 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 one

  delete from A where userid not in(select min(userid) as userid from A group by username ,userpwd)

  --method two

  delete from A where exists (select * from A b where a.username = b.username and a.userpwd = b.userpwd and a.userid < b.userid)

  --method three

  delete 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 A

  drop table A

  ------------刪除數據庫中的重復記錄(且僅保留一條有效記錄)示例-----------------

  -------------------------------迭歸的應用(找起點和終點之間的路徑-----------------------------

  create table t

  (st varchar(20),ed varchar(20),km int)

  go

  insert 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 t

  go

  --創建函數

  --函數返回一個表,根據實際情況的不同一層一層的插入,可以充分利用生成的表

  create function f_go(@col varchar(10))

  returns @t table(col varchar(30),st varchar(20),ed varchar(20),km int,level int)

  as

  begin

  declare @i int

  set @i=1

  insert @t select st+'-'+ed,*,@i from t where st=@col

  while exists (select * from t a,@t b where

  b.ed=a.st and b.level=@i and b.ed<>@col )

  begin

  set @i=@i+1

  insert @t

  select b.col+'-'+a.ed,a.st,a.ed,b.km+a.km,@i from t a,@t b

  where b.level=@i-1 and b.ed=a.st and b.ed<>@col

  end

  return

  end

  go

  --調用

  --select * from dbo.f_go('A')

  select col,km from dbo.f_go('a')

  --刪除環境

  drop function f_go

  drop table t

  -------------------------------迭歸的應用(找起點和終點之間的路徑-----------------------------

  --------按類別去最新的前N條記錄,把同一類的放在一起,統計同一類的項的個數等-------------

  create table t

  (

  ClassName varchar(50),

  ClassCode varchar(10),

  ClassID int identity(1,1)

  )

  insert into t

  select 'cccc1','002' union all

  select 'aaaa','001' union all

  select 'bbbb','001' union all

  select 'aaaa1','002' union all

  select 'cccc','001' union all

  select 'dddd','001' union all

  select 'bbbb1','002' union all

  select 'dddd1','002'

  select * from t

  select ClassCode = (case when exists(select 1 from t t1 where classCode = t1.ClassCode

  and ClassID < t1.ClassID)

  then '' else ClassCode end),ClassName from t order by ClassCode,ClassID desc

  select count(*),classCode from (select top 100 percent ClassCode = (case when exists(select 1 from t t1 where classCode = t1.ClassCode

  and ClassID < t1.ClassID)

  then '' else ClassCode end),ClassName from t order by ClassCode,ClassID desc)a group by classcode

  select classCode,className from t order by classCode,classID desc

  drop table t

  --------按類別去最新的前N條記錄,把同一類的放在一起,統計同一類的項的個數等-------------

  -------------同上,按類別進行統計,把同一類的項的其他內容進行相加並發在一個字段中------------------

  create table tb(ProductID varchar(10),PositionID varchar(10))

  insert into tb

  select '10001','A1'

  union all select '10001','B2'

  union all select '10002','C3'

  union all select '10002','D4'

  union all select '10002','E5'

  go

  create function dbo.fc_str(@ProductID varchar(10))

  returns varchar(100)

  as

  begin

  declare @sql varchar(1000)

  set @sql=''

  select @sql=@sql+','+cast(PositionID as varchar(20)) from tb where ProductID=@ProductID

  return stuff(@sql,1,1,'')

  end

  go

  select ProductID,dbo.fc_str(ProductID) as PositionID from tb group by ProductID

  drop table tb

  drop function dbo.fc_str

copyright © 萬盛學電腦網 all rights reserved