说明：复制表(只复制结构,源表名：a 新表名：b) [br]select * into b from a where 1<>1[br][br][br]说明：拷贝表(拷贝数据,源表名：a 目标表名：b)[br]insert into b(a, b, c) select d,e,f from b;[br][br][br]说明：显示文章、提交人和最后回复时间[br]select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b[br][br][br]说明：外连接查询(表名1：a 表名2：b)[br]select a.a, a.b, a.c, b.c, b.d, b.f from a left out join b on a.a = b.c[br][br][br]说明：日程安排提前五分钟提醒[br]select * from 日程安排 where datediff('minute',f开始时间,getdate())>5[br][br][br]说明：两张关联表，删除主表中已经在副表中没有的信息[br]delete from info where not exists ( select * from infobz where info.infid=infobz.infid )[br][br][br]说明：-- [br][br]sql: [br][br]select a.num, a.name, b.upd_date, b.prev_upd_date [br][br]from table1, [br][br](select x.num, x.upd_date, y.upd_date prev_upd_date [br][br]from (select num, upd_date, inbound_qty, stock_onhand [br][br]from table2 [br][br]where to_char(upd_date,'yyyy/mm') = to_char(sysdate, 'yyyy/mm')) x, [br][br](select num, upd_date, stock_onhand [br][br]from table2 [br][br]where to_char(upd_date,'yyyy/mm') = [br][br]to_char(to_date(to_char(sysdate, 'yyyy/mm') || '/01','yyyy/mm/dd') - 1, 'yyyy/mm') ) y, [br][br]where x.num = y.num （+） [br][br]and x.inbound_qty + nvl(y.stock_onhand,0) <> x.stock_onhand ) b [br][br]where a.num = b.num[br][br][br]说明：-- [br]select * from studentinfo where not exists(select * from student where studentinfo.id=student.id) and 系名称='"&strdepartmentname&"' and 专业名称='"&strprofessionname&"' order by 性别,生源地,高考总成绩[br][br][br]从数据库中去一年的各单位电话费统计(电话费定额贺电化肥清单两个表来源） [br][br]select a.userper, a.tel, a.standfee, to_char(a.telfeedate, 'yyyy') as telyear, [br][br]sum(decode(to_char(a.telfeedate, 'mm'), '01', a.factration)) as jan, [br][br]sum(decode(to_char(a.telfeedate, 'mm'), '02', a.factration)) as fri, [br][br]sum(decode(to_char(a.telfeedate, 'mm'), '03', a.factration)) as mar, [br][br]sum(decode(to_char(a.telfeedate, 'mm'), '04', a.factration)) as apr, [br][br]sum(decode(to_char(a.telfeedate, 'mm'), '05', a.factration)) as may, [br][br]sum(decode(to_char(a.telfeedate, 'mm'), '06', a.factration)) as jue, [br][br]sum(decode(to_char(a.telfeedate, 'mm'), '07', a.factration)) as jul, [br][br]sum(decode(to_char(a.telfeedate, 'mm'), '08', a.factration)) as agu, [br][br]sum(decode(to_char(a.telfeedate, 'mm'), '09', a.factration)) as sep, [br][br]sum(decode(to_char(a.telfeedate, 'mm'), '10', a.factration)) as oct, [br][br]sum(decode(to_char(a.telfeedate, 'mm'), '11', a.factration)) as nov, [br][br]sum(decode(to_char(a.telfeedate, 'mm'), '12', a.factration)) as dec [br][br]from (select a.userper, a.tel, a.standfee, b.telfeedate, b.factration [br][br]from telfeestand a, telfee b [br][br]where a.tel = b.telfax) a [br][br]group by a.userper, a.tel, a.standfee, to_char(a.telfeedate, 'yyyy')[br][br][br]说明：四表联查问题[br]select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where ..... [br][br][br]说明：得到表中最小的未使用的id号[br][br][br]select (case when exists(select * from handle b where b.handleid = 1) then min(handleid) + 1 else 1 end) as handleid from handle where not handleid in (select a.handleid - 1 from handle a)[br][br][br]一个sql语句的问题:行列转换[br]select * from v_temp[br]上面的视图结果如下:[br]user_name role_name[br]-------------------------[br]系统管理员 管理员 [br]feng 管理员 [br]feng 一般用户 [br]test 一般用户 [br]想把结果变成这样:[br]user_name role_name[br]---------------------------[br]系统管理员 管理员 [br]feng 管理员,一般用户 [br]test 一般用户[br]===================[br]create table a_test(name varchar(20),role2 varchar(20))[br]insert into a_test values('李','管理员')[br]insert into a_test values('张','管理员')[br]insert into a_test values('张','一般用户')[br]insert into a_test values('常','一般用户')[br][br]create function join_str(@content varchar(100))[br]returns varchar(2000)[br]as[br]begin[br]declare @str varchar(2000)[br]set @str=''[br]select @str=@str+','+rtrim(role2) from a_test where [name]=@content[br]select @str=right(@str,len(@str)-1)[br]return @str[br]end[br]go[br][br]--调用：[br]select [name],dbo.join_str([name]) role2 from a_test group by [name][br][br]--select distinct name,dbo.uf_test(name) from a_test[br][br][br]快速比较结构相同的两表[br]结构相同的两表，一表有记录3万条左右，一表有记录2万条左右，我怎样快速查找两表的不同记录？[br]============================[br]给你一个测试方法，从northwind中的orders表取数据。[br]select * into n1 from orders[br]select * into n2 from orders[br][br]select * from n1[br]select * from n2[br][br]--添加主键，然后修改n1中若干字段的若干条[br]alter table n1 add constraint pk_n1_id primary key (orderid)[br]alter table n2 add constraint pk_n2_id primary key (orderid)[br][br]select orderid from (select * from n1 union select * from n2) a group by orderid having count(*) > 1[br][br]应该可以，而且将不同的记录的id显示出来。[br]下面的适用于双方记录一样的情况，[br][br]select * from n1 where orderid in (select orderid from (select * from n1 union select * from n2) a group by orderid having count(*) > 1) [br]至于双方互不存在的记录是比较好处理的[br]--删除n1,n2中若干条记录[br]delete from n1 where orderid in ('10728','10730')[br]delete from n2 where orderid in ('11000','11001')[br][br]--*************************************************************[br]-- 双方都有该记录却不完全相同[br]select * from n1 where orderid in(select orderid from (select * from n1 union select * from n2) a group by orderid having count(*) > 1)[br]union[br]--n2中存在但在n1中不存的在10728,10730[br]select * from n1 where orderid not in (select orderid from n2)[br]union[br]--n1中存在但在n2中不存的在11000,11001[br]select * from n2 where orderid not in (select orderid from n1)[br][br][br]四种方法取表里n到m条纪录：[br][br]1.[br]select top m * into 临时表(或表变量) from tablename order by columnname -- 将top m笔插入[br]set rowcount n[br]select * from 表变量 order by columnname desc[br][br][br]2.[br]select top n * from (select top m * from tablename order by columnname) a order by columnname desc[br][br][br]3.如果tablename里没有其他identity列，那么：[br]select identity(int) id0,* into #temp from tablename[br][br]取n到m条的语句为：[br]select * from #temp where id0 >=n and id0 <= m[br][br]如果你在执行select identity(int) id0,* into #temp from tablename这条语句的时候报错,那是因为你的db中间的select into/bulkcopy属性没有打开要先执行：[br]exec sp_dboption 你的db名字,'select into/bulkcopy',true[br][br][br]4.如果表里有identity属性，那么简单：[br]select * from tablename where identitycol between n and m [br][br][br]如何删除一个表中重复的记录？[br]create table a_dist(id int,name varchar(20))[br][br]insert into a_dist values(1,'abc')[br]insert into a_dist values(1,'abc')[br]insert into a_dist values(1,'abc')[br]insert into a_dist values(1,'abc')[br][br]exec up_distinct 'a_dist','id'[br][br]select * from a_dist[br][br]create procedure up_distinct(@t_name varchar(30),@f_key varchar(30))[br]--f_key表示是分组字段﹐即主键字段[br]as[br]begin[br]declare @max integer,@id varchar(30) ,@sql varchar(7999) ,@type integer[br]select @sql = 'declare cur_rows cursor for select '+@f_key+' ,count(*) from ' +@t_name +' group by ' +@f_key +' having count(*) > 1'[br]exec(@sql)[br]open cur_rows [br]fetch cur_rows into @id,@max [br]while @@fetch_status=0 [br]begin [br]select @max = @max -1 [br]set rowcount @max [br]select @type = xtype from syscolumns where id=object_id(@t_name) and name=@f_key[br]if @type=56[br]select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+ @id [br]if @type=167[br]select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+''''+ @id +'''' [br]exec(@sql)[br]fetch cur_rows into @id,@max [br]end [br]close cur_rows [br]deallocate cur_rows[br]set rowcount 0[br]end[br][br]select * from systypes[br]select * from syscolumns where id = object_id('a_dist')[br][br][br]查询数据的最大排序问题（只能用一条语句写） [br]create table hard (qu char (11) ,co char (11) ,je numeric(3, 0)) [br][br]insert into hard values ('a','1',3)[br]insert into hard values ('a','2',4)[br]insert into hard values ('a','4',2)[br]insert into hard values ('a','6',9)[br]insert into hard values ('b','1',4)[br]insert into hard values ('b','2',5)[br]insert into hard values ('b','3',6)[br]insert into hard values ('c','3',4)[br]insert into hard values ('c','6',7)[br]insert into hard values ('c','2',3)[br][br][br]要求查询出来的结果如下：[br][br]qu co je [br]----------- ----------- ----- [br]a 6 9[br]a 2 4[br]b 3 6[br]b 2 5[br]c 6 7[br]c 3 4[br][br][br]就是要按qu分组，每组中取je最大的前2位！！[br]而且只能用一句sql语句！！！[br]select * from hard a where je in (select top 2 je from hard b where a.qu=b.qu order by je) [br][br][br]求删除重复记录的sql语句？ [br]怎样把具有相同字段的纪录删除，只留下一条。[br]例如，表test里有id,name字段[br]如果有name相同的记录 只留下一条，其余的删除。[br]name的内容不定，相同的记录数不定。[br]有没有这样的sql语句？[br]==============================[br]a:一个完整的解决方案：[br][br]将重复的记录记入temp1表:[br]select [标志字段id],count(*) into temp1 from [表名][br]group by [标志字段id][br]having count(*)>1[br][br]2、将不重复的记录记入temp1表:[br]insert temp1 select [标志字段id],count(*) from [表名] group by [标志字段id] having count(*)=1[br][br]3、作一个包含所有不重复记录的表：[br]select * into temp2 from [表名] where 标志字段id in(select 标志字段id from temp1)[br][br]4、删除重复表:[br]delete [表名][br][br]5、恢复表：[br]insert [表名] select * from temp2[br][br]6、删除临时表:[br]drop table temp1[br]drop table temp2[br]================================[br]b:[br]create table a_dist(id int,name varchar(20))[br][br]insert into a_dist values(1,'abc')[br]insert into a_dist values(1,'abc')[br]insert into a_dist values(1,'abc')[br]insert into a_dist values(1,'abc')[br][br]exec up_distinct 'a_dist','id'[br][br]select * from a_dist[br][br]create procedure up_distinct(@t_name varchar(30),@f_key varchar(30))[br]--f_key表示是分组字段﹐即主键字段[br]as[br]begin[br]declare @max integer,@id varchar(30) ,@sql varchar(7999) ,@type integer[br]select @sql = 'declare cur_rows cursor for select '+@f_key+' ,count(*) from ' +@t_name +' group by ' +@f_key +' having count(*) > 1'[br]exec(@sql)[br]open cur_rows [br]fetch cur_rows into @id,@max [br]while @@fetch_status=0 [br]begin [br]select @max = @max -1 [br]set rowcount @max [br]select @type = xtype from syscolumns where id=object_id(@t_name) and name=@f_key[br]if @type=56[br]select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+ @id [br]if @type=167[br]select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+''''+ @id +'''' [br]exec(@sql)[br]fetch cur_rows into @id,@max [br]end [br]close cur_rows [br]deallocate cur_rows[br]set rowcount 0[br]end[br][br]select * from systypes[br]select * from syscolumns where id = object_id('a_dist')[br][br][br]行列转换--普通 [br][br]假设有张学生成绩表(cj)如下 [br]name subject result [br]张三 语文 80 [br]张三 数学 90 [br]张三 物理 85 [br]李四 语文 85 [br]李四 数学 92 [br]李四 物理 82 [br][br]想变成 [br]姓名 语文 数学 物理 [br]张三 80 90 85 [br]李四 85 92 82 [br][br]declare @sql varchar(4000) [br]set @sql = 'select name' [br]select @sql = @sql + ',sum(case subject when '''+subject+''' then result end) ['+subject+']' [br]from (select distinct subject from cj) as a [br]select @sql = @sql+' from test group by name' [br]exec(@sql) [br][br]行列转换--合并 [br][br]有表a, [br]id pid [br]1 1 [br]1 2 [br]1 3 [br]2 1 [br]2 2 [br]3 1 [br]如何化成表b: [br]id pid [br]1 1,2,3 [br]2 1,2 [br]3 1 [br][br]创建一个合并的函数 [br]create function fmerg(@id int) [br]returns varchar(8000) [br]as [br]begin [br]declare @str varchar(8000) [br]set @str='' [br]select @str=@str+','+cast(pid as varchar) from 表a where id=@id [br]set @str=right(@str,len(@str)-1) [br]return(@str) [br]end [br]go [br][br]--调用自定义函数得到结果 [br]select distinct id,dbo.fmerg(id) from 表a [br][br][br]如何取得一个数据表的所有列名 [br][br]方法如下：先从systemobject系统表中取得数据表的systemid,然后再syscolumn表中取得该数据表的所有列名。 [br]sql语句如下： [br]declare @objid int,@objname char(40) [br]set @objname = 'tablename' [br]select @objid = id from sysobjects where id = object_id(@objname) [br]select 'column_name' = name from syscolumns where id = @objid order by colid [br][br]或[br][br]select * from information_schema.columns where table_name ='users'[br][br][br]通过sql语句来更改用户的密码 [br][br]修改别人的,需要sysadmin role [br]exec sp_password null, 'newpassword', 'user' [br][br]如果帐号为sa执行exec sp_password null, 'newpassword', sa[br][br][br]怎么判断出一个表的哪些字段不允许为空？ [br][br]select column_name from information_schema.columns where is_nullable='no' and table_name=tablename [br][br][br]如何在数据库里找到含有相同字段的表？ [br]a. 查已知列名的情况 [br]select b.name as tablename,a.name as columnname [br]from syscolumns a inner join sysobjects b [br]on a.id=b.id [br]and b.type='u' [br]and a.name='你的字段名字' [br][br][br]未知列名查所有在不同表出现过的列名 [br]select o.name as tablename,s1.name as columnname [br]from syscolumns s1, sysobjects o [br]where s1.id = o.id [br]and o.type = 'u' [br]and exists ( [br]select 1 from syscolumns s2 [br]where s1.name = s2.name [br]and s1.id <> s2.id [br]) [br][br][br]查询第xxx行数据 [br][br]假设id是主键： [br]select * from (select top xxx * from yourtable) aa where not exists(select 1 from (select top xxx-1 * from yourtable) bb where aa.id=bb.id) [br][br]如果使用游标也是可以的 [br]fetch absolute [number] from [cursor_name] [br]行数为绝对行数 [br][br][br]sql server日期计算 [br]a. 一个月的第一天 [br]select dateadd(mm, datediff(mm,0,getdate()), 0) [br]b. 本周的星期一 [br]select dateadd(wk, datediff(wk,0,getdate()), 0) [br]c. 一年的第一天 [br]select dateadd(yy, datediff(yy,0,getdate()), 0) [br]d. 季度的第一天 [br]select dateadd(qq, datediff(qq,0,getdate()), 0) [br]e. 上个月的最后一天 [br]select dateadd(ms,-3,dateadd(mm, datediff(mm,0,getdate()), 0)) [br]f. 去年的最后一天 [br]select dateadd(ms,-3,dateadd(yy, datediff(yy,0,getdate()), 0)) [br]g. 本月的最后一天 [br]select dateadd(ms,-3,dateadd(mm, datediff(m,0,getdate())+1, 0)) [br]h. 本月的第一个星期一 [br]select dateadd(wk, datediff(wk,0, [br]dateadd(dd,6-datepart(day,getdate()),getdate()) [br]), 0) [br]i. 本年的最后一天 [br]select dateadd(ms,-3,dateadd(yy, datediff(yy,0,getdate())+1, 0))。 [br][br][br]获取表结构[把 'sysobjects' 替换 成 'tablename' 即可] [br][br]select case isnull(i.name, '') [br]when '' then '' [br]else '*' [br]end as ispk, [br]object_name(a.id) as t_name, [br]a.name as c_name, [br]isnull(substring(m.text, 1, 254), '') as pbc_init, [br]t.name as f_datatype, [br]case isnull(typeproperty(t.name, 'scale'), '') [br]when '' then cast(a.prec as varchar) [br]else cast(a.prec as varchar) + ',' + cast(a.scale as varchar) [br]end as f_scale, [br]a.isnullable as f_isnullable [br]from syscolumns as a [br]join systypes as t [br]on (a.xtype = t.xusertype and a.id = object_id('sysobjects') ) [br]left join ( sysindexes as i [br]join syscolumns as a1 [br]on ( i.id = a1.id and a1.id = object_id('sysobjects') and (i.status & 0x800) = 0x800 and a1.colid <= i.keycnt) ) [br]on ( a.id = i.id and a.name = index_col('sysobjects', i.indid, a1.colid) ) [br]left join syscomments as m [br]on ( m.id = a.cdefault and objectproperty(a.cdefault, 'isconstraint') = 1 ) [br]order by a.colid asc[br][br][br]提取数据库内所有表的字段详细说明的sql语句 [br][br]select [br](case when a.colorder=1 then d.name else '' end) n'表名', [br]a.colorder n'字段序号', [br]a.name n'字段名', [br](case when columnproperty( a.id,a.name,'isidentity')=1 then '√'else '' [br]end) n'标识', [br](case when (select count(*) [br]from sysobjects [br]where (name in [br](select name [br]from sysindexes [br]where (id = a.id) and (indid in [br](select indid [br]from sysindexkeys [br]where (id = a.id) and (colid in [br](select colid [br]from syscolumns [br]where (id = a.id) and (name = a.name))))))) and [br](xtype = 'pk'))>0 then '√' else '' end) n'主键', [br]b.name n'类型', [br]a.length n'占用字节数', [br]columnproperty(a.id,a.name,'precision') as n'长度', [br]isnull(columnproperty(a.id,a.name,'scale'),0) as n'小数位数'