SQL基礎(chǔ)查詢(xún)1

1、-查詢(xún)?nèi)苛?機(jī)構(gòu)基本資料表所有數(shù)據(jù)信息select * from itprofile-根據(jù)top關(guān)鍵字返回行數(shù)-查詢(xún)機(jī)構(gòu)基本資料表中前一百條數(shù)據(jù)select top 100 * from itprofile-查詢(xún)機(jī)構(gòu)基本資料表中百分之一的數(shù)據(jù)select top 1 percent * from itprofile-去除重復(fù)行-查詢(xún)資產(chǎn)負(fù)債表中的公司代碼（不重復(fù)）select distinct companycode from cbsheet-查詢(xún)部分列-更新fl期，機(jī)構(gòu)代碼，機(jī)構(gòu)全稱(chēng)，機(jī)構(gòu)簡(jiǎn)稱(chēng)selectitprofile自定義列名select updatedmte as 更新日期，com

2、panycode as 機(jī)構(gòu)代 companyname as 機(jī)構(gòu)名稱(chēng),itprofilel as 機(jī)構(gòu)簡(jiǎn)稱(chēng) from itprofileselect 更新口 m=updatedatef tl5=companycode,機(jī)構(gòu)名稱(chēng)=companyn3in6 機(jī)構(gòu)簡(jiǎn)稱(chēng)=1tprof ilel from itprofile-case.when查詢(xún)資產(chǎn)表在機(jī)構(gòu)代碼為的資產(chǎn)合計(jì)的值，將數(shù)據(jù)化為元selectcompanycodez reportdate,reportstyle,reportunit rpub lishdate,cbsheet4 6,newdate= (case reportunit wh

3、en '兀' then 1 when7u1 then 1000 when '萬(wàn)元'then 10000 when '方萬(wàn)'then1000000 else 1 end )*cbsheet4 6from cbsheet-where 子句-查詢(xún)機(jī)構(gòu)代碼是80108978的信息select 更新 口 m=updatedatez tl5=companycode,機(jī)構(gòu) 名稱(chēng)=companyn3in0,機(jī)構(gòu)簡(jiǎn)稱(chēng)=it prof i leifrom itprofilewhere companycode= * 801089781查詢(xún)更新日期大于*2010-01-

4、01'的所有數(shù)據(jù)信息select from itprofilewhere updatedate>12010-01-01 *-查詢(xún)機(jī)構(gòu)代碼在f loooooio!到vooooiocr之間的所有數(shù)據(jù)信 息select * from itprofilewhere companycode>=110000010 * andcompanycode<= * 10000100 1-使用between.andselect * from itprofile-where 了句中使用or一在機(jī)構(gòu)資料表中查詢(xún)公司代碼在110000010 1到7 00 00100,之間或者更新日期大于*2010

5、-04-01*的所有數(shù)據(jù)信息select * from itprofilewhere companycode>=1100000101 andcompanycode<=110000100 * or updatedate>12010-04-01' 在機(jī)構(gòu)資料表中查詢(xún)更新口期大于*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子句中模糊查詢(xún)陀j 匚、1 1 a 1-查詢(xún)公司名稱(chēng)中含有'中國(guó)，的所有數(shù)據(jù)信息 select * from itprofilewhere companyname like 中國(guó)-查詢(xún)公司名稱(chēng)

7、前兩個(gè)字是 '中國(guó)，的信息select * from itprofilewhere companyname like '中國(guó)-查詢(xún)公司代碼前位是 ''的所有數(shù)據(jù)信息select * from itprofilewhere companycode like 1100000%1select * from itprofilewhere companycode like 1100000-查出公司代碼前位是、，第七位是-5之間的數(shù)據(jù)信息第八位是所有 select * from itprofilewhere companycode like 11000003-5_!-查出公

8、司代碼前位是'，第七位不是-5之間的數(shù)據(jù)信息第八位是所select * from itprofile-空值判斷is null-機(jī)構(gòu)簡(jiǎn)稱(chēng)為空select * from itprofile where itprofilel is null-機(jī)構(gòu)簡(jiǎn)稱(chēng)不為空select * from itprofile where itprofilel is not null-isnull ()應(yīng)用-對(duì)于有些數(shù)字?jǐn)?shù)據(jù)如果是，0，也以空值處理，時(shí)間如果是11900-01-01t也是以空值來(lái)處理/可以用isnull ()處理一查詢(xún)資產(chǎn)負(fù)債表中資產(chǎn)總計(jì)(cbsheet46)為空的數(shù)據(jù)，值以空 值處理select *

9、 from cbsheet where isnull (cbsheet4 6f 0)=0 -查詢(xún)資產(chǎn)負(fù)債表中公布日期為空的數(shù)據(jù)select top 100 updatedate,* from cbsheet whereisnull(updatedate,1900-01-01i)=,1900-0l-01f-查詢(xún)資產(chǎn)負(fù)債表中總資產(chǎn)，如果總資產(chǎn)為空則取所有者權(quán)益合計(jì) 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排序?qū)⒔Y(jié)果根據(jù)公司代碼和報(bào)表日期排序。select * from cbsheet order by companycode,reportdate descselect * from cbsheet order by reportdate desc? companycode查詢(xún)新準(zhǔn)則的現(xiàn)金表（cfst_new）中現(xiàn)金及現(xiàn)金等價(jià)物的凈增加 額

11、（cfst61）如果為空就取現(xiàn)金及現(xiàn)金等價(jià)物凈增加額（cfst32） -查詢(xún)結(jié)果還要包括公司代碼，報(bào)表口期，公布口期，單位，只查 詢(xún)報(bào)表日期為*2009-12-31*的數(shù)據(jù)按公司代碼降序排列select 公司彳弋碼=(20111卩any code,公布 日期=pu blishdatj扌艮 表日期=reportd3t6 -ftreportunit,現(xiàn)金及現(xiàn)金等價(jià)物的凈增加額=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-常用函數(shù)-sum ():求和-公司代碼在110000001 1和1 10000010 1之間的資產(chǎn)合計(jì)的和 select sum(cbsheet46*(case reportunit when '兀'then1 when '千then 1000 when 1t then 10000 when'仃萬(wàn)'then 1000000 else 1 end )from cbsheet where reportdate=12009-12-31 * andcompanycode between * 10

13、000001f and 1100000201 andreportstyle= * 11f-avg ():求平均值min ():求最小值(可以是數(shù)字，字符串或時(shí)間)max ():求最大值(可以是數(shù)字，字符串或時(shí)間)count ():用于統(tǒng)計(jì)行數(shù) select count (*) from cbsheet wherereportdate=12009-12-31 * and reportstyle= * 11-查詢(xún)公告中共有的公司個(gè)數(shù)select count(distinct companycode) from announmt-分組查詢(xún)-查詢(xún)cbsheet不同報(bào)表日期的合并報(bào)表數(shù)量select r

14、eportdate ,count (*) from cbsheet group by reportdate-同一家公司同一個(gè)報(bào)告期內(nèi)存在多條合并的數(shù)據(jù)select companycode,reportdate from cbsheet where reportstyle=1111 group by companycode,reportdate having count(*)>1-先將查詢(xún)的數(shù)據(jù)根據(jù)需求進(jìn)行分組，然后對(duì)分組后的數(shù)據(jù)進(jìn)行處 理select 國(guó)家=(select columnvalue fromdatadict_columnvalue where cid = 8432 and

15、columncode=isnull(itprofile42, * 1),count (*) from itprofile group by isnull(itprofile42z 1 t) order by 國(guó)家-getdate ():取當(dāng)時(shí)的時(shí)間select getdate()-day () z month () , year ():取時(shí)間值中的日期,月份,年份部分selectyear(getdate(),month(getdate()z day(getdate()一一dateadd (datepart r number, date ): 返回給扌旨定口期加上一個(gè)時(shí)間間隔后的新datetim

16、e值select dateadd(month,1,getdate ()abs ():返回指定數(shù)字的絕對(duì)值select abs (-1200)-charindex ( expressionl ,expression2 r start_location ):返回字符串expressionl在指定表達(dá)式 expression?的開(kāi)始位置 selectcharindex(1/1, 1asd/fgj1) ,charindex w 1asd/fgj1,-left (character, integer),right(character, integer):返回字符串中從左邊/右邊開(kāi)始指定個(gè)數(shù)的字符-len

17、 ( string_expression ):返回指定字符串表達(dá)式的字 符(而不是字節(jié))數(shù)，其中不包含尾隨空格select len(* absdef1)r len ( * abcdef *)zlen(1abcdef1 )-臨時(shí)表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 )-在財(cái)務(wù)費(fèi)用表(fexp )中計(jì)算每家公司個(gè)報(bào)告期的非合計(jì)項(xiàng)之和。 只查詢(xún)-12-31的數(shù)據(jù)select companycode, reportdate, sum (f exp ) as 期初合 計(jì)_計(jì)算/ sum (fexp2) as期末合計(jì)_計(jì)算from fexp where reportdate= *2009-12-311 and replace (expenseitem, *1 , 1 1 ) <> 1 合計(jì)!group by companycode,reportdate連接查詢(xún)連接創(chuàng)建兩張表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 )內(nèi)連接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查詢(xún)-12-31合并的流動(dòng)資產(chǎn)等信息(表cbsheet)股票代碼和視圖(dbview_getabhccode)連接查詢(xún)select 股票代5=isnull (symbol, a. companycode) r 股票名 稱(chēng)=is null (sname, companyname),扌艮表口期=report date, 公布日 期=卩口blish

22、dmtd 扌艮表類(lèi)型=(select columnvalue from datadict_columnvalue where cid = 950 and columncode=reportstyle ),單位=reportunit 7流動(dòng)資產(chǎn)=cbsheet21,總資產(chǎn) =cbsheet46z總負(fù)債=cbsheet77,歸屬母公司股東權(quán)益 =cbsheet8 6from cbsheet aleft join dbview_getabhccode bon a companycode=b companycodewhere repor* 2009-12-311 and reportstyle=

23、9;ll* order by股票代碼-在機(jī)構(gòu)資料表(itprofile)中查詢(xún)被吸收合并的公司的信息， 以及吸收合并后的公司情況，-機(jī)構(gòu)代碼變化表(tnstitution_chg)，變化類(lèi)型為-吸收合并 select 公司代碼=8compmnycods 用l構(gòu)全稱(chēng)=b companyname,機(jī)構(gòu)簡(jiǎn)稱(chēng)=b itprofile1,合并后公司代碼=ccompanycodd 合并后機(jī)構(gòu)全稱(chēng)=ccompanyname,合并后機(jī)構(gòu)簡(jiǎn)稱(chēng)=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練習(xí)-查詢(xún)機(jī)構(gòu)公告表(itannounmt )中在'-05-31z公布的機(jī)構(gòu)代碼, 公告標(biāo)題，公告日期，-出處，以及公布公司的機(jī)構(gòu)名稱(chēng)，簡(jiǎn)稱(chēng)，注冊(cè)資本(元)(itprofile21)信息select機(jī)構(gòu)代碼=入companycode,機(jī)構(gòu)名稱(chēng)二companyname,機(jī)構(gòu)簡(jiǎn)稱(chēng)=itproftle公告日期=convert(varchar(10),itannounmtl,12

25、0),注冊(cè)資本(元)=itprofile21 *10000,標(biāo)題=itannounmt3,出處=itannounmt4 from itannounmt aleft joinitprofile bon a companycode=b companycodewhere itannounmt1= * 2010-05-31 *實(shí)例講解-一般財(cái)務(wù)報(bào)表三大表及指標(biāo)錄入情況（合并期末）。declaredeclaredatel datetimedeclaredeclaredate2 datetimedeclareqcompanycode varchar(10)declareinfo int -，按日期范圍查；

26、 '，按公司代碼查set declareda= * 2010-05-011set declaredate2= * 2010-05-31 *set companycode= * 80002468 *-800024 68 鄂爾多斯市國(guó)有資產(chǎn)投資經(jīng)營(yíng)有限責(zé)任公司set info=11select distinct b. companycode as 公司彳弋碼,usecompanynameas公司名稱(chēng),breportdate <as扌艮表口期,(case扌旨標(biāo)表when0then * 1 else t v1 end)as指標(biāo)表,(case資產(chǎn)表when0then '! else

27、 1v1 end)as資產(chǎn)表,(case利潤(rùn)表when0then 1 f else 1v * end)as利潤(rùn)表,(case現(xiàn)金表when0then 1 * else % end)as現(xiàn)金,r deciaredate as 公布日期fromselect companycode, reportdate, sum ( (case 扌艮表類(lèi)型 when 0 then 1 else 0 end) ) as 扌旨標(biāo)表)sum ( (case 扌艮表類(lèi)型when 1 then 1elseend)as資產(chǎn)表)sum ( (case扌艮表類(lèi)型when 2 then 1elseend)as利潤(rùn)表,sum ( (

28、case 扌艮表類(lèi)型when 3 then 1elseend)as現(xiàn)金表)sum ( (case扌艮表類(lèi)里when 4 then 1 elseend)as財(cái)費(fèi)表,deciaredatefromselect companycode,enddate as reportdate,0 as 扌艮表類(lèi)型/ declaredatefrom mfdata_iunionselectcompanycode,reportdate1as報(bào)表類(lèi)deciaredatefrombsheet_iwherereportstyle= *11*unionselectcompanycode,reportdate,2as報(bào)表類(lèi)型,d

29、eciaredatefrominst_iwherereportstyle= *11*unionselectcompanycode,reportdate,3as報(bào)表類(lèi)型，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組合查詢(xún)與子查詢(xún)sql-聯(lián)合查詢(xún)-查詢(xún)一般企業(yè)資產(chǎn)負(fù)債表，一般企業(yè)利潤(rùn)分配表，一般企業(yè)現(xiàn)金流量表，一般企業(yè)主要財(cái)務(wù)指標(biāo)這四個(gè)表中-companycode,reportdate,be

31、gindate ,reportstyle,d eclaredate, updatedate, reportunit,表名，id 等組合信'息select companycode,reportdatea reportdate asbegindate ,reportstyle,declaredatez updatedate, rep ortunit,表名一般企業(yè)資產(chǎn)負(fù)債表j id as流水號(hào)from bsheet_iunionselectcompanycode,reportdate,begindate ,reportstyle,dec laredate, updatedate, repor

32、tunit,1 一般企業(yè)利i閏分配表j id as流水號(hào)from inst_iunionselectcompanycode,reportdate,begindate r reportstyle,dec laredate, updatedate, reportunit，表名=! 一般企業(yè)現(xiàn)金濟(jì)i 量表-id as流水號(hào)from cfst_iunionselect companycode,enddate as reportdateabegindate , * 111 asreportstyle, declareda te, up datedate,'萬(wàn)兀'reportunit r表

33、名一般企業(yè)主要財(cái)務(wù)指標(biāo)1 r mfdata_i_id as 流水號(hào)from mfdata_i-查詢(xún)表cbsheet中所有上市公司的合并報(bào)表select top 100 * from cbsheet where companycode in(select companycode from dbview_getabhccodewhere symbol is not null) and repor-查詢(xún)?cè)谛沦Y產(chǎn)負(fù)債表(cbshe0t_new)年年報(bào)中資產(chǎn)最大的公司(取合并報(bào)表數(shù)據(jù))select 公司彳弋碼=companycode/ 公告 fl ffi=publishdate,扌艮 表日期=repor

34、tdate,報(bào)表類(lèi)型=reportstyle,單位 =reportunit,資產(chǎn)總計(jì)=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 '萬(wàn)元'then

35、10000 when '百萬(wàn)，then 1000000 else 1 end )*cbsheet46 >(case a. reportunit when '兀'then 1 when '千兀 t then 1000 when '萬(wàn)元'then 10000 when 1 d) t then 1000000 else 1 end )*a cbshqet46)-查詢(xún)?cè)跈C(jī)構(gòu)資料表(ttprofi丄e)上市公司的信息，假設(shè)在itprofile直接無(wú)法判斷是否是上市公司select更新日期-updatedate,機(jī)構(gòu)代碼=companycode,機(jī)構(gòu)全

36、稱(chēng)=companyname,機(jī)構(gòu)簡(jiǎn)稱(chēng)=itprofile1from itprofile awhere exists (select 1 from dbview_getabhccode where companycode=a.companycode and symbo丄 is not null) order by機(jī)構(gòu)代碼-查詢(xún)機(jī)構(gòu)資料表中的公司代碼(上市的為股票代碼)，公司全稱(chēng), 公司簡(jiǎn)稱(chēng)和更新h期select isnull (sym bola company code) as 股票代 companyname as公司全稱(chēng)名稱(chēng)ttprof i丄el as公司簡(jiǎn)稱(chēng)/ updatedate as更新

37、日期from itprofile a,(select companycode,symbol fromdbview_getabhccode )bwhere a companycode=b companycode型是合并記錄，報(bào)表日期為'-12-3v-的總資產(chǎn)大于上市公司平均總資產(chǎn)的記錄的公司代碼，公告日期, 報(bào)表類(lèi)型，總資產(chǎn)等相關(guān)的信息。select 公司代碼=人.companycode,公告日mi=publishdate, 報(bào)表r期=reportdate,報(bào)表類(lèi)型=reportstylez單位 二reportunit,總資產(chǎn)=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 '萬(wàn)元，then 10000when '白萬(wàn)'then 1000000 else 1 end)> (select avg (cbshe0ti4 6* (case r