一个简单的去除重复字段的SQL查询语句

1、一个简单的去除重复字段的查询语句分类：我的知识库今天公司里让程序修改一个程序，需要去掉输出中的重复楼盘名称，一开始想到的是，但死路不通，只能改道，最终偶在网上找到了一个思路，修改了一下就有了。先看所有记录（这是我在测试的数据库里做的）：select*fruJTLtlidbN：dineotherlnfo1囲园园园洲洲国阁岸岸阁鬲花T-L花花绿绿明天右畜天明-盛盛盛盛礬潸日润盛盛润昌昌昌昌名名明商龙龙甫明ddd27善;d13，我们这样来消除重复项:selectbNiime,idfromtlwhereidm(selectm：ax(.id.)f工匚untlgroupbybN：iJTLehavingco

2、uiit(*)11.select*fromtable1asawherenotexists(select1fromtable1wherelogID=a.LogIDandIDa.ID)2.最近做一个数据库的数据导入功能，发现联合主键约束导致不能导入，原因是源表中有重复数据，但是源表中又没有主键，很是麻烦。经过努力终于解决了，现在就来和大家分享一下，有更好的办法的可以相互交流。有重复数据主要有一下几种情况：1.存在两条完全相同的纪录这是最简单的一种情况，用关键字distinct就可以去掉example：selectdistinct*fromtable(表名)where(条件)2存在部分字段相同的纪录

3、(有主键id即唯一键)如果是这种情况的话用distinct是过滤不了的，这就要用到主键id的唯一性特点及groupby分组example:select*fromtablewhereidin(selectmax(id)fromtablegroupby去除重复的字段名列表,)3没有唯一键ID这种情况我觉得最复杂，目前我只会一种方法，有那位知道其他方法的可以留言，交流一下：example:selectidentity(int1,1)asid,*intonewtable(临时表)fromtableselect*fromnewtablewhereidin(selectmax(id)fromnewtabl

4、egroupby去除重复的字段名列表,)droptablenewtable关于一个去除重复记录的sql语句2009-8-2416:33提问者：lichuanbao1234Q|悬赏分：30|浏览次数：1075次我要查询一个表中content字段相同的记录的详细信息。其中每条记录都有一个标识符state，0表示未发送，1表示已发送。我要统计所有content相同的记录的信息，包括其中已发送(state=1)的记录。请问大家看看我这样写有什么问题？selectdistinctcontent,name,push_date,total,e.total_sendedfromtbl_jingwei_push

5、a,(selectcount(*)astotal_sendedfromtbl_jingwei_pushwherestate=1andcontent=a.content)e这样查出的其他字段都是符合要求的，唯独e.total_sended的结果出问题，它显示的是表中所有state=1的记录，请问大家我要怎么改呢？问题补充：这个sql语句是不对的。表a是错误的。请大家指点迷津，我要统计content相同并且state为1的记录数目。谢谢各位。我就是想去掉重复记录并统计一下,只不过如果state=1的话，我要统计一下state=1的记录数。前提是这些记录的content是相同的。二楼回答的不对，这和

6、我写的是一样的，a表是不能在e表中用的。越2009-8-2416:57最佳答案selectdistinctcontent,name,push_date,total,sum(casestatewhen1then1when0then0end)astotal_sendedfromtbl_jingwei_push以上，希望对你有所帮助！selectdistinctcontent,name,push_date,total,e.total_sendedfromtbl_jingwei_pusha,(selectcount(*)astotal_sendedfromtbl_jingwei_pushwherest

7、ate=1ande.content=a.content)e0|评论2009-8-2416:54hrheroI五级selectdistinctcontent,name,push_date,total,e.total_sendedfromtbl_jingwei_pusha,(selectcount(*)astotal_sendedfromtbl_jingwei_pushwherestate=1andcontent=a.contentgroupbycontent)e_rx_rx、亠、厶试试这样SQLServer：Distinct和Groupby去除重复字段记录2010-10-1411:31:27|分

8、类：默认分类|标签：|字号大中小订阅重复记录有两个意义，一是完全重复的记录，也即所有字段均重复的记录二是部分关键字段重复的记录，比如Name字段重复，而其他字段不一定重复或都重复可以忽略。1、对于第一种重复，比较容易解决，使用selectdistinct*fromtableName就可以得到无重复记录的结果集。如果该表需要删除重复的记录(重复记录保留1条)，可以按以下方法删除selectdistinct*into#TmpfromtableNamedroptabletableNameselect*intotableNamefrom#Tmpdroptable#Tmp发生这种重复的原因是表设计不周产

9、生的，增加唯一索引列即可解决。2、这类重复问题通常要求保留重复记录中的第一条记录，操作方法如下假设有重复的字段为Name,Address,要求得到这两个字段唯一的结果集selectidentity(int,1,1)asautoID,*into#TmpfromtableNameselectmin(autoID)asautoIDinto#Tmp2from#TmpgroupbyNameselect*from#TmpwhereautoIDin(selectautoIDfrom#tmp2)最后一个select即得到了Name,Address不重复的结果集(但多了一个autolD字段，实际写时可以写在se

10、lect子句中省去此列)其它的数据库可以使用序列，如：createsequenceseq1;selectseq1.nextvalasautoID,*into#TmpfromtableNamezuolo:我根据上面实例得到所需要的语句为SELECTMAX(id)ASID,Prodou_id,FinalDyeFROManwelL.tblDBDdataGROUPBYProdoud,FinalDyeORDERBYid,之前一直想用Distinct来得到指定字段不重复的记录是个误区。如何写一个SQL语句，能检索出所有某个字段内容有重复的记录A:比如：NameAdressTeleYaoChina110Zh

11、angMoon110WangChina110WangChina110YaoChina110根据姓名字段检索以后，能筛选出的记录有：1，3，4，5selectb.idfromtableasb,(selectcount(field),fieldfromtablewherecount(field)2groupbyfield)asawherea.field=b.fieldSELECTidfromtbwheredcount(id,tb,name=&name&)1附:DCountFunctionSeeAlsoSpecificsYoucanusetheDCountfunctiontodeterminethe

12、numberofrecordsthatareinaspecifiedsetofrecords(adomain).UsetheDCountfunctioninVisualBasic,amacro,aqueryexpression,oracalculatedcontrol.Forexample,youcouldusetheDCountfunctioninamoduletoreturnthenumberofrecordsinanOrderstablethatcorrespondtoordersplacedonaparticulardate.DCount(expr,domain,criteria)Th

13、eDCountfunctionhasthefollowingarguments.ArgumentDescriptionexprAnexpressionthatidentifiesthefieldforwhichyouwanttocountrecords.Itcanbeastringexpressionidentifyingafieldinatableorquery,oritcanbeanexpressionthatperformsacalculationondatainthatfield.Inexpr,youcanincludethenameofafieldinatable,acontrolo

14、naform,aconstant,orafunction.Ifexprincludesafunction,itcanbeeitherbuilt-inoruser-defined,butnotanotherdomainaggregateorSQLaggregatefunction.domainAstringexpressionidentifyingthesetofrecordsthatconstitutesthedomain.Itcanbeatablenameoraquerynameforaquerythatdoesnotrequireaparameter.criteriaAnoptionals

15、tringexpressionusedtorestricttherangeofdataonwhichtheDCountfunctionisperformed.Forexample,criteriaisoftenequivalenttotheWHEREclauseinanSQLexpression,withoutthewordWHERE.Ifcriteriaisomitted,theDCountfunctionevaluatesexpragainsttheentiredomain.Anyfieldthatisincludedincriteriamustalsobeafieldindomain;o

16、therwisetheDCountfunctionreturnsaNull.RemarksUsetheDCountfunctiontocountthenumberofrecordsinadomainwhenyoudontneedtoknowtheirparticularvalues.Althoughtheexprargumentcanperformacalculationonafield,theDCountfunctionsimplytalliesthenumberofrecords.Thevalueofanycalculationperformedbyexprisunavailable.Us

17、etheDCountfunctioninacalculatedcontrolwhenyouneedtospecifycriteriatorestricttherangeofdataonwhichthefunctionisperformed.Forexample,todisplaythenumberoforderstobeshippedtoCalifornia,settheControlSourcepropertyofatextboxtothefollowingexpression:=DCount(OrderID,Orders,ShipRegion=CA)Ifyousimplywanttocou

18、ntallrecordsindomainwithoutspecifyinganyrestrictions,usetheCountfunction.TipTheCountfunctionhasbeenoptimizedtospeedcountingofrecordsinqueries.UsetheCountfunctioninaqueryexpressioninsteadoftheDCountfunction,andsetoptionalcriteriatoenforceanyrestrictionsontheresults.UsetheDCountfunctionwhenyoumustcoun

19、trecordsinadomainfromwithinacodemoduleormacro,orinacalculatedcontrol.YoucanusetheDCountfunctiontocountthenumberofrecordscontainingaparticularfieldthatisntintherecordsourceonwhichyourformorreportisbased.Forexample,youcoulddisplaythenumberofordersintheOrderstableinacalculatedcontrolonaformbasedonthePr

20、oductstable.TheDCountfunctiondoesntcountrecordsthatcontainNullvaluesinthefieldreferencedbyexprunlessexpristheasterisk(*)wildcardcharacter.Ifyouuseanasterisk,theDCountfunctioncalculatesthetotalnumberofrecords,includingthosethatcontainNullfields.ThefollowingexamplecalculatesthenumberofrecordsinanOrder

21、X=DCount(*,Orders)Ifdomainisatablewithaprimarykey,youcanalsocountthetotalnumberofrecordsbysettingexprtotheprimarykeyfield,sincetherewillneverbeaNullintheprimarykeyfield.Ifexpridentifiesmultiplefields,separatethefieldnameswithaconcatenationoperator,eitheranampersand(&)ortheadditionoperator(

22、+).Ifyouuseanampersandtoseparatethefields,theDCountfunctionreturnsthenumberofrecordscontainingdatainanyofthelistedfields.Ifyouusetheadditionoperator,theDCountfunctionreturnsonlythenumberofrecordscontainingdatainallofthelistedfields.Thefollowingexampledemonstratestheeffectsofeachoperatorwhenusedwitha

23、fieldthatcontainsdatainallrecords(ShipName)andafieldthatcontainsnodata(ShipRegion).intW=DCount(ShipName,Orders)intX=DCount(ShipRegion,Orders)intY=DCount(ShipName+ShipRegion,Orders)intZ=DCount(ShipName&ShipRegion,Orders)NoteTheampersandisthepreferredoperatorforperformingstringconcatenation.Youshoulda

24、voidusingtheadditionoperatorforanythingotherthannumericaddition,unlessyouspecificallywishtopropagateNullsthroughanexpression.Unsavedchangestorecordsindomainarentincludedwhenyouusethisfunction.IfyouwanttheDCountfunctiontobebasedonthechangedvalues,youmustfirstsavethechangesbyclickingSaveRecordontheRec

25、ordsmenu,movingthefocustoanotherrecord,orbyusingtheUpdatemethod.ExampleThefollowingfunctionreturnsthenumberofordersshippedtoaspecifiedcountryafteraspecifiedshipdate.ThedomainisanOrderstable.PublicFunctionOrdersCount(ByValstrCountryAsString,_ByValdteShipDateAsDate)AsIntegerOrdersCount=DCount(ShippedDate,Orders,_ShipCountry=&strCountry&_ANDShippedDate#&dteShipDate帮我把上面那一句怎么放入CString里面？谢谢啊，已经搞定，留给后来人吧：)strSQL.Format(SELECT*fromWeak