精妙的SQL语句

leves
浏览: 182584 次
性别:
来自: 杭州
最近访客更多访客>>

xiangshouxiyang
Black_Me
javabang
彩鸟飞
博主相关

博客
微博
相册
留言
关于我
文章分类

社区版块

存档分类

博客分类：
SQL大杂烩
SQL F#数据结构 C C++
说明：复制表(只复制结构,源表名：a新表名：b)
select*intobfromawhere1<>1
说明：拷贝表(拷贝数据,源表名：a目标表名：b)
insertintob(a,b,c)selectd,e,ffromb;
说明：显示文章、提交人和最后回复时间
selecta.title,a.username,b.adddatefromtablea,(selectmax(adddate)adddatefromtablewheretable.title=a.title)b
说明：外连接查询(表名1：a表名2：b)
selecta.a,a.b,a.c,b.c,b.d,b.ffromaLEFTOUTJOINbONa.a=b.c
说明：日程安排提前五分钟提醒

select*from日程安排wheredatediff('minute',f开始时间,getdate())>5 
说明：两张关联表，删除主表中已经在副表中没有的信息
deletefrominfowherenotexists(select*frominfobzwhereinfo.infid=infobz.infid)
说明：--
SQL:
SelectA.NUM,A.NAME,B.UPD_DATE,B.PREV_UPD_DATE
FROMTABLE1,
(SelectX.NUM,X.UPD_DATE,Y.UPD_DATEPREV_UPD_DATE
FROM(SelectNUM,UPD_DATE,INBOUND_QTY,STOCK_ONHAND
FROMTABLE2

WhereTO_CHAR(UPD_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM'))X, 
(SelectNUM,UPD_DATE,STOCK_ONHAND
FROMTABLE2

WhereTO_CHAR(UPD_DATE,'YYYY/MM')= 

TO_CHAR(TO_DATE(TO_CHAR(SYSDATE,'YYYY/MM')||'/01','YYYY/MM/DD')-1,'YYYY/MM'))Y, 
WhereX.NUM=Y.NUM（+）
ANDX.INBOUND_QTY+NVL(Y.STOCK_ONHAND,0)<>X.STOCK_ONHAND)B
WhereA.NUM=B.NUM
说明：--

select*fromstudentinfowherenotexists(select*fromstudentwherestudentinfo.id=student.id)and系名称='"&strdepartmentname&"'and专业名称='"&strprofessionname&"'orderby性别,生源地,高考总成绩 
从数据库中去一年的各单位电话费统计(电话费定额贺电化肥清单两个表来源）

Selecta.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))ASDEC 
FROM(Selecta.userper,a.tel,a.standfee,b.telfeedate,b.factration
FROMTELFEESTANDa,TELFEEb
Wherea.tel=b.telfax)a

GROUPBYa.userper,a.tel,a.standfee,TO_CHAR(a.telfeedate,'yyyy') 
说明：四表联查问题
select*fromaleftinnerjoinbona.a=b.brightinnerjoincona.a=c.cinnerjoindona.a=d.dwhere.....
说明：得到表中最小的未使用的ID号

Select(CASEWHENEXISTS(Select*FROMHandlebWhereb.HandleID=1)THENMIN(HandleID)+1ELSE1END)asHandleIDFROMHandleWhereNOTHandleIDIN(Selecta.HandleID-1FROMHandlea) 
一个SQL语句的问题:行列转换
select*fromv_temp
上面的视图结果如下:
user_namerole_name
-------------------------
系统管理员管理员
feng管理员
feng一般用户
test一般用户
想把结果变成这样:
user_namerole_name
---------------------------
系统管理员管理员
feng管理员,一般用户
test一般用户
===================
createtablea_test(namevarchar(20),role2varchar(20))

insertintoa_testvalues('李','管理员') 

insertintoa_testvalues('张','管理员') 

insertintoa_testvalues('张','一般用户') 

insertintoa_testvalues('常','一般用户') 
createfunctionjoin_str(@contentvarchar(100))
returnsvarchar(2000)

as 
begin
declare@strvarchar(2000)

set@str='' 

select@str=@str+','+rtrim(role2)froma_testwhere[name]=@content 
select@str=right(@str,len(@str)-1)

return@str 
end
go
--调用：
select[name],dbo.join_str([name])role2froma_testgroupby[name]
--selectdistinctname,dbo.uf_test(name)froma_test
快速比较结构相同的两表
结构相同的两表，一表有记录3万条左右，一表有记录2万条左右，我怎样快速查找两表的不同记录？
============================
给你一个测试方法，从northwind中的orders表取数据。
select*inton1fromorders
select*inton2fromorders
select*fromn1
select*fromn2
--添加主键，然后修改n1中若干字段的若干条
altertablen1addconstraintpk_n1_idprimarykey(OrderID)
altertablen2addconstraintpk_n2_idprimarykey(OrderID)
selectorderIDfrom(select*fromn1unionselect*fromn2)agroupbyorderIDhavingcount(*)>1
应该可以，而且将不同的记录的ID显示出来。
下面的适用于双方记录一样的情况，

select*fromn1whereorderidin(selectorderIDfrom(select*fromn1unionselect*fromn2)agroupbyorderIDhavingcount(*)>1) 
至于双方互不存在的记录是比较好处理的
--删除n1,n2中若干条记录

deletefromn1whereorderIDin('10728','10730') 

deletefromn2whereorderIDin('11000','11001') 
--*************************************************************
--双方都有该记录却不完全相同

select*fromn1whereorderidin(selectorderIDfrom(select*fromn1unionselect*fromn2)agroupbyorderIDhavingcount(*)>1) 
union
--n2中存在但在n1中不存的在10728,10730

select*fromn1whereorderIDnotin(selectorderIDfromn2) 
union
--n1中存在但在n2中不存的在11000,11001

select*fromn2whereorderIDnotin(selectorderIDfromn1) 
四种方法取表里n到m条纪录：
1.
selecttopm*into临时表(或表变量)fromtablenameorderbycolumnname--将topm笔插入

setrowcountn 
select*from表变量orderbycolumnnamedesc
2.
selecttopn*from(selecttopm*fromtablenameorderbycolumnname)aorderbycolumnnamedesc
3.如果tablename里没有其他identity列，那么：

selectidentity(int)id0,*into#tempfromtablename 
取n到m条的语句为：
select*from#tempwhereid0>=nandid0<=m

如果你在执行selectidentity(int)id0,*into#tempfromtablename这条语句的时候报错,那是因为你的DB中间的selectinto/bulkcopy属性没有打开要先执行： 

execsp_dboption你的DB名字,'selectinto/bulkcopy',true 
4.如果表里有identity属性，那么简单：
select*fromtablenamewhereidentitycolbetweennandm
如何删除一个表中重复的记录？

createtablea_dist(idint,namevarchar(20)) 

insertintoa_distvalues(1,'abc') 

insertintoa_distvalues(1,'abc') 

insertintoa_distvalues(1,'abc') 

insertintoa_distvalues(1,'abc') 

execup_distinct'a_dist','id' 
select*froma_dist
createprocedureup_distinct(@t_namevarchar(30),@f_keyvarchar(30))
--f_key表示是分组字段﹐即主键字段

as 
begin
declare@maxinteger,@idvarchar(30),@sqlvarchar(7999),@typeinteger

select@sql='declarecur_rowscursorforselect'+@f_key+',count(*)from'+@t_name+'groupby'+@f_key+'havingcount(*)>1' 
exec(@sql)
opencur_rows
fetchcur_rowsinto@id,@max

while@@fetch_status=0 
begin
select@max=@max-1

setrowcount@max 
select@type=xtypefromsyscolumnswhereid=object_id(@t_name)andname=@f_key

if@type=56 

select@sql='deletefrom'+@t_name+'where'+@f_key+'='+@id 

if@type=167 

select@sql='deletefrom'+@t_name+'where'+@f_key+'='+''''+@id+'''' 
exec(@sql)
fetchcur_rowsinto@id,@max
end
closecur_rows
deallocatecur_rows

setrowcount0 
end
select*fromsystypes

select*fromsyscolumnswhereid=object_id('a_dist') 
查询数据的最大排序问题（只能用一条语句写）

CreateTABLEhard(quchar(11),cochar(11),jenumeric(3,0)) 

insertintohardvalues('A','1',3) 

insertintohardvalues('A','2',4) 

insertintohardvalues('A','4',2) 

insertintohardvalues('A','6',9) 

insertintohardvalues('B','1',4) 

insertintohardvalues('B','2',5) 

insertintohardvalues('B','3',6) 

insertintohardvalues('C','3',4) 

insertintohardvalues('C','6',7) 

insertintohardvalues('C','2',3) 
要求查询出来的结果如下：
qucoje
---------------------------
A69
A24
B36
B25
C67
C34
就是要按qu分组，每组中取je最大的前2位！！
而且只能用一句sql语句！！！

select*fromhardawherejein(selecttop2jefromhardbwherea.qu=b.quorderbyje) 
求删除重复记录的sql语句？
怎样把具有相同字段的纪录删除，只留下一条。
例如，表test里有id,name字段
如果有name相同的记录只留下一条，其余的删除。
name的内容不定，相同的记录数不定。
有没有这样的sql语句？
==============================
A:一个完整的解决方案：
将重复的记录记入temp1表:
select[标志字段id],count(*)intotemp1from[表名]
groupby[标志字段id]
havingcount(*)>1
2、将不重复的记录记入temp1表:
inserttemp1select[标志字段id],count(*)from[表名]groupby[标志字段id]havingcount(*)=1
3、作一个包含所有不重复记录的表：

select*intotemp2from[表名]where标志字段idin(select标志字段idfromtemp1) 
4、删除重复表:
delete[表名]
5、恢复表：
insert[表名]select*fromtemp2
6、删除临时表:
droptabletemp1
droptabletemp2
================================
B:

createtablea_dist(idint,namevarchar(20)) 

insertintoa_distvalues(1,'abc') 

insertintoa_distvalues(1,'abc') 

insertintoa_distvalues(1,'abc') 

insertintoa_distvalues(1,'abc') 

execup_distinct'a_dist','id' 
select*froma_dist
createprocedureup_distinct(@t_namevarchar(30),@f_keyvarchar(30))
--f_key表示是分组字段﹐即主键字段

as 
begin
declare@maxinteger,@idvarchar(30),@sqlvarchar(7999),@typeinteger

select@sql='declarecur_rowscursorforselect'+@f_key+',count(*)from'+@t_name+'groupby'+@f_key+'havingcount(*)>1' 
exec(@sql)
opencur_rows
fetchcur_rowsinto@id,@max

while@@fetch_status=0 
begin
select@max=@max-1

setrowcount@max 
select@type=xtypefromsyscolumnswhereid=object_id(@t_name)andname=@f_key

if@type=56 

select@sql='deletefrom'+@t_name+'where'+@f_key+'='+@id 

if@type=167 

select@sql='deletefrom'+@t_name+'where'+@f_key+'='+''''+@id+'''' 
exec(@sql)
fetchcur_rowsinto@id,@max
end
closecur_rows
deallocatecur_rows

setrowcount0 
end
select*fromsystypes

select*fromsyscolumnswhereid=object_id('a_dist') 
行列转换--普通
假设有张学生成绩表(CJ)如下
NameSubjectResult
张三语文80
张三数学90
张三物理85
李四语文85
李四数学92
李四物理82
想变成
姓名语文数学物理
张三809085
李四859282
declare@sqlvarchar(4000)

set@sql='selectName' 

select@sql=@sql+',sum(caseSubjectwhen'''+Subject+'''thenResultend)['+Subject+']' 

from(selectdistinctSubjectfromCJ)asa 

select@sql=@sql+'fromtestgroupbyname' 
exec(@sql)
行列转换--合并
有表A,
idpid
11
12
13
21
22
31
如何化成表B:
idpid
11,2,3
21,2
31
创建一个合并的函数

createfunctionfmerg(@idint) 
returnsvarchar(8000)

as 
begin
declare@strvarchar(8000)

set@str='' 

select@str=@str+','+cast(pidasvarchar)from表Awhereid=@id 

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

return(@str) 
End
go
--调用自定义函数得到结果
selectdistinctid,dbo.fmerg(id)from表A
如何取得一个数据表的所有列名
方法如下：先从SYSTEMOBJECT系统表中取得数据表的SYSTEMID,然后再SYSCOLUMN表中取得该数据表的所有列名。
SQL语句如下：

declare@objidint,@objnamechar(40) 

set@objname='tablename' 
select@objid=idfromsysobjectswhereid=object_id(@objname)

select'Column_name'=namefromsyscolumnswhereid=@objidorderbycolid 
或

Select*FROMINFORMATION_SCHEMA.COLUMNSWhereTABLE_NAME='users' 
通过SQL语句来更改用户的密码
修改别人的,需要sysadminrole

EXECsp_passwordNULL,'newpassword','User' 

如果帐号为SA执行EXECsp_passwordNULL,'newpassword',sa 
怎么判断出一个表的哪些字段不允许为空？

selectCOLUMN_NAMEfromINFORMATION_SCHEMA.COLUMNSwhereIS_NULLABLE='NO'andTABLE_NAME=tablename 
如何在数据库里找到含有相同字段的表？
a.查已知列名的情况

Selectb.nameasTableName,a.nameascolumnname 
FromsyscolumnsaINNERJOINsysobjectsb
ONa.id=b.id

ANDb.type='U' 

ANDa.name='你的字段名字' 
未知列名查所有在不同表出现过的列名
Selecto.nameAstablename,s1.nameAscolumnname
Fromsyscolumnss1,sysobjectso
Wheres1.id=o.id

Ando.type='U' 
AndExists(
Select1Fromsyscolumnss2
Wheres1.name=s2.name
Ands1.id<>s2.id
)
查询第xxx行数据
假设id是主键：
select*from(selecttopxxx*fromyourtable)aawherenotexists(select1from(selecttopxxx-1*fromyourtable)bbwhereaa.id=bb.id)
如果使用游标也是可以的
fetchabsolute[number]from[cursor_name]
行数为绝对行数
SQLServer日期计算
a.一个月的第一天
SelectDATEADD(mm,DATEDIFF(mm,0,getdate()),0)
b.本周的星期一
SelectDATEADD(wk,DATEDIFF(wk,0,getdate()),0)
c.一年的第一天
SelectDATEADD(yy,DATEDIFF(yy,0,getdate()),0)
d.季度的第一天
SelectDATEADD(qq,DATEDIFF(qq,0,getdate()),0)
e.上个月的最后一天
Selectdateadd(ms,-3,DATEADD(mm,DATEDIFF(mm,0,getdate()),0))
f.去年的最后一天
Selectdateadd(ms,-3,DATEADD(yy,DATEDIFF(yy,0,getdate()),0))
g.本月的最后一天
Selectdateadd(ms,-3,DATEADD(mm,DATEDIFF(m,0,getdate())+1,0))
h.本月的第一个星期一
selectDATEADD(wk,DATEDIFF(wk,0,
dateadd(dd,6-datepart(day,getdate()),getdate())
),0)
i.本年的最后一天
Selectdateadd(ms,-3,DATEADD(yy,DATEDIFF(yy,0,getdate())+1,0))。

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

SelectCASEIsNull(I.name,'') 

When''Then'' 

Else'*' 

EndasIsPK, 

Object_Name(A.id)ast_name, 

A.nameasc_name, 

IsNull(SubString(M.text,1,254),'')aspbc_init, 

T.nameasF_DataType, 

CASEIsNull(TYPEPROPERTY(T.name,'Scale'),'') 

WHEN''ThenCast(A.precasvarchar) 

ELSECast(A.precasvarchar)+','+Cast(A.scaleasvarchar) 

ENDasF_Scale, 

A.isnullableasF_isNullAble 

FROMSyscolumnsasA 

JOINSystypesasT 

ON(A.xType=T.xUserTypeANDA.Id=Object_id('sysobjects')) 

LEFTJOIN(SysIndexesasI 

JOINSyscolumnsasA1 

ON(I.id=A1.idandA1.id=object_id('sysobjects')and(I.status&0x800)=0x800ANDA1.colid<=I.keycnt)) 

ON(A.id=I.idANDA.name=index_col('sysobjects',I.indid,A1.colid)) 

LEFTJOINSysCommentsasM 

ON(M.id=A.cdefaultandObjectProperty(A.cdefault,'IsConstraint')=1) 
orDERBYA.ColidASC
提取数据库内所有表的字段详细说明的SQL语句
Select

(casewhena.colorder=1thend.nameelse''end)N'表名', 

a.colorderN'字段序号', 

a.nameN'字段名', 

(casewhenCOLUMNPROPERTY(a.id,a.name,'IsIdentity')=1then'√'else'' 

end)N'标识', 

(casewhen(Selectcount(*) 
FROMsysobjects

Where(namein 
(Selectname
FROMsysindexes

Where(id=a.id)AND(indidin 
(Selectindid
FROMsysindexkeys

Where(id=a.id)AND(colidin 
(Selectcolid
FROMsyscolumns
Where(id=a.id)AND(name=a.name)))))))AND

(xtype='PK'))>0then'√'else''end)N'主键', 

b.nameN'类型', 

a.lengthN'占用字节数', 

COLUMNPROPERTY(a.id,a.name,'PRECISION')asN'长度', 

isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0)asN'小数位数', 

(casewhena.isnullable=1then'√'else''end)N'允许空', 

isnull(e.text,'')N'默认值', 

isnull(g.[value],'')ASN'字段说明' 
FROMsyscolumnsa
leftjoinsystypesb
ona.xtype=b.xusertype
innerjoinsysobjectsd

ona.id=d.idandd.xtype='U'andd.name<>'dtproperties' 
leftjoinsyscommentse
ona.cdefault=e.id
leftjoinsyspropertiesg
ona.id=g.idANDa.colid=g.smallid
orderbyobject_name(a.id),a.colorder
快速获取表test的记录总数[对大容量表非常有效]
快速获取表test的记录总数:

selectrowsfromsysindexeswhereid=object_id('test')andindidin(0,1) 

update2setKHXH=(ID+1)\22行递增编号 

update[23]setid1='No.'+right('00000000'+id,6)whereidnotlike'No%'//递增 

update[23]setid1='No.'+right('00000000'+replace(id1,'No.',''),6)//补位递增 
deletefrom[1]where(id%2)=1
奇数
替换表名字段

update[1]setdomurl=replace(domurl,'Upload/Imgswf/','Upload/Photo/')wheredomurllike'%Upload/Imgswf/%' 
截位
SelectLEFT(表名,5)