精妙的SQL語句

说明：复制表(只复制结构,源表名：a 新表名：b) SQL: select * into b from a where 11 -

说明：拷贝表(拷贝数据,源表名：a 目标表名：b) -

SQL: insert into b(a, b, c) select d,e,f from b; -

说明：显示文章、提交人和最后回复时间-

-

SQL: select a.title,a.username,b.adddate from table a,(select max(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 JOIN b ON a.a = b.c -

说明：日程安排提前五分钟提醒 -

SQL: select * from 日程安排 where datediff(’minute’,f开始时间,getdate())>5 -

说明：两张关联表，删除主表中已经在副表中没有的信息 -

SQL: -

delete from info where not exists ( select * from infobz where info.infid=infobz.infid ) -

说明：– -

SQL: -

SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE -

FROM TABLE1, -

(SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE -

FROM (SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND -

FROM TABLE2 -

WHERE TO_CHAR(UPD_DATE,’YYYY/MM’) = TO_CHAR(SYSDATE, ‘YYYY/MM’)) X, -

(SELECT NUM, UPD_DATE, STOCK_ONHAND -

FROM TABLE2 -

WHERE 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 ) B -

WHERE A.NUM = B.NUM -

说明：– -

SQL: -

select * from studentinfo where not exists(select * from student where studentinfo.id=student.id) and 系名称=’”&strdepartmentname&”‘ and 专业名称=’”&strprofessionname&”‘ order by 性别,生源地,高考总成绩 -

说明： -

从数据库中去一年的各单位电话费统计(电话费定额贺电化肥清单两个表来源） -

SQL: -

SELECT a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, ‘yyyy’) AS telyear, -

SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ‘01′, a.factration)) AS JAN, -

SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ‘02′, a.factration)) AS FRI, -

SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ‘03′, a.factration)) AS MAR, -

SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ‘04′, a.factration)) AS APR, -

SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ‘05′, a.factration)) AS MAY, -

SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ‘06′, a.factration)) AS JUE, -

SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ‘07′, a.factration)) AS JUL, -

SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ‘08′, a.factration)) AS AGU, -

SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ‘09′, a.factration)) AS SEP, -

SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ‘10′, a.factration)) AS OCT, -

SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ‘11′, a.factration)) AS NOV, -

SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ‘12′, a.factration)) AS DEC -

FROM (SELECT a.userper, a.tel, a.standfee, b.telfeedate, b.factration -

FROM TELFEESTAND a, TELFEE b -

WHERE a.tel = b.telfax) a -

GROUP 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 inner join 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 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)-

——————————————————————————–-

1.说明：复制表(只复制结构,源表名：a 新表名：b)-

SQL: select * into b from a where 11 -

2.说明：拷贝表(拷贝数据,源表名：a 目标表名：b) -

SQL: insert into b(a, b, c) select d,e,f from a; -

3.说明：显示文章、提交人和最后回复时间 -

SQL: select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b -

4.说明：外连接查询(表名1：a 表名2：b) -

SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUTER JOIN b ON a.a = b.c -

5.说明：日程安排提前五分钟提醒 -

SQL: select * from 日程安排 where datediff(’minute’,f开始时间,getdate())>5 -

6.说明：两张关联表，删除主表中已经在副表中没有的信息 -

SQL: -

delete from info where not exists ( select * from infobz where info.infid=infobz.infid ) -

说明：– -

SQL: -

SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE -

FROM TABLE1, -

(SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE -

FROM (SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND -

FROM TABLE2 -

WHERE TO_CHAR(UPD_DATE,’YYYY/MM’) = TO_CHAR(SYSDATE, ‘YYYY/MM’)) X, -

(SELECT NUM, UPD_DATE, STOCK_ONHAND -

FROM TABLE2 -

WHERE 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 ) B -

WHERE A.NUM = B.NUM -

说明：– -

SQL: -

select * from studentinfo where not exists(select * from student where studentinfo.id=student.id) and 系名称=’”&strdepartmentname&”‘ and 专业名称=’”&strprofessionname&”‘ order by 性别,生源地,高考总成绩 -

7.说明： -

从数据库中去一年的各单位电话费统计(电话费定额贺电化肥清单两个表来源） -

SQL: -

SELECT a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, ‘yyyy’) AS telyear, -

SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ‘01′, a.factration)) AS JAN, -

SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ‘02′, a.factration)) AS FRI, -

SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ‘03′, a.factration)) AS MAR, -

SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ‘04′, a.factration)) AS APR, -

SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ‘05′, a.factration)) AS MAY, -

SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ‘06′, a.factration)) AS JUE, -

SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ‘07′, a.factration)) AS JUL, -

SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ‘08′, a.factration)) AS AGU, -

SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ‘09′, a.factration)) AS SEP, -

SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ‘10′, a.factration)) AS OCT, -

SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ‘11′, a.factration)) AS NOV, -

SUM(decode(TO_CHAR(a.telfeedate, ‘mm’), ‘12′, a.factration)) AS DEC -

FROM (SELECT a.userper, a.tel, a.standfee, b.telfeedate, b.factration -

FROM TELFEESTAND a, TELFEE b -

WHERE a.tel = b.telfax) a -

GROUP BY a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, ‘yyyy’) -

8.说明：四表联查问题： -

SQL: 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 ….. -

9.说明：得到表中最小的未使用的ID号 -

SQL: -

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)-

9.SQL语句技巧-

9.1、一个SQL语句的问题:行列转换-

select * from v_temp-

上面的视图结果如下:-

user_name role_name-

————————--

系统管理员 管理员-

feng 管理员-

feng 一般用户-

test 一般用户-

想把结果变成这样:-

user_name role_name-

—————————-

系统管理员 管理员-

feng 管理员,一般用户-

test 一般用户-

===================-

create table a_test(name varchar(20),role2 varchar(20))-

insert into a_test values(’李’,'管理員’)-

insert into a_test values(’張’,'管理員’)-

insert into a_test values(’張’,'一般用戶’)-

insert into a_test values(’常’,'一般用戶’)-

create function join_str(@content varchar(100))-

returns varchar(2000)-

as-

begin-

declare @str varchar(2000)-

set @str=”-

select @str=@str+’,'+rtrim(role2) from a_test where [name]=@content-

select @str=right(@str,len(@str)-1)-

return @str-

end-

go-

–调用：-

select [name],dbo.join_str([name]) role2 from a_test group by [name]-

–select distinct name,dbo.uf_test(name) from a_test-

9.2、求助！快速比较结构相同的两表-

结构相同的两表，一表有记录3万条左右，一表有记录2万条左右，我怎样快速查找两表的不同记录？-

============================-

给你一个测试方法，从northwind中的orders表取数据。-

select * into n1 from orders-

select * into n2 from orders-

select * from n1-

select * from n2-

–添加主键，然后修改n1中若干字段的若干条-

alter table n1 add constraint pk_n1_id primary key (OrderID)-

alter table n2 add constraint pk_n2_id primary key (OrderID)-

select OrderID from (select * from n1-

union-

select * from n2) a group by OrderID having count(*) > 1-

应该可以，而且将不同的记录的ID显示出来。-

下面的适用于双方记录一样的情况，-

select * from n1 where orderid in-

(-

select OrderID from (select * from n1-

union-

select * from n2) a group by OrderID having count(*) > 1-

)-

至于双方互不存在的记录是比较好处理的-

–删除n1,n2中若干条记录-

delete from n1 where orderID in (’10728′,’10730′)-

delete from n2 where orderID in (’11000′,’11001′)-

–*************************************************************-

– 双方都有该记录却不完全相同-

select * from n1 where orderid in-

(-

select OrderID from (select * from n1-

union-

select * from n2) a group by OrderID having count(*) > 1-

)-

union-

–n2中存在但在n1中不存的在10728,10730-

select * from n1 where OrderID not in (select OrderID from n2)-

union-

–n1中存在但在n2中不存的在11000,11001-

select * from n2 where OrderID not in (select OrderID from n1)-

9.3、四种方法取表里n到m条纪录：-

1.-

select top m * into 临时表(或表变量) from tablename order by columnname — 将top m笔插入-

set rowcount n-

select * from 表变量 order by columnname desc-

2.-

select top n * from-

(select top m * from tablename order by columnname) a-

order by columnname desc-

3.如果tablename里没有其他identity列，那么：-

select identity(int) id0,* into #temp from tablename-

取n到m条的语句为：-

select * from #temp where id0 >=n and id0 1′-

exec(@sql)-

open cur_rows-

fetch cur_rows into @id,@max-

while @@fetch_status=0-

begin-

select @max = @max -1-

set rowcount @max-

select @type = xtype from syscolumns where id=object_id(@t_name) and name=@f_key-

if @type=56-

select @sql = ‘delete from ‘+@t_name+’ where ‘ + @f_key+’ = ‘+ @id-

if @type=167-

select @sql = ‘delete from ‘+@t_name+’ where ‘ + @f_key+’ = ‘+””+ @id +””-

exec(@sql)-

fetch cur_rows into @id,@max-

end-

close cur_rows-

deallocate cur_rows-

set rowcount 0-

end-

select * from systypes-

select * from syscolumns where id = object_id(’a_dist’)-

9.4.查询数据的最大排序问题（只能用一条语句写）-

CREATE TABLE hard (qu char (11) ,co char (11) ,je numeric(3, 0)) -

insert into hard values (’A',’1′,3)-

insert into hard values (’A',’2′,4)-

insert into hard values (’A',’4′,2)-

insert into hard values (’A',’6′,9)-

insert into hard values (’B',’1′,4)-

insert into hard values (’B',’2′,5)-

insert into hard values (’B',’3′,6)-

insert into hard values (’C',’3′,4)-

insert into hard values (’C',’6′,7)-

insert into hard values (’C',’2′,3)-

要求查询出来的结果如下：-

qu co je-

———– ———– —–-

A 6 9-

A 2 4-

B 3 6-

B 2 5-

C 6 7-

C 3 4-

就是要按qu分组，每组中取je最大的前2位！！-

而且只能用一句sql语句！！！-

select * from hard a where je in (select top 2 je from hard b where a.qu=b.qu order by je)-

9.5.求删除重复记录的sql语句？-

怎样把具有相同字段的纪录删除，只留下一条。-

例如，表test里有id,name字段-

如果有name相同的记录 只留下一条，其余的删除。-

name的内容不定，相同的记录数不定。-

有没有这样的sql语句？-

==============================-

A:一个完整的解决方案：-

将重复的记录记入temp1表:-

select [标志字段id],count(*) into temp1 from [表名]-

group by [标志字段id]-

having count(*)>1-

2、将不重复的记录记入temp1表:-

insert temp1-

select [标志字段id],count(*) from [表名]-

group by [标志字段id]-

having count(*)=1-

3、作一个包含所有不重复记录的表：-

select * into temp2 from [表名]-

where 标志字段id in(select 标志字段id from temp1)-

4、删除重复表:-

delete [表名]-

5、恢复表：-

insert [表名]-

select * from temp2-

6、删除临时表:-

drop table temp1-

drop table temp2-

================================-

B:-

create table a_dist(id int,name varchar(20))-

insert into a_dist values(1,’abc’)-

insert into a_dist values(1,’abc’)-

insert into a_dist values(1,’abc’)-

insert into a_dist values(1,’abc’)-

exec up_distinct ‘a_dist’,'id’-

select * from a_dist-

create procedure up_distinct(@t_name varchar(30),@f_key varchar(30))-

–f_key表示是分組字段﹐即主鍵字段-

as-

begin-

declare @max integer,@id varchar(30) ,@sql varchar(7999) ,@type integer-

select @sql = ‘declare cur_rows cursor for select ‘+@f_key+’ ,count(*) from ‘ +@t_name +’ group by ‘ +@f_key +’ having count(*) > 1′-

exec(@sql)-

open cur_rows-

fetch cur_rows into @id,@max-

while @@fetch_status=0-

begin-

select @max = @max -1-

set rowcount @max-

select @type = xtype from syscolumns where id=object_id(@t_name) and name=@f_key-

if @type=56-

select @sql = ‘delete from ‘+@t_name+’ where ‘ + @f_key+’ = ‘+ @id-

if @type=167-

select @sql = ‘delete from ‘+@t_name+’ where ‘ + @f_key+’ = ‘+””+ @id +””-

exec(@sql)-

fetch cur_rows into @id,@max-

end-

close cur_rows-

deallocate cur_rows-

set rowcount 0-

end-

select * from systypes-

select * from syscolumns where id = object_id(’a_dist’)-

10.1. 行列转换–普通 -

假设有张学生成绩表(CJ)如下-

Name Subject Result-

张三 语文 80-

张三 数学 90-

张三 物理 85-

李四 语文 85-

李四 数学 92-

李四 物理 82 -

想变成-

姓名 语文 数学 物理-

张三 80 90 85-

李四 85 92 82 -

declare @sql varchar(4000)-

set @sql = ’select Name’-

select @sql = @sql + ‘,sum(case Subject when ”’+Subject+”’ then Result end) ['+Subject+']‘-

from (select distinct Subject from CJ) as a-

select @sql = @sql+’ from test group by name’-

exec(@sql) -

10.2. 行列转换–合并 -

有表A,-

id pid-

1 1-

1 2-

1 3-

2 1-

2 2-

3 1-

如何化成表B:-

id pid-

1 1,2,3-

2 1,2-

3 1 -

创建一个合并的函数-

create function fmerg(@id int)-

returns varchar(8000)-

as-

begin-

declare @str varchar(8000)-

set @str=”-

select @str=@str+’,'+cast(pid as varchar) from 表A where id=@id-

set @str=right(@str,len(@str)-1)-

return(@str)-

End-

go -

–调用自定义函数得到结果-

select distinct id,dbo.fmerg(id) from 表A -

10.3. 如何取得一个数据表的所有列名 -

方法如下：先从SYSTEMOBJECT系统表中取得数据表的SYSTEMID,然后再SYSCOLUMN表中取得该数据表的所有列名。-

SQL语句如下：-

declare @objid int,@objname char(40)-

set @objname = ‘tablename’-

select @objid = id from sysobjects where id = object_id(@objname)-

select ‘Column_name’ = name from syscolumns where id = @objid order by colid -

是不是太简单了？ 呵呵 不过经常用阿. -

10.4. 通过SQL语句来更改用户的密码 -

修改别人的,需要sysadmin role-

EXEC sp_password NULL, ‘newpassword’, ‘User’ -

如果帐号为SA执行EXEC sp_password NULL, ‘newpassword’, sa -

10.5. 怎么判断出一个表的哪些字段不允许为空？ -

select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where IS_NULLABLE=’NO’ and TABLE_NAME=tablename -

10.6. 如何在数据库里找到含有相同字段的表？-

a. 查已知列名的情况-

SELECT b.name as TableName,a.name as columnname-

From syscolumns a INNER JOIN sysobjects b-

ON a.id=b.id-

AND b.type=’U’-

AND a.name=’你的字段名字’ -

b. 未知列名查所有在不同表出现过的列名-

Select o.name As tablename,s1.name As columnname-

From syscolumns s1, sysobjects o-

Where s1.id = o.id-

And o.type = ‘U’-

And Exists (-

Select 1 From syscolumns s2-

Where s1.name = s2.name-

And s1.id s2.id-

) -

10.7. 查询第xxx行数据 -

假设id是主键：-

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) -

如果使用游标也是可以的-

fetch absolute [number] from [cursor_name]-

行数为绝对行数 -

10.8. SQL Server日期计算-

a. 一个月的第一天-

SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)-

b. 本周的星期一-

SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)-

c. 一年的第一天-

SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)-

d. 季度的第一天-

SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)-

e. 上个月的最后一天-

SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))-

f. 去年的最后一天-

SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))-

g. 本月的最后一天-

SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))-

h. 本月的第一个星期一-

select DATEADD(wk, DATEDIFF(wk,0,-

dateadd(dd,6-datepart(day,getdate()),getdate())-

), 0)-

i. 本年的最后一天-

SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))。 -

11.1.获取表结构[把 'sysobjects' 替换 成 'tablename' 即可] -

SELECT CASE IsNull(I.name, ”)-

When ” Then ”-

Else ‘*’-

End as IsPK,-

Object_Name(A.id) as t_name,-

A.name as c_name,-

IsNull(SubString(M.text, 1, 254), ”) as pbc_init,-

T.name as F_DataType,-

CASE IsNull(TYPEPROPERTY(T.name, ‘Scale’), ”)-

WHEN ” Then Cast(A.prec as varchar)-

ELSE Cast(A.prec as varchar) + ‘,’ + Cast(A.scale as varchar)-

END as F_Scale,-

A.isnullable as F_isNullAble-

FROM Syscolumns as A-

JOIN Systypes as T-

ON (A.xType = T.xUserType AND A.Id = Object_id(’sysobjects’) )-

LEFT JOIN ( SysIndexes as I-

JOIN Syscolumns as A1-

ON ( I.id = A1.id and A1.id = object_id(’sysobjects’) and (I.status & 0×800) = 0×800 AND A1.colid 0 then ‘√’ else ” end) N’主键’,-

b.name N’类型’,-

a.length N’占用字节数’,-

COLUMNPROPERTY(a.id,a.name,’PRECISION’) as N’长度’,-

isnull(COLUMNPROPERTY(a.id,a.name,’Scale’),0) as N’小数位数’,-

(case when a.isnullable=1 then ‘√’else ” end) N’允许空’,-

isnull(e.text,”) N’默认值’,-

isnull(g.[value],”) AS N’字段说明’-

FROM syscolumns a-

left join systypes b-

on a.xtype=b.xusertype-

inner join sysobjects d-

on a.id=d.id and d.xtype=’U’ and d.name’dtproperties’-

left join syscomments e-

on a.cdefault=e.id-

left join sysproperties g-

on a.id=g.id AND a.colid = g.smallid-

order by object_name(a.id),a.colorder -

11.3.快速获取表test的记录总数[对大容量表非常有效] -

快速获取表test的记录总数:-

select rows from sysindexes where id = object_id(‘test’) and indid in (0,1)-

update 2 set KHXH=(ID+1)\2 2行递增编号-

update [23] set id1 = ‘No.’+right(’00000000′+id,6) where id not like ‘No%’ //递增-

update [23] set id1= ‘No.’+right(’00000000′+replace(id1,’No.’,”),6) //补位递增-

delete from [1] where (id%2)=1-

奇数-

替换表名字段-

update [1] set domurl = replace(domurl,’/upload/Imgswf/’,'/upload/Photo/’) where domurl like ‘%/upload/Imgswf/%’-

截位-

SELECT LEFT(表名, 5)-