SQL嵌套查詢總結(jié)
更新時(shí)間：2008年10月03日 00:16:54 作者：
這是我寫的一個(gè)結(jié)合UINON的嵌套查詢.
將五個(gè)方面的報(bào)表放到一個(gè)臨時(shí)表里,再?gòu)呐R時(shí)表里,將所要的數(shù)據(jù)查詢出來.
IT也有一段時(shí)間了,剛開始的時(shí)候``````
的困難主要是在編程語(yǔ)言上,數(shù)組,邏輯,算法,...
這些都過來了之后,困難就上升到數(shù)據(jù)庫(kù)設(shè)計(jì)上了.
以及數(shù)據(jù)邏輯.
一個(gè)優(yōu)秀的系統(tǒng),會(huì)集成優(yōu)秀的程序和優(yōu)秀的數(shù)據(jù)庫(kù)設(shè)計(jì).
要做到這點(diǎn)得有足夠的經(jīng)驗(yàn).
這是我寫的一個(gè)結(jié)合UINON的嵌套查詢.
將五個(gè)方面的報(bào)表放到一個(gè)臨時(shí)表里,再?gòu)呐R時(shí)表里,將所要的數(shù)據(jù)查詢出來.
$sql="SELECT type , sum( yjsl ) as yjsl , sum( yysl ) as yysl, sum( jyrs ) as jyrs, sum( jycs ) as jycs
FROM (
SELECT c.mc AS
TYPE , count( d.lsh ) AS yjsl, 0 AS yysl, 0 AS jyrs, 0 AS jycs
FROM sys_dzxxb AS b, sys_jcb AS c, sys_dzyjb AS d
WHERE b.bm = c.lsh
AND d.dzlsh = b.lsh
GROUP BY c.mc
UNION SELECT c.mc AS
TYPE , 0 AS yjsl, count( e.lsh ) AS yysl, 0 AS jyrs, 0 AS jycs
FROM sys_dzxxb AS b, sys_jcb AS c, sys_dzyy AS e
WHERE b.bm = c.lsh
AND e.dzlsh = b.lsh
GROUP BY c.mc
UNION SELECT c.mc AS
TYPE , 0 AS yjsl, 0 AS yysl, count( DISTINCT e.dzlsh ) AS jyrs, 0 AS jycs
FROM sys_dzxxb AS b, sys_jcb AS c, sys_ltxxb AS e
WHERE b.bm = c.lsh
AND e.dzlsh = b.lsh
GROUP BY c.mc
UNION SELECT c.mc AS
TYPE , 0 AS yjsl, 0 AS yysl, 0 AS jyrs, count( DISTINCT e.lsh ) AS jycs
FROM sys_dzxxb AS b, sys_jcb AS c, sys_ltxxb AS e
WHERE b.bm = c.lsh
AND e.dzlsh = b.lsh
GROUP BY c.mc
) AS temptable
GROUP BY TYPE ";
分享給大家.
的困難主要是在編程語(yǔ)言上,數(shù)組,邏輯,算法,...
這些都過來了之后,困難就上升到數(shù)據(jù)庫(kù)設(shè)計(jì)上了.
以及數(shù)據(jù)邏輯.
一個(gè)優(yōu)秀的系統(tǒng),會(huì)集成優(yōu)秀的程序和優(yōu)秀的數(shù)據(jù)庫(kù)設(shè)計(jì).
要做到這點(diǎn)得有足夠的經(jīng)驗(yàn).
這是我寫的一個(gè)結(jié)合UINON的嵌套查詢.
將五個(gè)方面的報(bào)表放到一個(gè)臨時(shí)表里,再?gòu)呐R時(shí)表里,將所要的數(shù)據(jù)查詢出來.
復(fù)制代碼 代碼如下:
$sql="SELECT type , sum( yjsl ) as yjsl , sum( yysl ) as yysl, sum( jyrs ) as jyrs, sum( jycs ) as jycs
FROM (
SELECT c.mc AS
TYPE , count( d.lsh ) AS yjsl, 0 AS yysl, 0 AS jyrs, 0 AS jycs
FROM sys_dzxxb AS b, sys_jcb AS c, sys_dzyjb AS d
WHERE b.bm = c.lsh
AND d.dzlsh = b.lsh
GROUP BY c.mc
UNION SELECT c.mc AS
TYPE , 0 AS yjsl, count( e.lsh ) AS yysl, 0 AS jyrs, 0 AS jycs
FROM sys_dzxxb AS b, sys_jcb AS c, sys_dzyy AS e
WHERE b.bm = c.lsh
AND e.dzlsh = b.lsh
GROUP BY c.mc
UNION SELECT c.mc AS
TYPE , 0 AS yjsl, 0 AS yysl, count( DISTINCT e.dzlsh ) AS jyrs, 0 AS jycs
FROM sys_dzxxb AS b, sys_jcb AS c, sys_ltxxb AS e
WHERE b.bm = c.lsh
AND e.dzlsh = b.lsh
GROUP BY c.mc
UNION SELECT c.mc AS
TYPE , 0 AS yjsl, 0 AS yysl, 0 AS jyrs, count( DISTINCT e.lsh ) AS jycs
FROM sys_dzxxb AS b, sys_jcb AS c, sys_ltxxb AS e
WHERE b.bm = c.lsh
AND e.dzlsh = b.lsh
GROUP BY c.mc
) AS temptable
GROUP BY TYPE ";
分享給大家.
相關(guān)文章
快速解決openGauss數(shù)據(jù)庫(kù)pg_xlog爆滿問題
這篇文章主要介紹了openGauss數(shù)據(jù)庫(kù)pg_xlog爆滿問題解決,本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2023-04-04
在PostgreSQL中使用日期類型時(shí)一些需要注意的地方
這篇文章主要介紹了在PostgreSQL中使用日期類型時(shí)一些需要注意的地方,包括時(shí)間戳和日期轉(zhuǎn)換等方面,需要的朋友可以參考下2015-04-04
一些關(guān)于數(shù)據(jù)存儲(chǔ)和查詢優(yōu)化的想法
今天咨詢了一下高手，關(guān)于數(shù)據(jù)存儲(chǔ)和查詢的問題，最終目的就是快，大家可以適當(dāng)?shù)氖褂?/div> 2012-05-05
NoSQL在2010年風(fēng)生水起，大大小小的Web站點(diǎn)在追求高性能高可靠性方面，不由自主都選擇了NoSQL技術(shù)作為優(yōu)先考慮的方面2016-02-02
14種SQL的進(jìn)階用法分享(更高效地處理數(shù)據(jù))
在實(shí)際的數(shù)據(jù)庫(kù)使用中除了CRUD還有很多高級(jí)應(yīng)用值得學(xué)習(xí)和掌握,能夠在平時(shí)的工作中得到很多便利,這篇文章主要給大家分享介紹了14種SQL的進(jìn)階用法,通過文中介紹的方法可以更高效地處理數(shù)據(jù)庫(kù)數(shù)據(jù),需要的朋友可以參考下2024-01-01
UNION指令的目的是將兩個(gè)SQL語(yǔ)句的結(jié)果合并起來。從這個(gè)角度來看， 我們會(huì)產(chǎn)生這樣的感覺，UNION跟JOIN似乎有些許類似，因?yàn)檫@兩個(gè)指令都可以由多個(gè)表格中擷取資料。2009-07-07
數(shù)據(jù)庫(kù)運(yùn)維人員DBA工作總結(jié)
中大型公司都會(huì)有一些專攻數(shù)據(jù)庫(kù)方面的牛人,專門的職位叫做DBA,對(duì)于公司的DBA他們的價(jià)值不可小覷,只要是數(shù)據(jù)庫(kù),就有吞吐量的限制,數(shù)據(jù)庫(kù)訪問瓶頸便是自然流量增長(zhǎng)或者流量突增造成的2023-10-10
如何讓SQL運(yùn)行得更快...2007-01-01
當(dāng)數(shù)據(jù)庫(kù)別批量注入掛馬后，需要批量替換掉，可以參考下面的代碼。2009-02-02
交叉表查詢sql語(yǔ)句...2007-06-06最新評(píng)論