-------------------------------去除首尾無效的字符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--------------------------
------------刪除數(shù)據(jù)庫中的重復記錄（且僅保留一條有效記錄）示例----------------- 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)
-------------------------------迭歸的應用（找起點和終點之間的路徑----------------------------- 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ù)返回一個表，根據(jù)實際情況的不同一層一層的插入，可以充分利用生成的表 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')
--------按類別去最新的前N條記錄,把同一類的放在一起,統(tǒng)計同一類的項的個數(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
-------------同上,按類別進行統(tǒng)計,把同一類的項的其他內(nèi)容進行相加并發(fā)在一個字段中------------------ 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
--取各個類的前n條記錄(每個類都取top n條) --如果有數(shù)據(jù)庫中有多個類,現(xià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
--分組統(tǒng)計,統(tǒng)計每個段中數(shù)據(jù)的個數(shù) --一般成績統(tǒng)計可以用到這個 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順序進行顯示數(shù)據(jù) select * from @t where id in(2,4,3) order by charindex(rtrim(id),',2,4,3,')
