原文:
常用SQL收藏
--拆分字符串之后匹配结果集合CREATE FUNCTION [dbo].[fnSplit]( @sInputList VARCHAR(8000) -- List of delimited items , @sDelimiter VARCHAR(8000) = ‘,‘ -- delimiter that separates items) RETURNS @List TABLE (item VARCHAR(8000))BEGINDECLARE @sItem VARCHAR(8000)WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0 BEGIN SELECT @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))), @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList)))) IF LEN(@sItem) > 0 INSERT INTO @List SELECT @sItem ENDIF LEN(@sInputList) > 0 INSERT INTO @List SELECT @sInputList -- Put the last item inRETURNEND----------------------使用方法if object_id(‘tempdb..#Tmp‘) is not null drop table #Tmpcreate table #Tmp --创建临时表#Tmp( ID VARCHAR(100) );DECLARE @iid VARCHAR(100)declare @name varchar(500)declare cursor1 cursor for --定义游标cursor1select iid,props from Iteminfos --使用游标的对象open cursor1 --打开游标fetch next from cursor1 into @iid,@name --将游标向下移1行，获取的数据放入之前定义的变量@iid,@name中while @@fetch_status=0 --判断是否成功获取数据beginIF((select COUNT(*) FROM fnSplit(@name, ‘;‘) WHERE item = ‘20000:20090‘)>0)INSERT INTO #Tmp (ID) VALUES (@iid)fetch next from cursor1 into @iid,@name --将游标向下移1行endclose cursor1 --关闭游标deallocate cursor1SELECT * FROM dbo.Iteminfos WHERE iid IN( SELECT ID FROM #Tmp)
create table tb(id varchar(3) , pid varchar(3) , name varchar(10))insert into tb values(‘001‘ , null , ‘广东省‘)insert into tb values(‘002‘ , ‘001‘ , ‘广州市‘)insert into tb values(‘003‘ , ‘001‘ , ‘深圳市‘)insert into tb values(‘004‘ , ‘002‘ , ‘天河区‘)insert into tb values(‘005‘ , ‘003‘ , ‘罗湖区‘)insert into tb values(‘006‘ , ‘003‘ , ‘福田区‘)insert into tb values(‘007‘ , ‘003‘ , ‘宝安区‘)insert into tb values(‘008‘ , ‘007‘ , ‘西乡镇‘)insert into tb values(‘009‘ , ‘007‘ , ‘龙华镇‘)insert into tb values(‘010‘ , ‘007‘ , ‘松岗镇‘)go-------------创建方法create function f_cid(@ID varchar(3)) returns @t_level table(id varchar(3) , level int)asbegindeclare @level intset @level = 1insert into @t_level select @id , @levelwhile @@ROWCOUNT > 0beginset @level = @level + 1insert into @t_level select a.id , @levelfrom tb a , @t_Level bwhere a.pid = b.id and b.level = @level - 1endreturnENDGO--------------使用方法select a.* from tb a , f_cid(‘001‘) b where a.id = b.id order by a.id--------------调用函数查询(广州市)及其所有子节点select a.* from tb a , f_cid(‘002‘) b where a.id = b.id order by a.id
---两张关联表，删除主表中已经在副表中没有的信息 delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )---日程安排提前五分钟提醒 SQL: select * from 日程安排 where datediff(‘minute‘,f开始时间,getdate())>5---随机取出10条数据select top 10 * from tablename order by newid()-- 类似有 month day yearselect * from table1 where convert(varchar,date,120) like ‘2006-04-01%‘ --datediffselect * from table1 where datediff(day,time,‘2006-4-1‘)=0
--1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断 select * from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1) --2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录 delete from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1) and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1) --3、查找表中多余的重复记录（多个字段） select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) --4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录 delete from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1) --5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录 select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1) --经典尝试 删除重复值declare @table table (id int,name nvarchar(10))insert into @table select 1,‘aa‘ union all select 1,‘aa‘ union all select 2,‘bb‘ union all select 3,‘bb‘ union all select 4,‘cc‘ union all select 1,‘aa‘ union all select 4,‘cc‘delete afrom ( select id,name,rn = row_number() over (partition by id,name order by id) from @table ) a where rn > 1select * from @table id name----------- ----------1 aa2 bb3 bb4 cc(4 row(s) affected)
select CONVERT(varchar, getdate(), 120 )--结果2004-09-12 11:06:08select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),‘-‘,‘‘),‘ ‘,‘‘),‘:‘,‘‘)--结果20040912110608select CONVERT(varchar(12) , getdate(), 111 )--结果2004/09/12select CONVERT(varchar(12) , getdate(), 112 )--结果20040912select CONVERT(varchar(12) , getdate(), 102 )--结果2004.09.12
create table tb(姓名varchar(10) , 课程varchar(10) , 分数int)insert into tb values(‘张三‘ , ‘语文‘ , 74)insert into tb values(‘张三‘ , ‘数学‘ , 83)insert into tb values(‘张三‘ , ‘物理‘ , 93)insert into tb values(‘李四‘ , ‘语文‘ , 74)insert into tb values(‘李四‘ , ‘数学‘ , 84)insert into tb values(‘李四‘ , ‘物理‘ , 94)goselect 姓名 as 姓名,max(case 课程 when ‘语文‘ then 分数 else 0 end) 语文,max(case 课程 when ‘数学‘ then 分数 else 0 end) 数学,max(case 课程 when ‘物理‘ then 分数 else 0 end) 物理,cast(avg(分数*1.0) as decimal(18,2)) 平均分,sum(分数) 总分from tbgroup by 姓名--SQL SERVER 2000 动态SQL。declare @sql varchar(8000)set @sql = ‘select 姓名 ‘select @sql = @sql + ‘ , max(case 课程 when ‘‘‘ + 课程 + ‘‘‘ then 分数 else 0 end) [‘ + 课程 + ‘]‘from (select distinct 课程 from tb) as aset @sql = @sql + ‘ , cast(avg(分数*1.0) as decimal(18,2)) 平均分, sum(分数) 总分 from tb group by 姓名‘exec(@sql)--姓名 数学 物理 语文 平均分 总分--李四 84 94 74 84.00 252--张三 83 93 74 83.33 250
ALTER function [dbo].[f_cid](@id int)returns @t table(id int,[name] varchar(30),parentid int,lev int)asbegin declare @lev int set @lev=1 insert into @t SELECT cid,name,parent_cid,@lev from TB_ItemCats where cid=@id while(@@rowcount>0) begin set @lev=@lev+1 insert into @t select a.cid,a.name,a.parent_cid,@lev from TB_ItemCats a,@t b where a.cid=b.parentid and b.lev=@lev-1 AND a.cid NOT IN (select b.id from @t) end return END
3232
3232
3232
3232
3232
3232
3232
3232
3232
3232
3232
