刪除重復(fù)記錄,將TABLE_NAME中的不重復(fù)記錄保存到#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

與此相關(guān)的是“select into”選項(xiàng)，可以在數(shù)據(jù)庫(kù)屬性
對(duì)話框中，勾起來(lái)此項(xiàng)，或者在Query Analyzer中執(zhí)行
execute sp_dboption 'db_name','select into','true'
開(kāi)啟。默認(rèn)值是關(guān)閉的。


*******************************************************
收縮事務(wù)日志(多次執(zhí)行)

backup log register with NO_LOG
backup log register with TRUNCATE_ONLY
DBCC SHRINKDATABASE(register)

更多有用的sql語(yǔ)句
/*sql 語(yǔ)法學(xué)習(xí)*/

/*函數(shù)的學(xué)習(xí)－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－*/

獲取當(dāng)前時(shí)間（時(shí)/分/秒）：select convert(varchar(10),getdate(),8)
獲取當(dāng)前年月日：select convert(varchar(10),getdate(),120)
獲取當(dāng)前年月：select convert(varchar(7),getdate(),120)
獲取當(dāng)前年月：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 '細(xì)細(xì)' when '22' then '呵呵' else '哈哈' end as 轉(zhuǎn)換,c from table1

select a,b,case when c = '111' then '細(xì)細(xì)' when c = '222' then '呵呵' else '哈哈' end as 轉(zhuǎn)換1 from table1

獲取當(dāng)前時(shí)間：print current_timestamp

/*－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－*/

-----------------將sql查詢(xún)輸出到txt文本文件中-------------------------------------------
EXEC master..xp_cmdshell 'bcp 數(shù)據(jù)庫(kù)名.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---------------------------------

--------------------------------自動(dòng)收縮數(shù)據(jù)庫(kù)begin-----------------------------

EXEC [master]..sp_dboption [Database Name], 'autoshrink', 'TRUE'

--------------------------------自動(dòng)收縮數(shù)據(jù)庫(kù)end-----------------------------


-------------------------------去除首尾無(wú)效的字符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
-------------------------------去除首尾無(wú)效的字符end--------------------------


------------刪除數(shù)據(jù)庫(kù)中的重復(fù)記錄（且僅保留一條有效記錄）示例-----------------
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

------------刪除數(shù)據(jù)庫(kù)中的重復(fù)記錄（且僅保留一條有效記錄）示例-----------------



-------------------------------迭歸的應(yīng)用（找起點(diǎn)和終點(diǎn)之間的路徑-----------------------------
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

--創(chuàng)建函數(shù)
--函數(shù)返回一個(gè)表，根據(jù)實(shí)際情況的不同一層一層的插入，可以充分利用生成的表
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

--調(diào)用
--select * from dbo.f_go('A')
select col,km from dbo.f_go('a')

--刪除環(huán)境
drop function f_go
drop table t

-------------------------------迭歸的應(yīng)用（找起點(diǎn)和終點(diǎn)之間的路徑-----------------------------



--------按類(lèi)別去最新的前N條記錄,把同一類(lèi)的放在一起,統(tǒng)計(jì)同一類(lèi)的項(xiàng)的個(gè)數(shù)等-------------
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

--------按類(lèi)別去最新的前N條記錄,把同一類(lèi)的放在一起,統(tǒng)計(jì)同一類(lèi)的項(xiàng)的個(gè)數(shù)等-------------


-------------同上,按類(lèi)別進(jìn)行統(tǒng)計(jì),把同一類(lèi)的項(xiàng)的其他內(nèi)容進(jìn)行相加并發(fā)在一個(gè)字段中------------------
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

-------------按類(lèi)別進(jìn)行統(tǒng)計(jì),把同一類(lèi)的項(xiàng)的其他內(nèi)容進(jìn)行相加并發(fā)在一個(gè)字段中------------------



--取各個(gè)類(lèi)的前n條記錄(每個(gè)類(lèi)都取top n條)
--如果有數(shù)據(jù)庫(kù)中有多個(gè)類(lèi),現(xiàn)在要取每個(gè)類(lèi)的前n條記錄,可用以下語(yǔ)句
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 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)<3
GO
Drop Table TEST
GO


--分組統(tǒng)計(jì),統(tǒng)計(jì)每個(gè)段中數(shù)據(jù)的個(gè)數(shù)
--一般成績(jī)統(tǒng)計(jì)可以用到這個(gè)
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語(yǔ)句中只用自定義排序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
--默認(rèn)in語(yǔ)句中sql會(huì)按照id進(jìn)行排序
select * from @t where id in(2,4,3)
--用此方法可以按照我們傳入的id順序進(jìn)行顯示數(shù)據(jù)
select * from @t where id in(2,4,3) order by charindex(rtrim(id),',2,4,3,')

----------------------------在in語(yǔ)句中只用自定義排序end--------------------------------

最新評(píng)論