爱华网--查询表的默认值if object_id('tb') is not nulldrop table tbgocreate table tb(id int,name varchar(50) default 'abc',num intdefault 5)insert into tb(id) select 1insert into tb select 1,'oo',100insert into tb(id,name) select 1,'oo'godeclare @tbname varchar(50)set @tbname='tb'--表名select @tbname as tbname,c.name ascolname,replace(replace(replace(replace(b.[text],'(''',''),''')',''),'((',''),'))','')as defaultvaluefrom sysconstraints a join syscomments b on a.constid=b.idjoin syscolumns c on a.id=c.id and a.colid=c.colidwhere a.id=object_id(@tbname) and object_name(a.constid) like'�%'
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
模糊表名的联合查询..reate table tz2008_1_1(id int,name varchar(50))insert into tz2008_1_1 select 1,'a'create table tz2008_1_2(id int,name varchar(50))insert into tz2008_1_2 select 2,'b'create table tz2008_1_3(id int,name varchar(50))insert into tz2008_1_3 select 3,'c'
declare @sql varchar(8000)select @sql=isnull(@sql+' union all ','')+' select * from['+name+']'from sysobjects where xtype='u' and name like 'tz2008%'exec(@sql)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
--存储过程语句查询if object_id('proc_ttt') is not nulldrop proc proc_tttgocreate proc proc_tttasselect 1 union select 2go
select [text] from syscommentswhere id=object_id('proc_ttt')
EXEC SP_HELPTEXT 'proc_ttt'
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
--通过身份证获得户籍create function f_getcityfromcid (@cid varchar(18))returns varchar(50) as begin declare@acity varchar(1000) set@acity ='____,____,____,____,____,____,____,____,____,____,____,北京__,天津__,河北__,山西__,内蒙古_,____,____,____,____,____,辽宁__,吉林__,黑龙江_,____,____,____,____,____,____,____,上海__,江苏__,浙江__,安微__,福建__,江西__,山东__,____,____,____,河南__,湖北__,湖南__,广东__,广西__,海南__,____,____,____,重庆__,四川__,贵州__,云南__,西藏__,____,____,____,____,____,____,陕西__,甘肃__,青海__,宁夏__,新疆__,____,____,____,____,____,台湾__,____,____,____,____,____,____,____,____,____,香港__,澳门__,____,____,____,____,____,____,____,____,国外__,' set@cid = upper(@cid) IF(len(@cid) <> 18 ORpatindex('%[^0-9X]%',@cid) > 0) RETURN '你小子骗我，这不是合法的身份证' IFsubstring(@acity,cast(left(@cid,2) as int)* 5+1,4) = '' RETURN '你小子骗我，这身份证的地区码不存在' RETURN'这小子是：'+replace(substring(@acity,cast(left(@cid,2) as int)*5+1,4),'_','') end go select dbo.f_getcityfromcid('32108519760502ttt9')select dbo.f_getcityfromcid('32108519****026**9')drop function f_getcityfromcid
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
--随机选择一个小于等于500的组合declare @tb table(id int,num int)insert into @tb select 1,1000insert into @tb select 2,100insert into @tb select 3,500insert into @tb select 4,200insert into @tb select 5,200insert into @tb select 6,50insert into @tb select 7,150insert into @tb select 8,80insert into @tb select 9,70
declare @idtb table(id int)declare @num int,@id int,@sum intset @sum=0while @sum<>500beginselect top 1 @id=id,@num=num from @tb wherenum<=500 order by newid()if @num=500insert into @idtb select @idelseif not exists(select 1 from @idtb whereid=@id)insert into @idtb select @idselect @sum=sum(num) from @tb where id in(select id from@idtb)if(@sum>500)begindelete @idtbendend
select * from @tb where id in(select id from @idtb)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
普通行列转换(version 2.0)
问题：假设有张学生成绩表(tb)如下:姓名 课程 分数张三 语文 74张三 数学 83张三 物理 93李四 语文 74李四 数学 84李四 物理 94想变成(得到如下结果)：姓名 语文 数学 物理---- ---- ---- ----李四 74 84 94张三 74 83 93-------------------*/
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)go
--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)select 姓名 as 姓名 ,max(case 课程 when '语文' then分数 else 0 end) 语文,max(case 课程 when '数学' then分数 else 0 end) 数学,max(case 课程 when '物理' then分数 else 0 end) 物理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 + ' from tb group by 姓名'exec(@sql)
--SQL SERVER 2005 静态SQL。select * from (select * from tb) a pivot (max(分数) for 课程 in(语文,数学,物理)) b
--SQL SERVER 2005 动态SQL。declare @sql varchar(8000)select @sql = isnull(@sql + '],[' , '') + 课程 from tb group by课程set @sql = '[' + @sql + ']'exec ('select * from (select * from tb) a pivot (max(分数) for课程 in (' + @sql + ')) b')
---------------------------------
--SQL SERVER 2000 静态SQL。select 姓名 姓名,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) asdecimal(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)
--SQL SERVER 2005 静态SQL。select m.* , n.平均分 , n.总分 from(select * from (select * from tb) a pivot (max(分数) for 课程 in(语文,数学,物理)) b) m,(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数)总分 from tb group by 姓名) nwhere m.姓名 = n.姓名
--SQL SERVER 2005 动态SQL。declare @sql varchar(8000)select @sql = isnull(@sql + ',' , '') + 课程 from tb group by课程exec ('select m.* , n.平均分 , n.总分 from(select * from (select * from tb) a pivot (max(分数) for 课程 in(' + @sql + ')) b) m ,(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数)总分 from tb group by 姓名) nwhere m.姓名 = n.姓名')
drop table tb
------------------------------------
create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理int)insert into tb values('张三',74,83,93)insert into tb values('李四',74,84,94)go
--SQL SERVER 2000 静态SQL。select * from(select 姓名 , 课程 = '语文' , 分数 = 语文 fromtbunion allselect 姓名 , 课程 = '数学' , 分数 = 数学 fromtbunion allselect 姓名 , 课程 = '物理' , 分数 = 物理 fromtb) torder by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when'物理' then 3 end
--SQL SERVER 2000 动态SQL。--调用系统表动态生态。declare @sql varchar(8000)select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名, [课程] = ' + quotename(Name , '''') + ' , [分数] = ' +quotename(Name) + ' from tb'from syscolumnswhere name! = N'姓名' and ID = object_id('tb')--表名tb，不包含列名为姓名的其它列order by colid ascexec(@sql + ' order by 姓名 ')
--SQL SERVER 2005 动态SQL。select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学], [物理])) t
--SQL SERVER 2005 动态SQL，同SQL SERVER 2000 动态SQL。
--------------------
select * from(select 姓名 as 姓名 , 课程 = '语文' , 分数 = 语文 fromtbunion allselect 姓名 as 姓名 , 课程 = '数学' , 分数 = 数学 fromtbunion allselect 姓名 as 姓名 , 课程 = '物理' , 分数 = 物理 fromtbunion allselect 姓名 as 姓名 , 课程 = '平均分' , 分数 = cast((语文+ 数学 + 物理)*1.0/3 as decimal(18,2)) from tbunion allselect 姓名 as 姓名 , 课程 = '总分' , 分数 = 语文 + 数学 +物理 from tb) torder by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when'物理' then 3 when '平均分' then 4 when '总分' then 5 end
drop table tb
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
--按某一字段分组取最大(小)值所在行的数据
name val memoa 2 a2(a的第二个值)a 1 a1--a的第一个值a 3 a3:a的第三个值b 1 b1--b的第一个值b 3 b3:b的第三个值b 2 b2b2b2b2b 4 b4b4b 5 b5b5b5b5b5*/--创建表并插入数据：create table tb(name varchar(10),val int,memovarchar(20))insert into tb values('a', 2, 'a2(a的第二个值)')insert into tb values('a', 1, 'a1--a的第一个值')insert into tb values('a', 3, 'a3:a的第三个值')insert into tb values('b', 1, 'b1--b的第一个值')insert into tb values('b', 3, 'b3:b的第三个值')insert into tb values('b', 2, 'b2b2b2b2')insert into tb values('b', 4, 'b4b4')insert into tb values('b', 5, 'b5b5b5b5b5')go
--一、按name分组取val最大的值所在行的数据。--方法1：select a.* from tb a where val = (select max(val) from tbwhere name = a.name) order by a.name--方法2：select a.* from tb a where not exists(select 1 from tb wherename = a.name and val > a.val)--方法3：select a.* from tb a,(select name,max(val) val from tb groupby name) b where a.name = b.name and a.val = b.val order bya.name--方法4：select a.* from tb a inner join (select name , max(val) valfrom tb group by name) b on a.name = b.name and a.val = b.val orderby a.name--方法5select a.* from tb a where 1 > (select count(*)from tb where name = a.name and val > a.val ) orderby a.name
--二、按name分组取val最小的值所在行的数据。--方法1：select a.* from tb a where val = (select min(val) from tbwhere name = a.name) order by a.name--方法2：select a.* from tb a where not exists(select 1 from tb wherename = a.name and val < a.val)--方法3：select a.* from tb a,(select name,min(val) val from tb groupby name) b where a.name = b.name and a.val = b.val order bya.name--方法4：select a.* from tb a inner join (select name , min(val) valfrom tb group by name) b on a.name = b.name and a.val = b.val orderby a.name--方法5select a.* from tb a where 1 > (select count(*)from tb where name = a.name and val < a.val) orderby a.name
--三、按name分组取第一次出现的行所在的数据。select a.* from tb a where val = (select top 1 val from tbwhere name = a.name) order by a.name
--四、按name分组随机取一条数据。select a.* from tb a where val = (select top 1 val from tbwhere name = a.name order by newid()) order by a.name
--五、按name分组取最小的两个(N个)valselect a.* from tb a where 2 > (select count(*)from tb where name = a.name and val < a.val ) orderby a.name,a.valselect a.* from tb a where val in (select top 2 val from tbwhere name=a.name order by val) order by a.name,a.valselect a.* from tb a where exists (select count(*) from tbwhere name = a.name and val < a.val having Count(*)< 2) order by a.name
--六、按name分组取最大的两个(N个)valselect a.* from tb a where 2 > (select count(*)from tb where name = a.name and val > a.val ) orderby a.name,a.valselect a.* from tb a where val in (select top 2 val from tbwhere name=a.name order by val desc) order by a.name,a.valselect a.* from tb a where exists (select count(*) from tbwhere name = a.name and val > a.val having Count(*)< 2) order by a.name--七，如果整行数据有重复，所有的列都相同。--在sql server2000中只能用一个临时表来解决，生成一个自增列，先对val取最大或最小，然后再通过自增列来取数据。--创建表并插入数据：create table tb(name varchar(10),val int,memovarchar(20))insert into tb values('a', 2, 'a2(a的第二个值)')insert into tb values('a', 1, 'a1--a的第一个值')insert into tb values('a', 1, 'a1--a的第一个值')insert into tb values('a', 3, 'a3:a的第三个值')insert into tb values('a', 3, 'a3:a的第三个值')insert into tb values('b', 1, 'b1--b的第一个值')insert into tb values('b', 3, 'b3:b的第三个值')insert into tb values('b', 2, 'b2b2b2b2')insert into tb values('b', 4, 'b4b4')insert into tb values('b', 5, 'b5b5b5b5b5')go
select * , px = identity(int,1,1) into tmp from tb
select m.name,m.val,m.memo from(select t.* from tmp twhere val = (select min(val) from tmp where name = t.name)) m where px = (select min(px) from(select t.* from tmp twhere val = (select min(val) from tmp where name = t.name)) n where n.name = m.name)
drop table tb,tmp
--在sql server 2005中可以使用row_number函数，不需要使用临时表。--创建表并插入数据：create table tb(name varchar(10),val int,memovarchar(20))insert into tb values('a', 2, 'a2(a的第二个值)')insert into tb values('a', 1, 'a1--a的第一个值')insert into tb values('a', 1, 'a1--a的第一个值')insert into tb values('a', 3, 'a3:a的第三个值')insert into tb values('a', 3, 'a3:a的第三个值')insert into tb values('b', 1, 'b1--b的第一个值')insert into tb values('b', 3, 'b3:b的第三个值')insert into tb values('b', 2, 'b2b2b2b2')insert into tb values('b', 4, 'b4b4')insert into tb values('b', 5, 'b5b5b5b5b5')go
select m.name,m.val,m.memo from(select * , px =row_number() over(order by name , val) from tb) m where px = (select min(px) from(select * , px =row_number() over(order by name , val) from tb) n where n.name = m.name)
drop table tb
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
合并列值
表结构，数据如下：id value----- ------1 aa1 bb2 aaa2 bbb2 ccc
需要得到结果：id values------ -----------1 aa,bb2 aaa,bbb,ccc即：group by id, 求 value 的和（字符串相加）
1. 旧的解决方法(在sql server 2000中只能用函数解决。)--1. 创建处理函数create table tb(id int, value varchar(10))insert into tb values(1, 'aa')insert into tb values(1, 'bb')insert into tb values(2, 'aaa')insert into tb values(2, 'bbb')insert into tb values(2, 'ccc')go
CREATE FUNCTION dbo.f_str(@id int)RETURNS varchar(8000)ASBEGINDECLARE @rvarchar(8000)SET @r = ''SELECT @r = @r + ',' +value FROM tb WHERE id=@idRETURN STUFF(@r, 1, 1,'')ENDGO
-- 调用函数SELECt id, value = dbo.f_str(id) FROM tb GROUP BY id
drop table tbdrop function dbo.f_str
--2、另外一种函数.create table tb(id int, value varchar(10))insert into tb values(1, 'aa')insert into tb values(1, 'bb')insert into tb values(2, 'aaa')insert into tb values(2, 'bbb')insert into tb values(2, 'ccc')go
--创建一个合并的函数create function f_hb(@id int)returns varchar(8000)asbegindeclare @strvarchar(8000)set @str = ''select @str = @str + ',' +cast(value as varchar) from tb where id = @idset @str = right(@str ,len(@str) - 1)return(@str)Endgo
--调用自定义函数得到结果：select distinct id ,dbo.f_hb(id) as value from tb
drop table tbdrop function dbo.f_hb
2. 新的解决方法(在sql server 2005中用OUTER APPLY等解决。)create table tb(id int, value varchar(10))insert into tb values(1, 'aa')insert into tb values(1, 'bb')insert into tb values(2, 'aaa')insert into tb values(2, 'bbb')insert into tb values(2, 'ccc')go-- 查询处理SELECT * FROM(SELECT DISTINCT id FROM tb)A OUTER APPLY(SELECT [values]=STUFF(REPLACE(REPLACE((SELECT value FROM tbNWHERE id = A.idFOR XML AUTO), '<Nvalue="', ','), '"/>', ''), 1, 1, ''))Ndrop table tb
--SQL2005中的方法2create table tb(id int, value varchar(10))insert into tb values(1, 'aa')insert into tb values(1, 'bb')insert into tb values(2, 'aaa')insert into tb values(2, 'bbb')insert into tb values(2, 'ccc')go
select id, [values]=stuff((select ','+[value] from tb t whereid=tb.id for xml path('')), 1, 1, '')from tbgroup by id
drop table tb
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
分拆列值
有表tb, 如下:id value----------- -----------1 aa,bb2 aaa,bbb,ccc欲按id,分拆value列, 分拆后结果如下:id value----------- --------1 aa1 bb2 aaa2 bbb2 ccc
1. 旧的解决方法(sql server 2000)SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROMsyscolumns a, syscolumns b
SELECT A.id, SUBSTRING(A.[values], B.id, CHARINDEX(',',A.[values] + ',', B.id) - B.id)FROM tb A, # BWHERE SUBSTRING(',' + A.[values], B.id, 1) = ','
DROP TABLE #
2. 新的解决方法(sql server 2005)
create table tb(id int,value varchar(30))insert into tb values(1,'aa,bb')insert into tb values(2,'aaa,bbb,ccc')goSELECT A.id, B.valueFROM(SELECT id, [value] =CONVERT(xml,'<root><v>'+ REPLACE([value], ',','</v><v>')+'</v></root>')FROM tb)AOUTER APPLY(SELECT value =N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/root/v')N(v))B
DROP TABLE tb
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
DECLARE @dt datetimeSET @dt=GETDATE()
DECLARE @number intSET @number=3
--1．指定日期该年的第一天或最后一天--A. 年的第一天SELECT CONVERT(char(5),@dt,120)+ '1-1'
--B. 年的最后一天SELECT CONVERT(char(5),@dt,120)+ '12-31'
--2．指定日期所在季度的第一天或最后一天--A. 季度的第一天SELECT CONVERT(datetime,CONVERT(char(8),DATEADD(Month,DATEPART(Quarter,@dt)*3-Month(@dt)-2,@dt),120)+ '1 ')
--B. 季度的最后一天（CASE判断法）SELECT CONVERT(datetime,CONVERT(char(8),DATEADD(Month,DATEPART(Quarter,@dt)*3-Month(@dt),@dt),120)+CASE WHEN DATEPART(Quarter,@dt)in(1,4)THEN '31 'ELSE '30 'END)
--C. 季度的最后一天（直接推算法）SELECT DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1+DATEPART(Quarter,@dt)*3-Month(@dt),@dt),120)+ '1 ')
--3．指定日期所在月份的第一天或最后一天--A. 月的第一天SELECT CONVERT(datetime,CONVERT(char(8),@dt,120)+ '1')
--B. 月的最后一天SELECTDATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1,@dt),120)+ '1')
--C. 月的最后一天（容易使用的错误方法）SELECTDATEADD(Month,1,DATEADD(Day,-DAY(@dt),@dt))
--4．指定日期所在周的任意一天SELECTDATEADD(Day,@number-DATEPART(Weekday,@dt),@dt)
--5．指定日期所在周的任意星期几--A. 星期天做为一周的第1天SELECTDATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-1)%7,@dt)
--B. 星期一做为一周的第1天SELECTDATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-2)%7-1,@dt)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
分解字符串并查询相关数据
日期的推算
问题：通过分解一个带某种符号分隔的字符串在数据库中查找相关数据。例如 @str = '1,2,3',查询下表得到记录1,4,5,6ID TypeID1 1,2,3,4,5,6,7,8,9,10,11,122 2,33 3,7,8,94 2,65 4,56 6,7*/-----------------------------create table tb (ID int , TypeIDvarchar(30))insert into tb values(1 ,'1,2,3,4,5,6,7,8,9,10,11,12')insert into tb values(2 , '2,3')insert into tb values(3 , '3,7,8,9')insert into tb values(4 , '2,6')insert into tb values(5 , '4,5')insert into tb values(6 , '6,7')go-------------------------------如果仅仅是一个,如@str = '1'.declare @str as varchar(30)set @str = '1'select * from tb where charindex(',' + @str + ',' , ',' +TypeID + ',') > 0select * from tb where ',' + TypeID + ',' like '%,' + @str +',%'
-------------------------------如果包含两个,如@str = '1,2'.declare @str as varchar(30)set @str = '1,2'select * from tb where charindex(',' + left(@str ,charindex(',' , @str) - 1) + ',' , ',' + typeid + ',')> 0 orcharindex(',' +substring(@str , charindex(',' , @str) + 1 , len(@str)) + ',' , ','+ typeid + ',') > 0select * from tb where ',' + typeid + ',' like '%,' +left(@str , charindex(',' , @str) - 1) + ',%'or',' + typeid + ',' like'%,' + substring(@str , charindex(',' , @str) + 1 , len(@str)) +',%'
---------------------------------------------如果包含三个或四个，用PARSENAME函数来处理.declare @str as varchar(30)set @str = '1,2,3,4'select * from tb wherecharindex(',' +parsename(replace(@str , ',' , '.') , 4) + ',' , ',' + typeid +',') > 0 orcharindex(',' +parsename(replace(@str , ',' , '.') , 3) + ',' , ',' + typeid +',') > 0 orcharindex(',' +parsename(replace(@str , ',' , '.') , 2) + ',' , ',' + typeid +',') > 0 orcharindex(',' +parsename(replace(@str , ',' , '.') , 1) + ',' , ',' + typeid +',') > 0select * from tb where',' + typeid + ',' like'%,' + parsename(replace(@str , ',' , '.') , 4) + ',%' or',' + typeid + ',' like'%,' + parsename(replace(@str , ',' , '.') , 3) + ',%' or',' + typeid + ',' like'%,' + parsename(replace(@str , ',' , '.') , 2) + ',%' or',' + typeid + ',' like'%,' + parsename(replace(@str , ',' , '.') , 1) + ',%'
-----------------------------------------如果超过四个，则只能使用函数或动态SQL来分解并查询数据。create function dbo.fn_split(@inputstr varchar(8000),@seprator varchar(10))returns @temp table (a varchar(200))asbegindeclare @i intset @inputstr =rtrim(ltrim(@inputstr))set @i =charindex(@seprator , @inputstr)while @i >=1begin insert@temp values(left(@inputstr , @i - 1)) set@inputstr = substring(@inputstr , @i + 1 , len(@inputstr) -@i) set @i= charindex(@seprator , @inputstr)endif @inputstr<> ''insert @tempvalues(@inputstr)returnendgo
--调用declare @str as varchar(30)set @str = '1,2,3,4,5'
select distinct m.* from tb m,(select * from dbo.fn_split(@str,',')) nwhere charindex(',' + n.a + ',' , ',' + m.typeid + ',')> 0
drop table tbdrop function dbo.fn_split
--------------------------------------------使用动态SQL的语句。declare @str varchar(200)declare @sql as varchar(1000)set @str = '1,2,3,4,5'set @sql = 'select ''' + replace(@str , ',' , ''' as id unionall select ''')set @sql = @sql + ''''set @sql = 'select distinct a.* from tb a , (' + @sql + ') bwhere charindex(' + ''','' + b.id + ' + ''',''' + ' , ' + ''','' +a.typeid + ' + ''',''' + ') > 0 'exec (@sql)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
----------------------------------------------------------------------------------SET NOCOUNT ONDECLARE @LogicalFileName sysname, --日志文件名@MaxMinutes INT, --允许此脚本执行的最长时间@NewSize INT --目标日志文件的大小
USE CRM --要操作的数据库名
SELECT @LogicalFileName = 'CRM_LOG', -- 日志文件名
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 1 -- 想要收缩到的目标大小(单位M),此处标记收缩到1M
DECLARE @OriginalSize int
SELECT @OriginalSize = sizeFROM sysfilesWHERE name = @LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'FROM sysfilesWHERE name = @LogicalFileName
CREATE TABLE DummyTrans(DummyColumn char (8000) notnull)
DECLARE @Counter INT,@StartTime DATETIME,@TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(),@TruncLog = 'BACKUP LOG ' + db_name() + ' WITHTRUNCATE_ONLY'
DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.WHILE @MaxMinutes > DATEDIFF (mi, @StartTime,GETDATE()) -- time has not expiredAND @OriginalSize = (SELECT size FROM sysfiles WHERE name=@LogicalFileName)AND (@OriginalSize * 8 /1024) > @NewSize
BEGIN -- Outer loop. SELECT@Counter = 0
WHILE((@Counter < @OriginalSize / 16) AND (@Counter< 50000)) BEGIN -- update INSERT DummyTrans VALUES ('Fill Log') DELETE DummyTrans SELECT @Counter = @Counter + 1 END
EXEC(@TruncLog)
END
SELECT 'Final Size of ' + db_name() + ' LOG is ' +CONVERT(VARCHAR(30),size) + ' 8K pages or ' +CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'FROM sysfilesWHERE name = @LogicalFileName
DROP TABLE DummyTrans
SET NOCOUNT OFF
----------------------------------------------------------------------------
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
--递归删除父节点及所有子节点
create table tb(Id int, ParentId int, Name varchar(5))insert into tb select 1, 0, 'a1'union all select 2,2, 'a2'union all select 14, 1, 'b11'union all select 15, 1, 'b12'union all select 16, 14, 'c13'union all select 17, 14, 'c14'union all select 104,17,'d15'goWITH temptab(id, parentid, name) AS ( SELECT root.id, root.parentid, root.name FROM tb root WHERE id=1 UNIONALL SELECT sub.id, sub.parentid,sub.name FROM tb sub, temptab super WHERE sub.parentid = super.id )delete from tb where id in(select id from temptab)select * from tbgodrop table tb/*Id ParentId Name----------- ----------- -----2 2 a2
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
精妙SQL语句
明：复制表(只复制结构,源表名：a 新表名：b)SQL: select * into b from a where 1<>1
说明：拷贝表(拷贝数据,源表名：a 目标表名：b)SQL: insert into b(a, b, c) select d,e,f fromb;
说明：显示文章、提交人和最后回复时间SQL: select a.title,a.username,b.adddate from table a,(selectmax(adddate) adddate from table where table.title=a.title)b
说明：外连接查询(表名1：a 表名2：b)SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOINb ON a.a = b.c
说明：日程安排提前五分钟提醒SQL: select * from 日程安排 where datediff(<|>minute<|>,f开始时间,getdate())>5
说明：两张关联表，删除主表中已经在副表中没有的信息SQL:delete from info where not exists ( select * from infobz whereinfo.infid=infobz.infid )
说明：--SQL:SELECT A.NUM, A.NAME, B.UPD_DATE,B.PREV_UPD_DATEFROM TABLE1,(SELECT X.NUM, X.UPD_DATE, Y.UPD_DATEPREV_UPD_DATEFROM (SELECT NUM, UPD_DATE, INBOUND_QTY,STOCK_ONHANDFROM TABLE2WHERE TO_CHAR(UPD_DATE,<|>YYYY/MM<|>) = TO_CHAR(SYSDATE,<|>YYYY/MM<|>)) X,(SELECT NUM, UPD_DATE, STOCK_ONHANDFROM TABLE2WHERE TO_CHAR(UPD_DATE,<|>YYYY/MM<|>) =TO_CHAR(TO_DATE(TO_CHAR(SYSDATE,<|>YYYY/MM<|>) ||<|>/01<|>,<|>YYYY/MM/DD<|>) - 1,<|>YYYY/MM<|>) ) Y,WHERE X.NUM = Y.NUM （+）AND X.INBOUND_QTY + NVL(Y.STOCK_ONHAND,0)<> X.STOCK_ONHAND )BWHERE A.NUM = B.NUM
说明：--SQL:select * from studentinfo where not exists(select * fromstudent where studentinfo.id=student.id) and 系名称=<|>"&strdepartmentname&"<|> and 专业名称=<|>"&strprofessionname&"<|> order by性别,生源地,高考总成绩
说明：从数据库中去一年的各单位电话费统计(电话费定额贺电化肥清单两个表来源）SQL:SELECT a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate,<|>yyyy<|>) AStelyear,SUM(decode(TO_CHAR(a.telfeedate,<|>mm<|>),<|>01<|>, a.factration)) ASJAN,SUM(decode(TO_CHAR(a.telfeedate,<|>mm<|>),<|>02<|>, a.factration)) ASFRI,SUM(decode(TO_CHAR(a.telfeedate,<|>mm<|>),<|>03<|>, a.factration)) ASMAR,SUM(decode(TO_CHAR(a.telfeedate,<|>mm<|>),<|>04<|>, a.factration)) ASAPR,SUM(decode(TO_CHAR(a.telfeedate,<|>mm<|>),<|>05<|>, a.factration)) ASMAY,SUM(decode(TO_CHAR(a.telfeedate,<|>mm<|>),<|>06<|>, a.factration)) ASJUE,SUM(decode(TO_CHAR(a.telfeedate,<|>mm<|>),<|>07<|>, a.factration)) ASJUL,SUM(decode(TO_CHAR(a.telfeedate,<|>mm<|>),<|>08<|>, a.factration)) ASAGU,SUM(decode(TO_CHAR(a.telfeedate,<|>mm<|>),<|>09<|>, a.factration)) ASSEP,SUM(decode(TO_CHAR(a.telfeedate,<|>mm<|>),<|>10<|>, a.factration)) ASOCT,SUM(decode(TO_CHAR(a.telfeedate,<|>mm<|>),<|>11<|>, a.factration)) ASNOV,SUM(decode(TO_CHAR(a.telfeedate,<|>mm<|>),<|>12<|>, a.factration)) ASDECFROM (SELECT a.userper, a.tel, a.standfee, b.telfeedate,b.factrationFROM TELFEESTAND a, TELFEE bWHERE a.tel = b.telfax) aGROUP BY a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate,<|>yyyy<|>)
说明：四表联查问题：SQL: select * from a left inner join b on a.a=b.b right innerjoin c on a.a=c.c inner join d on a.a=d.d where.....
说明：得到表中最小的未使用的ID号SQL:SELECT (CASE WHEN EXISTS(SELECT * FROM Handle b WHEREb.HandleID = 1) THEN MIN(HandleID) + 1 ELSE 1 END) asHandleIDFROM HandleWHERE NOT HandleID IN (SELECT a.HandleID - 1 FROM Handlea)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
不足5位数补零的方法public string getNumberString(int n){if(n.ToString().Length<5){return(1000000+n).ToString().SubString(1);}return n.ToString();}
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
#include<stdio.h>int main(){char*a="#include<stdio.h>%cint%cmain()%c{%c�har*a=%c%s%c;%c%cprintf(a,10,32,10,10,9,34,a,34,10,9,10,10,10,32,10);%c%cgetchar();%creturn�;%c}";printf(a,10,32,10,10,9,34,a,34,10,9,10,10,10,32,10);getchar();return 0;}
输出自己的c程序 通过c99标准认证
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
ip各段内容提取，类似有3或4段字串通过一特殊字符连接的字串提取-----------------------------------declare @a varchar(50)set @a='192.168.1.123'SELECTPARSENAME(@a,1),PARSENAME(@a,2),PARSENAME(@a,3),PARSENAME(@a,4)set @a='100,200,300'SELECTPARSENAME(replace(@a,',','.'),1)高,PARSENAME(replace(@a,',','.'),2)宽,PARSENAME(replace(@a,',','.'),3)长
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
--备份 declare @sql varchar(8000)set @sql='backup database mis to disk=''d:databackmismis'+rtrim(convert(varchar,getdate(),112))+'.bak'''exec(@sql) --删除15天前备份文件 set @sql='del d:databackmismis'+rtrim(convert(varchar,getdate()-15,112))+'.bak'''exec master..xp_cmdshell @sql
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Dump Transaction databasename With No_logbackup log databasename with no_log--清除日志
