剛剛開發(fā)需求寫了個SQL，記個筆記，學(xué)習(xí)下關(guān)于數(shù)據(jù)庫的多表合并查詢的用法

select t.* from A t

UNION ALL/UNION/Intersect/MINUS

select s.* from B s;

UNION ALL

使用UNION ALL，表示取A、B的合集,不過濾重復(fù)的數(shù)據(jù)行

UNION

使用UNION，會將結(jié)果集A和結(jié)果集B進(jìn)行UNION ALL運(yùn)算,然后取兩者交集的余集作為結(jié)果集

Intersect

使用Intersect,會將結(jié)果集A和結(jié)果集B進(jìn)行UNION ALL運(yùn)算,然后兩者之間的集交集作為結(jié)果集和UNION剛好相反

MINUS

使用MINUS,取結(jié)果集A減去結(jié)果集B留下的差集,注:如果結(jié)果集A小于等于結(jié)果集B,返回空結(jié)果集.

好啦，下面進(jìn)入實(shí)戰(zhàn)階段，我就直接將我寫的SQL貼出來吧

select a.*

from (select t.c_fund_account_name as "fundAccountNo", --基金賬號

tfp.project_code as "projectCode", --項(xiàng)目編號

tfp.project_name as "projectName", --項(xiàng)目名稱

tfp.project_shortname as "projectShortName", --項(xiàng)目簡稱

c.c_fund_name as "fundName", --基金產(chǎn)品名稱

c.c_fund_code as "fundCode", --基金產(chǎn)品代碼

nvl(thold.subsistAssetsShare, 0) as "subsisAssetsShare", --份額(家族)

to_char(thold.updateDate, 'yyyy-MM-dd') as "updateDate", --日期

nvl(c.c_current_share, 0) as "currentShare", --份額(基金網(wǎng)站)

to_char(c.d_date, 'yyyy-MM-dd') as "dateDate", --日期

(nvl(thold.subsistAssetsShare, 0) - nvl(c.c_current_share, 0)) as "diffValue", --差值

CAST((CASE

WHEN (nvl(thold.subsistAssetsShare, 0) -

nvl(c.c_current_share, 0)) = 0 THEN

'1'

WHEN (nvl(thold.subsistAssetsShare, 0) -

nvl(c.c_current_share, 0)) <> 0 THEN

'0'

END) as nvarchar2(2)) as "identical" --是否一致

from t_fund_account t

inner join (select fhs.*,

row_number() over(partition by fhs.c_fund_account_no, fhs.c_project_code order by fhs.d_date desc) rn

from td_fund_holding_share fhs) c

on t.c_fund_account_name = c.c_fund_account_no

and t.c_project_code = c.c_project_code

LEFT JOIN (SELECT tha.project_code as projectCode,

sum(tha.current_share) as subsistAssetsShare, -- 持有份額

sum(tha.current_cost) as currentCost, --當(dāng)前成本

sum(tha.accumulated_profit) as accumulatedProfit, --累積利潤

max(tha.update_time) as updateDate

FROM t_hold_assets tha

left join t_polling_product p

on tha.c_product_code = p.c_product_code

WHERE 1 = 1

and tha.delete_flag = '0'

and p.c_stock_type_level1 = '0'

and p.c_stock_type_level2 = '01'

GROUP BY tha.project_code) thold

on thold.projectCode = t.c_project_code

left join t_family_project tfp

on tfp.project_code = t.c_project_code

and tfp.delete_flag = '0'

where rn = 1

AND t.c_fund_account_type = '1' --基金賬戶

AND t.delete_flag = '0'

UNION ALL

select t.c_fund_account_name as "fundAccountNo", --基金賬號

tfp.project_code as "projectCode", --項(xiàng)目編號

tfp.project_name as "projectName", --項(xiàng)目名稱

tfp.project_shortname as "projectShortName", --項(xiàng)目簡稱

CAST('' as nvarchar2(50)) as "fundName", --基金產(chǎn)品名稱

CAST('' as nvarchar2(50)) as "fundCode", --基金產(chǎn)品代碼

nvl(thold.subsistAssetsShare, 0) as "subsisAssetsShare", --份額(家族)

to_char(thold.updateDate, 'yyyy-MM-dd') as "updateDate", --日期

to_number(nvl('', 0)) as "currentShare", --份額(基金網(wǎng)站)

to_char(CAST('' as nvarchar2(50)), 'yyyy-MM-dd') as "dateDate", --日期

nvl(thold.subsistAssetsShare, 0) - nvl('', 0) as "diffValue", --差值

CAST((CASE

WHEN (nvl(thold.subsistAssetsShare, 0) - nvl('', 0)) = 0 THEN

'1'

WHEN (nvl(thold.subsistAssetsShare, 0) - nvl('', 0)) <> 0 THEN

'0'

END) as nvarchar2(2)) as "identical" --是否一致

from t_fund_account t

LEFT JOIN (SELECT tha.project_code as projectCode,

sum(tha.current_share) as subsistAssetsShare, -- 持有份額

max(tha.update_time) as updateDate

FROM t_hold_assets tha

left join t_polling_product p

on tha.c_product_code = p.c_product_code

WHERE 1 = 1

and tha.delete_flag = '0'

and p.c_stock_type_level1 = '0'

and p.c_stock_type_level2 = '01'

GROUP BY tha.project_code) thold

on thold.projectCode = t.c_project_code

left join t_family_project tfp

on t.c_project_code = tfp.project_code

and tfp.delete_flag = '0'

where t.c_fund_account_name not in

(select td.c_fund_account_no from td_fund_holding_share td)

and t.c_fund_account_type = '1'

and t.delete_flag = '0') a

order by a."diffValue" desc, a."projectCode" desc

上面sql具體意思是：查詢出基金信息，A和基金對比先查詢其中有的數(shù)據(jù)，再union all A有基金沒有的數(shù)據(jù)，一起取個并集。OK，需求完成

哈哈哈哈，其實(shí)只要你SQL寫得牛逼，然后了解下業(yè)務(wù)流程，什么都好說哈哈哈

哦對了，最后啰嗦一句。 對于這些并集計(jì)算之后，需要排序 則語法為：

select t.* from (語句1 union all 語句2)

t order by t.id;

SQL多表合并查詢結(jié)果

兩表合并查詢,并同時展示及分頁SELECT a.* FROM ( ( SELECT punycode, `domain`, 'Success' AS state, add_time, AS refun ...

Oracle 表復(fù)雜查詢之多表合并查詢

轉(zhuǎn)自:https://www.cnblogs.com/GreenLeaves/p/6635887.html 本文使用到的是oracle數(shù)據(jù)庫scott方案所帶的表,scott是oracle數(shù)據(jù)庫自帶的 ...

mysql——多表——合并查詢結(jié)果

合并查詢結(jié)果 合并查詢結(jié)果 是將多個select語句的查詢結(jié)果合并到一起 union關(guān)鍵字,數(shù)據(jù)庫會將所有的查詢結(jié)果合并到一起,然后除掉相同的記錄: union all關(guān)鍵字,只是簡單的合并到一起 前 ...

ORACLE數(shù)據(jù)庫多表關(guān)聯(lián)查詢效率問題解決方案

最近在做項(xiàng)目中遇到多表關(guān)聯(lián)查詢排序的效率問題(5張以上40W+數(shù)據(jù)的表),查詢一次大概要20多秒,經(jīng)過一番苦思冥想,處理方案如下: 1.軟件設(shè)計(jì)初期,需要一對一關(guān)聯(lián)的表應(yīng)該設(shè)計(jì)在一張大表里,這樣雖然字 ...

一條sql&comma;有分頁，表合并查詢，多表連接，用于oracle數(shù)據(jù)庫

SELECT * FROM ( SELECT TT.*,ROWNUM RN FROM ( SELECT A.CASE_ID AS TREATID, A.TYPE AS TYPE, B.CONTENT ...

ORACLE EBS常用表及查詢語句(最終整理版)

建議去看參考二 參考一: ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?? call fnd_global.APPS_INITI ...

SQL 兩張結(jié)構(gòu)一樣的表合并查詢 &period;

select * from table1 union all select * from table2 union all 是所有的都顯示出來: select * from table1 union ...

Oracle數(shù)據(jù)庫鎖表的查詢方法以及解鎖的方法

1,鎖表語句簡單查詢方法 ? select t2.username,t2.sid,t2.serial#,t2.logon_time from v$locked_object t1,v$session ...

oracle數(shù)據(jù)庫數(shù)據(jù)庫表空間查詢及擴(kuò)充

1.查詢表空間,及表空間的大小 SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size FROM dba_tabl ...

隨機(jī)推薦

SharePoint 2013必備組件離線包安裝：AppFabric無法安裝問題解決

由于沒有網(wǎng)絡(luò),無法使用sharepoint2013的安裝必備軟件的在線下載向?qū)О惭b,當(dāng)要安裝 SharePoint 2013 的服務(wù)器與 Internet 隔離時,通常需要從脫機(jī)位置安裝必備組件.即使 ...

SharePoint 2010 ——自定義上傳頁面與多文件上傳解決方案

最近項(xiàng)目遇到一個很麻煩的問題,原以為很容易解決,結(jié)果搞了那么久,先開個頭,再慢慢寫 SharePoint 2010 ——自定義上傳頁面與多文件上傳解決方案 1.創(chuàng)建Sharepoint空白項(xiàng)目,創(chuàng)建應(yīng) ...

GridView 無數(shù)據(jù)時，綁定提示

private void BindData() { DataTable dt = DAO.RunSQLReturnDt(this.getsql()); int dtcount = dt.Rows.Co ...

stack around the variable &OpenCurlyDoubleQuote; ” was corrupted

用scanf格式控制不當(dāng)經(jīng)常發(fā)生此錯誤. 如 short int a=10;? scanf("%d",&a); 應(yīng)該是%hd; 一般是越界引起的. 參看:http://bl ...

js isArray小結(jié)

原文:[轉(zhuǎn)載]js isArray小結(jié) 在日常開發(fā)中,我們經(jīng)常需要判斷某個對象是否是數(shù)組類型的,在js中檢測對象類型的常見的方法有幾種: 1.typeof操作符.對于Function.String.N ...

老李分享：《Linux Shell腳本攻略》 要點(diǎn)(七)

老李分享: 要點(diǎn)(七) ? 1.顯示給定文件夾下的文件的磁盤適用情況 [root@localhost program_test]# du -a -h ./ ...

POJ--3172 Scales &lpar;DFS 大容量背包 C&plus;&plus;&rpar;

Scales Time Limit:?1000MS ? Memory Limit:?65536K Total Submissions:?3148 ? Accepted:?851 Description ...

微信小程序語音識別服務(wù)搭建全過程解析(項(xiàng)目開源在github)

silk v3錄音轉(zhuǎn)olami語音識別和語義處理的api服務(wù)(ubuntu16.04服務(wù)器上實(shí)現(xiàn)) ## 重要的寫在前面 重要事項(xiàng)一: 目前本文中提到的API已支持微信小程序錄音文件格式:silk v ...

Mac 下 搭建 svn 服務(wù)器

Mac自帶了svn服務(wù)端和客戶端,所以只需要簡單配置一下就可以使用. 1.創(chuàng)建svn repository svnadmin?create?/Users/gaohf/svn/repository 2. ...

安裝JDK，配置環(huán)境變量

計(jì)算機(jī)(右鍵)-屬性-高級系統(tǒng)設(shè)置-環(huán)境變量1.新建系統(tǒng)變量 : JAVA_HOMEC:\Program Files (x86)\Java\jdk1.6.0_10(你的JDK安裝路徑)2.在系統(tǒng)變量p ...

總結(jié)

以上是生活随笔為你收集整理的oracle 查询两张表合并,oracle的多表合并查询-工作心得的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。