SQL基础查询1

1、-查询全部列-机构基本资料表所有数据信息select * from itprofile-根据top关键字返回行数-查询机构基本资料表中前一百条数据select top 100 * from itprofile-查询机构基本资料表中百分之一的数据select top 1 percent * from itprofile-去除重复行-查询资产负债表中的公司代码（不重复）select distinct companycode from cbsheet-查询部分列-更新fl期，机构代码，机构全称，机构简称selectitprofile自定义列名select updatedmte as 更新日期，com

2、panycode as 机构代 companyname as 机构名称,itprofilel as 机构简称 from itprofileselect 更新口 m=updatedatef tl5=companycode,机构名称=companyn3in6 机构简称=1tprof ilel from itprofile-case.when查询资产表在机构代码为的资产合计的值，将数据化为元selectcompanycodez reportdate,reportstyle,reportunit rpub lishdate,cbsheet4 6,newdate= (case reportunit wh

3、en '兀' then 1 when7u1 then 1000 when '万元'then 10000 when '方万'then1000000 else 1 end )*cbsheet4 6from cbsheet-where 子句-查询机构代码是80108978的信息select 更新 口 m=updatedatez tl5=companycode,机构 名称=companyn3in0,机构简称=it prof i leifrom itprofilewhere companycode= * 801089781查询更新日期大于*2010-01-

4、01'的所有数据信息select from itprofilewhere updatedate>12010-01-01 *-查询机构代码在f loooooio!到vooooiocr之间的所有数据信 息select * from itprofilewhere companycode>=110000010 * andcompanycode<= * 10000100 1-使用between.andselect * from itprofile-where 了句中使用or一在机构资料表中查询公司代码在110000010 1到7 00 00100,之间或者更新日期大于*2010

5、-04-01*的所有数据信息select * from itprofilewhere companycode>=1100000101 andcompanycode<=110000100 * or updatedate>12010-04-01' 在机构资料表中查询更新口期大于*2010-04-01*并且公司代码 小于 1 10000100 1 或者在 1 10000100 '和 110000200 1 之间 select * from itprofilewhere updatedate>12010-04-01* andcompanycode<1100

6、001001 or companycode between1 100001001 and 1 100002001select * from itprofilewhere updatedate> *2010-04-011 and(companycode<1100001001 or companycode between 1 10000100 t and t10000200 t)where子句中模糊查询陀j 匚、1 1 a 1-查询公司名称中含有'中国，的所有数据信息 select * from itprofilewhere companyname like 中国-查询公司名称

7、前两个字是 '中国，的信息select * from itprofilewhere companyname like '中国-查询公司代码前位是 ''的所有数据信息select * from itprofilewhere companycode like 1100000%1select * from itprofilewhere companycode like 1100000-查出公司代码前位是、，第七位是-5之间的数据信息第八位是所有 select * from itprofilewhere companycode like 11000003-5_!-查出公

8、司代码前位是'，第七位不是-5之间的数据信息第八位是所select * from itprofile-空值判断is null-机构简称为空select * from itprofile where itprofilel is null-机构简称不为空select * from itprofile where itprofilel is not null-isnull ()应用-对于有些数字数据如果是，0，也以空值处理，时间如果是11900-01-01t也是以空值来处理/可以用isnull ()处理一查询资产负债表中资产总计(cbsheet46)为空的数据，值以空 值处理select *

9、 from cbsheet where isnull (cbsheet4 6f 0)=0 -查询资产负债表中公布日期为空的数据select top 100 updatedate,* from cbsheet whereisnull(updatedate,1900-01-01i)=,1900-0l-01f-查询资产负债表中总资产，如果总资产为空则取所有者权益合计 selectcompanycodea reportdatea reportstylef reportunitr publishdate,cbsheet4 6,cbsheet87,isnull ( (case when isnull (c

10、bsheet46, 0) =0 then nullelse cbsheet46 end),cbsheet87) from cbsheetwhere isnull (cbsheet46,0)=0 and isnull (cbsheet87,0)<>0排序将结果根据公司代码和报表日期排序。select * from cbsheet order by companycode,reportdate descselect * from cbsheet order by reportdate desc? companycode查询新准则的现金表（cfst_new）中现金及现金等价物的净增加 额

11、（cfst61）如果为空就取现金及现金等价物净增加额（cfst32） -查询结果还要包括公司代码，报表口期，公布口期，单位，只查 询报表日期为*2009-12-31*的数据按公司代码降序排列select 公司彳弋码=(20111卩any code,公布 日期=pu blishdatj扌艮 表日期=reportd3t6 -ftreportunit,现金及现金等价物的净增加额=isnull ( ( case when isnull (cfst61,0)=0 then null else cfst61 end),cfst32) from cfst_new where reportdate= * 20

12、09-12-31order by companycode desc-常用函数-sum ():求和-公司代码在110000001 1和1 10000010 1之间的资产合计的和 select sum(cbsheet46*(case reportunit when '兀'then1 when '千then 1000 when 1t then 10000 when'仃万'then 1000000 else 1 end )from cbsheet where reportdate=12009-12-31 * andcompanycode between * 10

13、000001f and 1100000201 andreportstyle= * 11f-avg ():求平均值min ():求最小值(可以是数字，字符串或时间)max ():求最大值(可以是数字，字符串或时间)count ():用于统计行数 select count (*) from cbsheet wherereportdate=12009-12-31 * and reportstyle= * 11-查询公告中共有的公司个数select count(distinct companycode) from announmt-分组查询-查询cbsheet不同报表日期的合并报表数量select r

14、eportdate ,count (*) from cbsheet group by reportdate-同一家公司同一个报告期内存在多条合并的数据select companycode,reportdate from cbsheet where reportstyle=1111 group by companycode,reportdate having count(*)>1-先将查询的数据根据需求进行分组，然后对分组后的数据进行处 理select 国家=(select columnvalue fromdatadict_columnvalue where cid = 8432 and

15、columncode=isnull(itprofile42, * 1),count (*) from itprofile group by isnull(itprofile42z 1 t) order by 国家-getdate ():取当时的时间select getdate()-day () z month () , year ():取时间值中的日期,月份,年份部分selectyear(getdate(),month(getdate()z day(getdate()一一dateadd (datepart r number, date ): 返回给扌旨定口期加上一个时间间隔后的新datetim

16、e值select dateadd(month,1,getdate ()abs ():返回指定数字的绝对值select abs (-1200)-charindex ( expressionl ,expression2 r start_location ):返回字符串expressionl在指定表达式 expression?的开始位置 selectcharindex(1/1, 1asd/fgj1) ,charindex w 1asd/fgj1,-left (character, integer),right(character, integer):返回字符串中从左边/右边开始指定个数的字符-len

17、 ( string_expression ):返回指定字符串表达式的字 符(而不是字节)数，其中不包含尾随空格select len(* absdef1)r len ( * abcdef *)zlen(1abcdef1 )-临时表select top 1 * into #a from itprofile order by companycodeselect *from #a-自定义变量declarepublishdatel datetimedeclarepublishdate2 datetimedeclarereportdate datetimedeclarereportstyle intset

18、 pub丄ishdmtel二set publishdate2=set reportdate= * *set reportstyle=11select rom cbsheet where(pub1ishd3tql=' 1 and publishdat2=' 1 orpublishdate>=publishdatel andpublishdate<=publishdate2)and (reportdate=11 or reportdate>11 andreportdate=reportdate)and (reportstyle=1 1 or reportsty0=

19、''3ndreportstyle=reportstyle )-在财务费用表(fexp )中计算每家公司个报告期的非合计项之和。 只查询-12-31的数据select companycode, reportdate, sum (f exp ) as 期初合 计_计算/ sum (fexp2) as期末合计_计算from fexp where reportdate= *2009-12-311 and replace (expenseitem, *1 , 1 1 ) <> 1 合计!group by companycode,reportdate连接查询连接创建两张表cre

20、ate table #a (a intb varchar (10)create table #b (c int,d varchar(10)insertinto#avalues(lz!af )insertinto#avalues(2,9 )insertinto#avalues(3,fc!)insertinto#avalues(4,® )insertinto#bvalues(lz!af )insertinto#bvalues(2,9 )insertinto#bvalues(5,fef )insertinto#bvalues(6,!f 1 )内连接select * from#ainner

21、join #b on a=c-外连接一-左连接select * from#aleft join#bon a=c右连接select * from#aright join#bon a=c-完全连接select * from#afull join#bon a=c查询-12-31合并的流动资产等信息(表cbsheet)股票代码和视图(dbview_getabhccode)连接查询select 股票代5=isnull (symbol, a. companycode) r 股票名 称=is null (sname, companyname),扌艮表口期=report date, 公布日 期=卩口blish

22、dmtd 扌艮表类型=(select columnvalue from datadict_columnvalue where cid = 950 and columncode=reportstyle ),单位=reportunit 7流动资产=cbsheet21,总资产 =cbsheet46z总负债=cbsheet77,归属母公司股东权益 =cbsheet8 6from cbsheet aleft join dbview_getabhccode bon a companycode=b companycodewhere repor* 2009-12-311 and reportstyle=

23、9;ll* order by股票代码-在机构资料表(itprofile)中查询被吸收合并的公司的信息， 以及吸收合并后的公司情况，-机构代码变化表(tnstitution_chg)，变化类型为-吸收合并 select 公司代码=8compmnycods 用l构全称=b companyname,机构简称=b itprofile1,合并后公司代码=ccompanycodd 合并后机构全称=ccompanyname,合并后机构简称=citprofile1from(select companycode,companycode_old frominstitution_chg where changety

24、pe=4)aleft joinitprofile bon a companycode_old=b companycodeleft joinitprofile con acompanycode=c companycode练习-查询机构公告表(itannounmt )中在'-05-31z公布的机构代码, 公告标题，公告日期，-出处，以及公布公司的机构名称，简称，注册资本(元)(itprofile21)信息select机构代码=入companycode,机构名称二companyname,机构简称=itproftle公告日期=convert(varchar(10),itannounmtl,12

25、0),注册资本(元)=itprofile21 *10000,标题=itannounmt3,出处=itannounmt4 from itannounmt aleft joinitprofile bon a companycode=b companycodewhere itannounmt1= * 2010-05-31 *实例讲解-一般财务报表三大表及指标录入情况（合并期末）。declaredeclaredatel datetimedeclaredeclaredate2 datetimedeclareqcompanycode varchar(10)declareinfo int -，按日期范围查；

26、 '，按公司代码查set declareda= * 2010-05-011set declaredate2= * 2010-05-31 *set companycode= * 80002468 *-800024 68 鄂尔多斯市国有资产投资经营有限责任公司set info=11select distinct b. companycode as 公司彳弋码,usecompanynameas公司名称,breportdate <as扌艮表口期,(case扌旨标表when0then * 1 else t v1 end)as指标表,(case资产表when0then '! else

27、 1v1 end)as资产表,(case利润表when0then 1 f else 1v * end)as利润表,(case现金表when0then 1 * else % end)as现金,r deciaredate as 公布日期fromselect companycode, reportdate, sum ( (case 扌艮表类型 when 0 then 1 else 0 end) ) as 扌旨标表)sum ( (case 扌艮表类型when 1 then 1elseend)as资产表)sum ( (case扌艮表类型when 2 then 1elseend)as利润表,sum ( (

28、case 扌艮表类型when 3 then 1elseend)as现金表)sum ( (case扌艮表类里when 4 then 1 elseend)as财费表,deciaredatefromselect companycode,enddate as reportdate,0 as 扌艮表类型/ declaredatefrom mfdata_iunionselectcompanycode,reportdate1as报表类deciaredatefrombsheet_iwherereportstyle= *11*unionselectcompanycode,reportdate,2as报表类型,d

29、eciaredatefrominst_iwherereportstyle= *11*unionselectcompanycode,reportdate,3as报表类型，deciaredatefrom cfst_iwhere reportstyle= * 11)agroup by companycode,reportdate,deciaredate)b left join select distinct usecompanycodez usecompanyname from dbview_companycode_allwhere len(usecompanycode)=8)con b compa

30、nycode=c usecompanycodewhere (info= * 1t and declaredate> = declaredatel and declaredate<=declaredate2)or (info=121 and b companycode = companycode)order by b companycode,b reportdatedesc,deciaredate desc组合查询与子查询sql-联合查询-查询一般企业资产负债表，一般企业利润分配表，一般企业现金流量表，一般企业主要财务指标这四个表中-companycode,reportdate,be

31、gindate ,reportstyle,d eclaredate, updatedate, reportunit,表名，id 等组合信'息select companycode,reportdatea reportdate asbegindate ,reportstyle,declaredatez updatedate, rep ortunit,表名一般企业资产负债表j id as流水号from bsheet_iunionselectcompanycode,reportdate,begindate ,reportstyle,dec laredate, updatedate, repor

32、tunit,1 一般企业利i闰分配表j id as流水号from inst_iunionselectcompanycode,reportdate,begindate r reportstyle,dec laredate, updatedate, reportunit，表名=! 一般企业现金济i 量表-id as流水号from cfst_iunionselect companycode,enddate as reportdateabegindate , * 111 asreportstyle, declareda te, up datedate,'万兀'reportunit r表

33、名一般企业主要财务指标1 r mfdata_i_id as 流水号from mfdata_i-查询表cbsheet中所有上市公司的合并报表select top 100 * from cbsheet where companycode in(select companycode from dbview_getabhccodewhere symbol is not null) and repor-查询在新资产负债表(cbshe0t_new)年年报中资产最大的公司(取合并报表数据)select 公司彳弋码=companycode/ 公告 fl ffi=publishdate,扌艮 表日期=repor

34、tdate,报表类型=reportstyle,单位 =reportunit,资产总计=cbsheet 4 6 from cbsheet_new awhere reportdate =12009-12-311 and reportstyle=111 andexists (select 1 from cbsheet_new wherereportdate=12009-12-311 and reportstyle=111 * and(case reportunit when '兀'then 1 when '千兀，then 1000 when '万元'then

35、10000 when '百万，then 1000000 else 1 end )*cbsheet46 >(case a. reportunit when '兀'then 1 when '千兀 t then 1000 when '万元'then 10000 when 1 d) t then 1000000 else 1 end )*a cbshqet46)-查询在机构资料表(ttprofi丄e)上市公司的信息，假设在itprofile直接无法判断是否是上市公司select更新日期-updatedate,机构代码=companycode,机构全

36、称=companyname,机构简称=itprofile1from itprofile awhere exists (select 1 from dbview_getabhccode where companycode=a.companycode and symbo丄 is not null) order by机构代码-查询机构资料表中的公司代码(上市的为股票代码)，公司全称, 公司简称和更新h期select isnull (sym bola company code) as 股票代 companyname as公司全称名称ttprof i丄el as公司简称/ updatedate as更新

37、日期from itprofile a,(select companycode,symbol fromdbview_getabhccode )bwhere a companycode=b companycode型是合并记录，报表日期为'-12-3v-的总资产大于上市公司平均总资产的记录的公司代码，公告日期, 报表类型，总资产等相关的信息。select 公司代码=人.companycode,公告日mi=publishdate, 报表r期=reportdate,报表类型=reportstylez单位 二reportunit,总资产=cbsheet46from cbsheet_new alef

38、t join dbview_getabhccode bon a companycode=b companycodewhere symbo丄 is not null and reportdate= *2009-12-31 * and reportstyle= * 111group bya.companycode,publishdatea reportdate,reportstyle,reportunit,cbsheet4 6having cbsheet46* (case reportunit when '兀' then 1when '千元'then 1000 when '万元，then 10000when '白万'then 1000000 else 1 end)> (select avg (cbshe0ti4 6* (case r