SQL嵌套查詢總結(jié)
更新時間：2008年10月03日 00:16:54 作者：
這是我寫的一個結(jié)合UINON的嵌套查詢.
將五個方面的報表放到一個臨時表里,再從臨時表里,將所要的數(shù)據(jù)查詢出來.
IT也有一段時間了,剛開始的時候``````
的困難主要是在編程語言上,數(shù)組,邏輯,算法,...
這些都過來了之后,困難就上升到數(shù)據(jù)庫設(shè)計上了.
以及數(shù)據(jù)邏輯.
一個優(yōu)秀的系統(tǒng),會集成優(yōu)秀的程序和優(yōu)秀的數(shù)據(jù)庫設(shè)計.
要做到這點(diǎn)得有足夠的經(jīng)驗(yàn).
這是我寫的一個結(jié)合UINON的嵌套查詢.
將五個方面的報表放到一個臨時表里,再從臨時表里,將所要的數(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 ";
分享給大家.
的困難主要是在編程語言上,數(shù)組,邏輯,算法,...
這些都過來了之后,困難就上升到數(shù)據(jù)庫設(shè)計上了.
以及數(shù)據(jù)邏輯.
一個優(yōu)秀的系統(tǒng),會集成優(yōu)秀的程序和優(yōu)秀的數(shù)據(jù)庫設(shè)計.
要做到這點(diǎn)得有足夠的經(jīng)驗(yàn).
這是我寫的一個結(jié)合UINON的嵌套查詢.
將五個方面的報表放到一個臨時表里,再從臨時表里,將所要的數(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)文章
explain慢查詢SQL調(diào)優(yōu)exists的實(shí)戰(zhàn)
這篇文章主要介紹了explain慢查詢SQL調(diào)優(yōu)exists的實(shí)戰(zhàn),經(jīng)過兩次優(yōu)化SQL語句之后,慢SQL的性能顯著提升了,耗時從8s優(yōu)化到了0.7s,現(xiàn)在拿出來給大家分享一下,希望對你會有所幫助2023-12-12
數(shù)據(jù)庫中row_number()?分組排序函數(shù)的具體使用
row_number()是一個強(qiáng)大的SQL窗口函數(shù),它通過partitionby和orderby子句實(shí)現(xiàn)分組和排序,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2024-11-11
redis數(shù)據(jù)庫查找key在內(nèi)存中的位置的方法
這篇文章主要介紹了redis數(shù)據(jù)庫查找key在內(nèi)存中的位置的方法,需要的朋友可以參考下2014-03-03
Sql Server、Access數(shù)據(jù)排名的實(shí)現(xiàn)方法(例如：成績排名)
在很多時候，我們需要統(tǒng)計數(shù)據(jù)的排名情況，最常見的是成績、投票數(shù)等等的。2009-06-06
SQLMap是一款自動執(zhí)行SQL注入的審計工具,支持多種SQL注入技術(shù),包括布爾型盲注、時間型盲注、報錯型注入、聯(lián)合查詢注入和堆疊查詢注入2025-01-01
SQLServer與Oracle常用函數(shù)實(shí)例對比匯總
這篇文章主要介紹了SQLServer與Oracle常用函數(shù)對比,需要的朋友可以參考下2014-06-06
這篇文章主要介紹了Mac系統(tǒng)Navicat的破解安裝與使用教程,本文通過圖文并茂的形式給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下2021-03-03
Win2003系統(tǒng)安裝SQL Server2000后1433端口未開放的解釋
這篇文章主要介紹了Win2003系統(tǒng)安裝SQL Server2000后1433端口未開放的解釋2007-02-02
關(guān)系型數(shù)據(jù)庫的設(shè)計規(guī)則詳解
大家好，本篇文章主要講的是關(guān)系型數(shù)據(jù)庫的設(shè)計規(guī)則詳解，感興趣的同學(xué)趕快來看一看吧，對你有幫助的話記得收藏一下，方便下次瀏覽2021-12-12
關(guān)于sql語句中的連接（join）關(guān)鍵字，是較為常用而又不太容易理解的關(guān)鍵字，下面就為大家介紹一下相關(guān)資料,需要的朋友可以參考下2020-09-09