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