10.ABAP数据库查询(三)

Page1REPORTY170DM72.TABLES:TABNA.DATA:CNTRYLIKETABNA-COUNTRY.SELECTDISTINCTCOUNTRYFROMTABNAINTO(CNTRY).WRITE:/CNTRY.ENDSELECT.SELECTDISTINCT语句使用SELECTDISTINCT语句,可以从结果集中消除重复的记录Page2SELECT使用总计功能REPORTY170DM73.TABLES:TABNA.DATA:TOTALTYPEI,MAX_SALESLIKETABNA-SALES,AVG_SALESLIKETABNA-SALES,MIN_SALESLIKETABNA-SALES.SELECTCOUNT(DISTINCTCOUNTRY)MAX(SALES)AVG(SALES)MIN(SALES)FROMTABNAINTO(TOTAL,MAX_SALES,AVG_SALES,MIN_SALES)WHERECOUNTRYSPACE.WRITE:/‘TableTABNAStatistics’,/TOTAL,‘DifferentCountries’,/8MIN_SALES,‘LowestSales’,/8AVG_SALES,‘AverageSales’,/8MAX_SALES,‘HighestSales’.Page3CONCATENATE语句REPORTY170DM74.TABLES:TABNA.PARAMETERS:WHERECL1(72)DEFAULT‘COUNTRY=‘‘USA’’’,WHERECL2(3)DEFAULT‘OR’,WHERECL3(72)DEFAULT‘COUNTRY=‘‘GB’’’.TYPE:BEGINOFITAB_RECORD,TEXT(72),ENDOFITAB_RECORD.DATA:WHERE_ITABTYPESTANDARDTABLEOFITAB_RECORDINITIALSIZE3WITHHEADERLINE.CONCATENATEWHERECL1WHERECL2WHERECL3INTOWHERE_ITAB-TEXTSEPARATEDBYSPACE.APPENDWHERE_ITAB.SELECT*FROMTABNAWHERE(WHERE_ITAB).WRITE:/TABNA-COUNTRY,TABNA-ID.ENDSELECT.CONCATENATE原字段1原字段2原字段3::原字段nINTO目标字段SEPARATEDBY常量.Page4REPORTY170DM76.PARAMETERS:TAB_NAME(5)DEFAULT‘TABNA’.DATA:TABLE_LINE(240).SELECT*FROM(TAB_NAME)INTOTABLE_LINE.WRITE:/TABLE_LINE.ENDSELECT.动态指定表名称Page5连接:为什么我们要使用它们•连接比逻辑数据库和嵌套循环更加复杂•在同一个select语句中使用不同的表Page6内连接SFLIGHTSCARRPage7内连接语法SELECT表1~字段1表1~字段2表2~字段3...INTO(目标)FROM表1INNERJOIN表2ON表1~关键字段1=表2~关键字段1AND表1~关键字段2=表2~关键字段2AND...WHERE...ENDSELECT.Page8主表SELECTscarr~carrnamesflight~carridsflight~connidsflight~fldateINTO(carrid,connid,date,carrname)FROMscarrINNERJOINsflightONscarr~carrid=sflight~carrid.WRITE:/carrid,connid,date,carrname.ENDSELECT.Page9左外连接SFLIGHTSCARRPage10左外连接语法SELECT表1~字段1表1~字段2表2~字段3...INTO(目标)FROM表1LEFTOUTERJOIN表2ON表1~关键字段1=表2~关键字段1AND表1~关键字段2=表2~关键字段2AND...WHERE...ENDSELECT.Page11OpenSQL语句语法检查对于连接语句有一些限制以保证对于SAP支持的数据库可以产生相同的结果Page12冗余LFA1BSIKPage13多于两个表的连接SELECT表1~字段1表1~字段2表2~字段3...INTO(目标)FROM表1INNERJOIN表2ON表1~关键字段1=表2~关键字段1AND表1~关键字段2=表2~关键字段2AND...)INNERJOIN表3ON表1~关键字段=表3~关键字段AND...WHERE...ENDSELECT.Page14别名SELECTA~carrnameB~carridB~connidB~fldateINTO(carrid,connid,date,carrname)FROMscarrASAINNERJOINsflightASBONscarr~carrid=sflight~carrid.WRITE:/carrid,connid,date,carrname.ENDSELECT.Page15子查询SFLIGHTSCARR哪一条航线在sflight表中没有记录?Page16子查询语法/例子SELECT*FROMscarrWHERENOTcarridIN(SELECTcarridFROMsflight).WRITE:/scarr-carrid,scarr-carrname.ENDSELECT.Page17Having语句列出Luftansa的CONNIDs,其中LUGGWEIGHT之和要小于25,0000.Page18Having语句语法/例子SELECTcarridconnidCOUNT(*)SUM(luggweight)INTO(carrid,connid,count,sum_weight)FROMsbookWHEREcarrid='LH'GROUPBYcarridconnidHAVINGSUM(luggweight)25.WRITE:/carrid,connid,count,sum_weight.ENDSELECT.