国产理论最新国产精品视频,波多野吉衣HD蘑菇影视无限

主頁 > 知識庫 > 有用的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

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

--取各個類的前n條記錄(每個類都取top n條)
--如果有數據庫中有多個類,現在要取每個類的前n條記錄,可用以下語句
Create Table TEST
(ID Int Identity(1,1),
h_id Int)
Insert TEST Select 100
Union All Select 100
Union All Select 100
Union All Select 101
Union All Select 101
Union All Select 101
Union All Select 100
GO
--方法一：
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 IDA.ID Having Count(*)>2)
--方法三：
Select * From TEST A Where (Select Count(*) From TEST Where h_id=A.h_id And IDA.ID)3
GO
Drop Table TEST
GO

--分組統計,統計每個段中數據的個數
--一般成績統計可以用到這個
declare @t table(id int,weight int)
insert into @t select 1, 20
insert into @t select 2, 15
insert into @t select 3, 5
insert into @t select 4, 60
insert into @t select 5, 12
insert into @t select 6, 33
insert into @t select 7, 45
insert into @t select 8, 59
insert into @t select 9, 89
insert into @t select 10,110

declare @p int
set @p=10
select
rtrim(p*@p)+'-'+rtrim((p+1)*@p">p*@p)+'-'+rtrim((p+1)*@p) as p,
num
from
(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, 20
insert into @t select 2, 15
insert into @t select 3, 5
insert into @t select 4, 60
insert into @t select 5, 12
insert into @t select 6, 33
insert into @t select 7, 45
insert into @t select 8, 59
insert into @t select 9, 89
insert 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語句實現刪除重復記錄并只保留一條
MySQL數據庫中刪除重復記錄的方法總結[推薦]
SqlServer2005中使用row_number()在一個查詢中刪除重復記錄的方法
SQL Server2008中刪除重復記錄的方法分享
sqlserver 刪除重復記錄處理（轉）
SqlServer 2005中使用row_number()在一個查詢中刪除重復記錄
mysql刪除重復記錄語句的方法
SQL語句實現刪除ACCESS重復記錄的兩種方法
SQL對冗余數據的刪除重復記錄只保留單條的說明
sql 刪除表中的重復記錄

標簽：邯鄲無錫西寧來賓七臺河營口寶雞汕尾

巨人網絡通訊聲明：本文標題《有用的SQL語句（刪除重復記錄，收縮日志）》，本文關鍵詞有,用的,SQL,語句,刪除,重復,；如發現本文內容存在版權問題，煩請提供相關信息告之我們，我們將及時溝通與處理。本站內容系統采集于網絡，涉及言論、版權與本站無關。

好湿?好紧?好多水好爽自慰,久久久噜久噜久久综合,成人做爰A片免费看黄冈,机机对机机30分钟无遮挡

有用的SQL語句（刪除重復記錄，收縮日志）