NC5自定義查詢用戶手冊new

1、精品文檔第17章 在數(shù)據(jù)庫應(yīng)用系統(tǒng)中，數(shù)據(jù)查詢始終是應(yīng)用的基礎(chǔ)和核心。數(shù)據(jù)利用和挖掘的常用方式就是從數(shù)據(jù)庫中獲取用戶所需的業(yè)務(wù)數(shù)據(jù)，按照用戶預(yù)定義的格式，通常以業(yè)務(wù)報表的形式提供給用戶進(jìn)行瀏覽、分析、統(tǒng)計(jì)、打印及轉(zhuǎn)換，還可作進(jìn)一步的處理。由于查詢需求的多樣性，不可能窮舉所有的用戶查詢，當(dāng)然也不能要求普通用戶去直接拼寫SQL語句，因此需要提供一個工具讓用戶可以自己進(jìn)行查詢的設(shè)計(jì)和定制。17.6查詢引擎查詢引擎是面向高級實(shí)施人員和專業(yè)開發(fā)人員的查詢建模工具，可以全面支持復(fù)雜查詢的設(shè)計(jì)和個性化的報表展現(xiàn)。查詢引擎由兩個功能點(diǎn)組成：查詢引擎管理和查詢引擎權(quán)限控制。17.6.0 查詢引擎基本概念及案例1

2、7.6.0.1 基本概念數(shù)據(jù)表、臨時表、報表：數(shù)據(jù)表是數(shù)據(jù)庫里物理存在的表；報表是符合用戶要求的一個界面展現(xiàn)，它通常是一個二維的數(shù)據(jù)表格；有時我們僅僅通過數(shù)據(jù)表無法直接查出報表要求的數(shù)據(jù)結(jié)果，這時可能需要通過數(shù)據(jù)表查詢建立一些具有過渡性質(zhì)的中間表，然后再通過這些中間表查出最終結(jié)果，這種起到過渡作用的表稱為臨時表。數(shù)據(jù)字典：數(shù)據(jù)字典提供數(shù)據(jù)庫對象的邏輯屬性，有助于提高查詢定義的直觀性和易用性。內(nèi)連接與外連接：這是多表查詢中的不同連接方式，其中外連接主要包括左連接（left out join）和右連接（right out join）兩種。左連接取出左側(cè)關(guān)系中所有與右側(cè)關(guān)系的任一元組都不匹配的元組，

3、用空值填充所有來自右側(cè)關(guān)系的屬性，再把產(chǎn)生的元組加到內(nèi)連接的結(jié)果上。右連接與左連接完全對稱，“表A左連接表B”與“表B右連接表A”的效果是一樣的。以下述的表A和表B為例，它們以字段K作為鍵值進(jìn)行不同連接后會得到不同結(jié)果集。KV2011000330005500表BKV1011002200330表A內(nèi)連接（select A.K, A.V1, B.K, B.V2 from A inner join B on A.K=B.K）的結(jié)果是：A.KA.V1B.KB.V2011001100033003300左連接（select A.K, A.V1, B.V2 from A left join B on A.K

4、=B.K）的結(jié)果是：A.KA.V1B.KB.V20110011000220033003300右連接（select A.K, A.V1, B.V2 from A right join B on A.K=B.K）的結(jié)果是：A.KA.V1B.KB.V201100110003300330005500普通查詢（簡單查詢）與復(fù)合查詢：復(fù)合查詢和普通查詢的關(guān)系如圖所示。兩者的查詢結(jié)果都可以看成是臨時表，其差別在于：普通查詢是基于數(shù)據(jù)表定義查詢，復(fù)合查詢是基于已經(jīng)定義好的查詢來定義查詢。參數(shù)定義：通常用于查詢定義中SQL向?qū)гO(shè)計(jì)工具中的where條件表達(dá)式中，存在字符型和數(shù)值型（瀏覽時可直接錄入）、字符枚舉和

5、數(shù)值枚舉型參數(shù)（瀏覽時可下拉框錄入），參照型參數(shù)（瀏覽時可參照錄入）等穿透：當(dāng)明細(xì)表與匯總表一起出現(xiàn)時，某種內(nèi)在聯(lián)系可從匯總數(shù)據(jù)聯(lián)查明細(xì)數(shù)據(jù)，稱穿透或鉆取分級匯總：按分級列（具有一定級次的編碼規(guī)則），對匯總列（數(shù)值型）進(jìn)行各級匯總，并在各級末尾添加分級匯總列。交叉：按一定交叉規(guī)則進(jìn)行旋轉(zhuǎn)交叉或投影交叉1）旋轉(zhuǎn)交叉：行列不固定存在一定交叉規(guī)則（指定交叉行、交叉列、交叉值字段）之后，報表全動態(tài)地實(shí)現(xiàn)數(shù)據(jù)交叉。常用多層列表頭輔助體現(xiàn)交叉的維度，且交叉規(guī)則能夠在運(yùn)行態(tài)重新設(shè)定。適用于無法預(yù)先確定交叉后列結(jié)構(gòu)的報表；2）投影類交叉：行列固定；每行、每列均對應(yīng)一個篩選條件，而單元格第i行第j列的內(nèi)容根據(jù)S

6、QL語句“select 統(tǒng)計(jì)函數(shù)(字段) from 表 where (行條件i) and (列條件j)”查出，且是個唯一的值交叉砝碼：交叉設(shè)置界面中的交叉砝碼的作用是，描述交叉值字段對應(yīng)的列出現(xiàn)在交叉列展開后的哪個層次上。退化：在投影交叉查詢中的篩選條件中設(shè)置為退化的項(xiàng)；用于在運(yùn)行態(tài)（瀏覽態(tài)）進(jìn)行再次投影交叉時，其結(jié)果集中需顯示的備用行或備用列。在第一次投影交叉查詢結(jié)果中，退化項(xiàng)則既不出現(xiàn)在行頭上，也不出現(xiàn)在列頭上。行間占比：行間占比：指先按分組排序列進(jìn)行分組排序，然后計(jì)算單元格與其所在分組小計(jì)值的比，或者為單元格與其所在列總合計(jì)值的比列間占比：列間占比：指比例=本列數(shù)值/多列總和合并查詢：報

7、表部分區(qū)域由查詢結(jié)果填充、部分區(qū)域由手工填入，此類報表可通過“合并查詢”模式完成。17.6.0.2典型報表案例：報表是由數(shù)據(jù)和格式組成的整體。報表中的取數(shù)規(guī)則離不開SELECT查詢語句查詢模型中的核心功能“SQL向?qū)гO(shè)計(jì)”實(shí)際是SELECT查詢語句的可視化工具。如匯總表中的SELECT查詢語句：SELECT 年度，公司編碼，公司名稱，科目，sum(借方發(fā)生額)，sum(貸方發(fā)生額) FROM 憑證表INNER JOIN 憑證分錄表 ON憑證表. 憑證主鍵=憑證分錄表. 憑證主鍵INNER JOIN 公司目錄表 ON憑證表. 公司編碼=憑證分錄表. 公司主鍵INNER JOIN 科目表 ON憑證

8、分錄表. 科目主鍵=科目表. 科目主鍵WHERE 公司=corpGROUPBY年度，公司編碼，公司名稱，科目ORDERBY 年度，公司編碼，公司名稱ASC以下“SQL向?qū)гO(shè)計(jì)”工具（如下圖）中：“連接表”頁簽對應(yīng)FROM.部分“連接條件”頁簽對應(yīng)INNER JOIN .ON部分“連接字段” 頁簽對應(yīng)SELECT部分“篩選條件”頁簽對應(yīng)WHERE部分“排序字段” 頁簽對應(yīng)ORDERBY系統(tǒng)自動對不含聚合函數(shù)的字段作GROUPBY處理。(一)匯總表：年度公司編碼公司名稱科目借方發(fā)生額貸方發(fā)生額200101A1001100200101A2001100200202B1002200200202B2002

9、200200303C1003700200303C2003700用sql語言表示：SELECT 年度，公司編碼，公司名稱，科目，sum(借方發(fā)生額)，sum(貸方發(fā)生額) FROM 憑證表，憑證分錄表，公司目錄表，科目表INNER.ONGROUPBY年度，公司編碼，公司名稱，科目ORDERBY 年度，公司編碼，公司名稱（可通過“SQL向?qū)гO(shè)計(jì)”功能完成。）（二）明細(xì)表：年度公司編碼公司名稱憑證號制單日期科目借方發(fā)生額貸方發(fā)生額200101A12001/9/11001100200101A12001/9/12001100200202B22002/8/11002200200202B22002/8/12

10、002200200303C32003/8/11003300200303C32003/8/12003300200303C42003/10/11003400200303C42003/10/12003400用sql語言表示：SELECT 年度，公司編碼，公司名稱，科目，借方發(fā)生額，貸方發(fā)生額 FROM 憑證表，憑證分錄表，公司目錄表，科目表INNERN.ONORDERBY 年度，公司編碼，公司名稱（可通過“SQL向?qū)гO(shè)計(jì)”完成。）（三）旋轉(zhuǎn)交叉：如對上述的明細(xì)表進(jìn)行旋轉(zhuǎn)交叉（1）交叉列為1列時交叉行：憑證號、科目交叉列：年度交叉值：借方發(fā)生額交叉砝碼(借方發(fā)生額)：在列頭的第2層復(fù)合維度：為Y時 年

11、度 憑證號、科目200120022003借方發(fā)生額借方發(fā)生額借方發(fā)生額110011002100220031003300120012200232003交叉行：憑證號、科目交叉列：年度交叉值：借方發(fā)生額交叉砝碼(借方發(fā)生額)：在列頭的第1層年度 憑證號、科目借方發(fā)生額借方發(fā)生額借方發(fā)生額200120022003110011002100220031003300120012200232003（2）交叉列為多列時交叉行：憑證號、科目交叉列：年度、公司名稱交叉值：借方發(fā)生額、貸方發(fā)生額交叉砝碼(借方發(fā)生額、貸方發(fā)生額)：在列頭的第3層列復(fù)合維度：為Y時年度、公司名稱 憑證號、科目200120022003A

12、BC借方發(fā)生額貸方發(fā)生額借方發(fā)生額貸方發(fā)生額借方發(fā)生額貸方發(fā)生額1100110012001100210022002200220031003300320033004100340042003400交叉行：憑證號、科目交叉列：年度、公司名稱交叉值：借方發(fā)生額、貸方發(fā)生額交叉砝碼（借方發(fā)生額、貸方發(fā)生額）：在列頭的第3層列復(fù)合維度：為N時年度、 公司名稱憑證號、科目200120022003ABCABCABC借方發(fā)生額貸方發(fā)生額借方發(fā)生額貸方發(fā)生額借方發(fā)生額貸方發(fā)生額借方發(fā)生額貸方發(fā)生額借方發(fā)生額貸方發(fā)生額借方發(fā)生額貸方發(fā)生額借方發(fā)生額貸方發(fā)生額借方發(fā)生額貸方發(fā)生額借方發(fā)生額貸方發(fā)生額110011001

13、2001100210022002200220031003300320033004100340042003400（可通過“SQL向?qū)гO(shè)計(jì)”完成。）（四）投影交叉：部門姓名性別財(cái)務(wù)開發(fā)部Sbq男財(cái)務(wù)開發(fā)部sy男供應(yīng)鏈開發(fā)部ghl女財(cái)務(wù)開發(fā)部fll女 性別部門男女財(cái)務(wù)開發(fā)部21供應(yīng)鏈開發(fā)部2用SQL語言表示：select count(性別) from 人事視圖 where 部門= 財(cái)務(wù)開發(fā)部 and 性別= 男（可通過“SQL向?qū)гO(shè)計(jì)”完成。）（五）穿透：（1）對旋轉(zhuǎn)交叉結(jié)果穿透：年度公司編碼公司名稱憑證號制單日期科目借方發(fā)生額貸方發(fā)生額200101A12001/9/11001100200101A1

14、2001/9/12001100200202B22002/8/11002200200202B22002/8/12002200200303C32003/8/11003300200303C32003/8/12003300200303C42003/10/11003400200303C42003/10/12003400200403C52004/4/11003500按單列（科目=1003）穿透結(jié)果：年度公司編碼公司名稱憑證號制單日期科目借方發(fā)生額貸方發(fā)生額200303C32003/8/11003300200303C42003/10/11003400200403C52004/4/11003500按多列（科目

15、=1003和年度=2003）穿透結(jié)果：年度公司編碼公司名稱憑證號制單日期科目借方發(fā)生額貸方發(fā)生額200303C32003/8/11003300200303C42003/10/11003400（可通過“SQL向?qū)гO(shè)計(jì)”及“穿透規(guī)則”中的查詢間穿透來共同完成。）（2）對投影交叉結(jié)果穿透： 性別部門男女財(cái)務(wù)開發(fā)部21供應(yīng)鏈開發(fā)部2按單元格穿透：部門姓名性別財(cái)務(wù)開發(fā)部Sbq男財(cái)務(wù)開發(fā)部sy男（可通過“SQL向?qū)гO(shè)計(jì)”及“穿透規(guī)則”中的投影交叉穿透功能來共同完成。）（六）行間占比年度公司編碼公司名稱憑證號制單日期科目借方發(fā)生額借方占比200101A12001/9/11001100200101A12001

16、/9/12001100200202B22002/8/11002200200202B22002/8/12002200200303C32003/8/11003300200303C32003/8/12003400如按公司編碼對借方發(fā)生額進(jìn)行匯總：年度公司編碼公司名稱憑證號制單日期科目借方發(fā)生額200101A12001/9/11001100200101A12001/9/12001100小計(jì)200200202B22002/8/11002200200202B22002/8/12002200小計(jì)400200303C32003/8/11003300200303C32003/8/12003400小計(jì)700合計(jì)1

17、300行間占比結(jié)果：年度公司編碼公司名稱憑證號制單日期科目借方發(fā)生額借方占比1（借方發(fā)生額/分組小計(jì)）借方占比2（借方發(fā)生額/合計(jì)）200101A12001/9/110011000500769200101A12001/9/120011000500769200202B22002/8/110022000501538200202B22002/8/120022000501538200303C32003/8/110033000428602308200303C32003/8/120034000571403077（可通過“SQL向?qū)гO(shè)計(jì)”及“數(shù)據(jù)加工”中內(nèi)置的分組統(tǒng)計(jì)、行間占比算法來共同完成。）（七）列向分

18、支統(tǒng)計(jì)型corpnamesexA公司韓千穗女A公司智銀圣男A公司金曉光女A公司王麗娜女B公司金賢成男B公司姜希燦女通過SELECT corp, (CASE sex WHEN '男' THEN 1 ELSE 0 END) AS male, (CASE sex WHEN '女' THEN 1 ELSE 0 END) AS femaleFROM T將獲得如下結(jié)果：corpmalefemaleA公司01A公司10A公司01A公司01B公司10B公司01SELECT corp, SUM(CASE sex WHEN '男' THEN 1 ELSE 0 END

19、) AS male, SUM(CASE sex WHEN '女' THEN 1 ELSE 0 END) AS femaleFROM TEM_CASEGROUP BY corp則將查出以下結(jié)果：corpmalefemaleA公司13B公司11（可通過“SQL向?qū)гO(shè)計(jì)”功能來完成。）（八）合并查詢（半錄入半嵌入型）部門漢族滿族蒙古族1、直屬部門情況財(cái)務(wù)部<人數(shù)><人數(shù)><人數(shù)>行政部<人數(shù)><人數(shù)><人數(shù)>2、開發(fā)部門情況HR產(chǎn)品開發(fā)部<人數(shù)><人數(shù)><人數(shù)>HR產(chǎn)品市場部&

20、lt;人數(shù)><人數(shù)><人數(shù)>（可通過“合并查詢”功能來完成，上述表格中的部分區(qū)域通過手工錄入，部分區(qū)域通過嵌入查詢完成）17.6.1查詢引擎管理操作方法在主菜單中單擊客戶化-自定義查詢-查詢管理中心的子菜單查詢引擎管理，系統(tǒng)將彈出“查詢引擎”的瀏覽窗口。查詢引擎管理包括查詢/界面對象樹管理、查詢模型設(shè)計(jì)、界面模型設(shè)計(jì)等主要功能。17.6.1.1查詢/界面對象樹管理圖17-6所示的查詢引擎主界面左側(cè)為一棵對象樹，樹上掛有代表查詢對象和界面對象的節(jié)點(diǎn)，右側(cè)為顯示當(dāng)前選中對象基本屬性的屬性框。圖17-6查詢引擎主界面文件夾管理：l 增加/刪除/修改：在根節(jié)點(diǎn)或目錄節(jié)點(diǎn)之

21、下可以增加目錄，刪除目錄時會刪除目錄下的所有對象。目錄的可修改屬性為目錄名稱。同一目錄下的目錄和對象不能重名。l 復(fù)制/剪切/粘貼：在查詢子樹或格式子樹下，可以將一個目錄及其下屬內(nèi)容從某位置拷貝或移動到另一個位置。對象管理：l 增加/刪除/修改：在根節(jié)點(diǎn)或目錄節(jié)點(diǎn)之下可以增加查詢對象或者界面對象。前者的屬性為編碼、名稱和數(shù)據(jù)源，后者的屬性為編碼和名稱，其中編碼一經(jīng)使用，就不再建議作任何修改，因?yàn)榇司幋a可能被其它對象引用。l 復(fù)制/剪切/粘貼：在查詢或格式子樹下，可以將一個對象從某位置拷貝或移動到另一個位置。l 導(dǎo)入/導(dǎo)出：選中某個對象，可將其導(dǎo)出為XML文件，該文件可被導(dǎo)入到另外的目錄下。此功

22、能可用于對象的跨數(shù)據(jù)庫復(fù)制。在導(dǎo)出查詢對象或界面對象時，需將其引用的查詢對象一并導(dǎo)出才有意義。選中某目錄時，導(dǎo)入功能可用。切換定義數(shù)據(jù)源：（當(dāng)查詢引擎不支持多數(shù)據(jù)源，只支持單數(shù)據(jù)源時此功能置灰）查詢引擎支持多數(shù)據(jù)源運(yùn)作機(jī)制模式NC業(yè)務(wù)在數(shù)據(jù)源A下運(yùn)行，查詢引擎的定義放在數(shù)據(jù)源B，通過引擎定義的查詢可以到數(shù)據(jù)源C去執(zhí)行。圖17-7切換定義數(shù)據(jù)源定義數(shù)據(jù)源（上面說的B）是指查詢引擎自身的系統(tǒng)表所在的數(shù)據(jù)源，執(zhí)行切換功能后，主界面上的對象樹的內(nèi)容會作相應(yīng)改變。圖17-8切換定義數(shù)據(jù)源效果修改執(zhí)行數(shù)據(jù)源：當(dāng)需修改執(zhí)行數(shù)據(jù)源時，選擇左側(cè)查詢設(shè)計(jì)下的目錄，按“輔助設(shè)置”>“批量修改執(zhí)行數(shù)據(jù)源”，選擇

23、數(shù)據(jù)源下拉框數(shù)據(jù)進(jìn)行保存。17.6.1.2查詢模型設(shè)計(jì)查詢模型設(shè)計(jì)包括參數(shù)定義、SQL設(shè)計(jì)（含交叉定義）、SQL整理、數(shù)據(jù)加工、穿透規(guī)則等幾部分，其中穿透規(guī)則描述的是本查詢與外部查詢的通信關(guān)系，其余部分都用于描述查詢內(nèi)部的屬性。查詢設(shè)計(jì)態(tài)和運(yùn)行態(tài)的基本流程如下圖所示：圖17-9查詢模型流程(1)參數(shù)控制參數(shù)是查詢模型中代表動態(tài)信息的元素。在SQL設(shè)計(jì)、SQL整理和數(shù)據(jù)加工中，均可以根據(jù)參數(shù)的不同，對查詢的定義和執(zhí)行進(jìn)行調(diào)整。如果創(chuàng)建某個參數(shù)的作用是根據(jù)其取值的不同改變WHERE條件的設(shè)置，則這類參數(shù)成為過濾型參數(shù)，這是最常用的參數(shù)功能。參數(shù)定義：用于定義本查詢內(nèi)部引用的所有參數(shù)，其中參數(shù)名要保

24、證互不相同。數(shù)據(jù)類型包括自由錄入、枚舉、參照幾種，當(dāng)類型為枚舉時，枚舉項(xiàng)為用“”分隔的枚舉值，或者是一個單字段的查詢SQL；當(dāng)類型為參照時，枚舉項(xiàng)為基礎(chǔ)參照名，或者是用尖括號括起的自定義參照的類名。圖17-10參數(shù)定義參數(shù)引用：參數(shù)可在SQL設(shè)計(jì)的篩選條件頁簽引用，也可在SQL整理或數(shù)據(jù)加工的嵌入代碼中利用編碼向?qū)б谩D17-11參數(shù)引用參數(shù)設(shè)置：運(yùn)行態(tài)要求用戶首先對參數(shù)進(jìn)行設(shè)置，這與查詢模板的使用類似。當(dāng)報表引用了多個查詢時，參數(shù)將通過多頁簽設(shè)置，每個頁簽代表一個查詢。圖17-12參數(shù)設(shè)置(2)SQL設(shè)計(jì)SQL設(shè)計(jì)包括向?qū)гO(shè)計(jì)和手工設(shè)計(jì)兩種方式。無論采用哪種，在完成設(shè)計(jì)時系統(tǒng)均會對SQL進(jìn)

25、行合法性校驗(yàn)，如未通過校驗(yàn)則不能保存設(shè)計(jì)。作為向?qū)гO(shè)計(jì)的一個重要組成環(huán)節(jié)，有必要先介紹一下數(shù)據(jù)字典。u 數(shù)據(jù)字典前面已經(jīng)提到，查詢對象的屬性中包含數(shù)據(jù)源信息，在設(shè)計(jì)查詢的時候需要加載相應(yīng)數(shù)據(jù)源的數(shù)據(jù)字典。NC的數(shù)據(jù)庫以DDC作為數(shù)據(jù)字典，但非NC的數(shù)據(jù)庫則沒有DDC。系統(tǒng)會根據(jù)執(zhí)行數(shù)據(jù)源是NC、IUFO或其它數(shù)據(jù)庫三種情況分別加載不同的數(shù)據(jù)字典，而且這一機(jī)制已經(jīng)預(yù)留了擴(kuò)展的接口，從而可以面向各種類型的數(shù)據(jù)庫執(zhí)行取數(shù)功能。關(guān)于數(shù)據(jù)字典的生成有多種做法，可以參考DDC的專門文檔。查詢引擎中用到的數(shù)據(jù)字典都以一棵對象樹的形式存在，樹節(jié)點(diǎn)包括目錄、表、視圖、字段、外鍵等信息，通過雙擊對象節(jié)點(diǎn)可以協(xié)助錄

26、入SQL語句的成分。u 向?qū)гO(shè)計(jì)向?qū)гO(shè)計(jì)參考典型SQL的構(gòu)成規(guī)則，充分利用數(shù)據(jù)字典的功能，引導(dǎo)用戶一步步完成SQL的設(shè)計(jì)，因此是推薦使用的方式。它由以下六個環(huán)節(jié)構(gòu)成，其中最后一步“交叉屬性”超出了SQL的涵義，在后面的章節(jié)中進(jìn)行專門介紹。l 表定義：即SQL語句中的from部分，可以利用數(shù)據(jù)字典選擇庫里的數(shù)據(jù)表，也可以選擇查詢對象，從而實(shí)現(xiàn)查詢的集成（基于查詢結(jié)果定義新的查詢）。圖17-13表定義圖17-14數(shù)據(jù)表與臨時表l 連接定義：即SQL語句中的join部分（表間連接關(guān)系），支持單鍵連接和多鍵連接兩種模式。圖17-15連接定義l 字段定義：即SQL語句中的select部分，可以定義任何S

27、QLSERVER語法支持的查詢表達(dá)式，但要保證字段別名不重復(fù)且使用規(guī)范名稱（以字母開頭、由字母、數(shù)字、下劃線組成的字符串，且不能使用SQL保留字）。如果使用了聚集函數(shù)，則由系統(tǒng)自動完成group by部分。圖17-16字段定義圖17-16表達(dá)式生成器l 字段表達(dá)式定義：下面作些特殊函數(shù)的用法說明。如其它函數(shù)：CASE WHEN的用法如某查詢定義中，當(dāng)科目表中的字段“余額方向”為1時，需將查詢中的字段“余額方向名稱”（如字段別名為name）值顯示為”借”，否則為”貸”。則字段表達(dá)式為case when bd_accsubj.balanorient=1 then '借' else

28、'貸' end操作方法：雙擊“其它函數(shù)”中的CASE WHEN，則在字段表達(dá)式中生成：case when then else end，然后在字段表達(dá)式的編輯框中繼續(xù)手工編輯成完整表達(dá)式：case when bd_accsubj.balanorient=1 then '借' else '貸' end，然后按“確定”。圖17-17 CASE WHEN的用法1圖17-17 CASE WHEN的用法2l 篩選條件定義：即SQL語句中的where部分，可定義確定條件和待定條件，后者可以通過雙擊右操作數(shù)編輯框彈出參數(shù)界面并作引用。圖17-18篩選條件定義l

29、排序字段定義：即SQL語句中的order by部分。圖17-17排序字段定義l 數(shù)據(jù)交叉屬性：交叉是一種常用的多維數(shù)據(jù)分析手段，詳見后文。u 手工設(shè)計(jì)手工設(shè)計(jì)是支持自由SQL的方式，由于復(fù)雜SQL在解析、適配、執(zhí)行等方面的不穩(wěn)定性較大，因此不推薦普通用戶使用。手工設(shè)計(jì)包括：l SQL錄入：用戶可以在數(shù)據(jù)字典的輔助下錄入SQL語句，要求遵循SQLSERVER語法。圖17-20 SQL錄入l 字段顯示名設(shè)置：系統(tǒng)通過對用戶錄入SQL的解析獲得字段信息，并向用戶提供輸入字段顯示名的機(jī)會，顯示名的默認(rèn)值與查詢字段的物理名相同。圖17-21字段顯示名設(shè)置(3)數(shù)據(jù)交叉數(shù)據(jù)交叉包括第一類(投影)交叉和第二

30、類(旋轉(zhuǎn))交叉兩種類型。兩類交叉的差別在于：第一類交叉屬于查詢行為，適用于行列設(shè)置都已在表樣中固定的報表，其交叉結(jié)果嚴(yán)格遵循表樣格式，其行列結(jié)構(gòu)不會受查詢數(shù)據(jù)的影響；第二類交叉屬于數(shù)據(jù)加工行為，其交叉后的行列結(jié)構(gòu)在查詢出數(shù)據(jù)之前不可預(yù)料，多用于完全真實(shí)地對原始數(shù)據(jù)進(jìn)行動態(tài)處理。u 第一類（投影）交叉假定有一個查詢SQL，其查詢結(jié)果是單行單列的匯總值，另外還有兩組WHERE條件的序列，分別為橫向序列（WhereX1、WhereX2、WhereXn）和縱向序列（WhereY1、WhereY2、WhereYn），則數(shù)據(jù)矩陣A（aij）對應(yīng)的結(jié)果集成為投影交叉，aij是WhereXi和WhereYj同

31、時作用在上述SQL上獲得的結(jié)果。比如，某查詢用于查出公司和部門下的人數(shù)，原始SQL為：select bd_invbasdoc.invcode , bd_invbasdoc.invname, bd_invcl.invclasscode, bd_invcl.invclassname, ic_general_b.ninnum from ic_general_b left outer join bd_invbasdoc on ic_general_b.cinvbasid = bd_invbasdoc.pk_invbasdoc left outer join bd_invcl on bd_invbasd

32、oc.pk_invcl = bd_invcl.pk_invcl group by bd_invbasdoc.invcode,bd_invbasdoc.invname,bd_invcl.invclasscode,bd_invcl.invclassname查出的結(jié)果如下圖所示：圖17-22原始數(shù)據(jù)（無交叉）現(xiàn)在把查詢字段中只保留實(shí)收數(shù)ic_general_b.ninnum一項(xiàng)，然后進(jìn)行如下的投影交叉設(shè)置：圖17-23投影交叉設(shè)置則交叉后的結(jié)果如下圖所示：圖17-24投影交叉結(jié)果u 第二類（旋轉(zhuǎn)）交叉旋轉(zhuǎn)交叉用于顯示表中某幾個字段（稱為交叉值字段）的統(tǒng)計(jì)值，并將其分組放置在查詢結(jié)果中，一組列在數(shù)據(jù)表

33、的左側(cè)（稱為交叉行字段），一組列（稱為交叉列字段）旋轉(zhuǎn)到數(shù)據(jù)表的上部成為列表頭，統(tǒng)計(jì)值則放在對應(yīng)的交叉單元。關(guān)于旋轉(zhuǎn)交叉的更細(xì)節(jié)的內(nèi)容，請參考交叉表使用說明。對于投影交叉中的例子，假定進(jìn)行如下的旋轉(zhuǎn)交叉設(shè)置（其中交叉砝碼表示交叉值字段的位置，它只能出現(xiàn)在交叉行字段或交叉列字段當(dāng)中）：圖17-25旋轉(zhuǎn)交叉設(shè)置則交叉后的結(jié)果如下圖所示：圖17-26旋轉(zhuǎn)交叉結(jié)果(4)代碼嵌入代碼嵌入包括SQL整理、數(shù)據(jù)加工、數(shù)據(jù)穿透等三種查詢對象使用方式。u SQL整理SQL整理是指在SQL查詢之前，對SQL所作的調(diào)整。通常會根據(jù)參數(shù)的不同，對查詢的表、字段或條件等信息進(jìn)行增補(bǔ)或修改。SQL整理的具體內(nèi)容由內(nèi)嵌的代

34、碼片段描述（這與后面將介紹的數(shù)據(jù)加工和穿透規(guī)則一樣），代碼的錄入可通過編碼向?qū)f(xié)助完成，盡管如此，這幾種功能仍然推薦由高級實(shí)施人員或程序員使用。圖17-27 SQL整理代碼在圖示的SQL整理代碼中，當(dāng)參數(shù)“detail”取值為“Y”時，引擎會在本查詢對象已完成的SQL設(shè)計(jì)基礎(chǔ)上多查詢一張表（bd_defdoc）和兩個字段（檔案編碼、檔案名稱），從而獲得更明細(xì)的查詢信息；如果該參數(shù)取值不為“Y”，則仍然按照SQL設(shè)計(jì)的定義去執(zhí)行查詢。以下簡單介紹幾種SQL整理算法的編碼向?qū)褂梅椒ǎ簂 追加查詢表：參數(shù)為待追加的表物理名。圖17-28追加表向?qū) 追加連接條件：與追加表同時使用，參數(shù)為連接模式、

35、追加表物理名和追加的表間連接條件。圖17-29追加連接條件向?qū) 追加查詢字段：與追加表同時使用，參數(shù)為追加字段的表達(dá)式、顯示名和別名。圖17-30追加字段向?qū) 按參數(shù)追加條件：根據(jù)參數(shù)取值的不同追加不同的篩選條件，參數(shù)為參數(shù)名和篩選字段。圖17-31按參數(shù)追加條件向?qū) 數(shù)據(jù)加工數(shù)據(jù)加工是指某個（或某幾個）查詢結(jié)果集在經(jīng)過一系列加工算法的變換后得到另一個結(jié)果集，而后者通常是業(yè)務(wù)上要求的展現(xiàn)結(jié)果。相關(guān)的算法又稱為加工濾鏡，濾鏡通常支持輸入多個結(jié)果集和輸出一個結(jié)果集，并可以嵌套使用。數(shù)據(jù)加工與SQL整理的區(qū)別在于：前者在SQL查詢之后進(jìn)行，作用的對象是結(jié)果集；后者在查詢之前進(jìn)行，作用的對象是S

36、QL定義。在數(shù)據(jù)加工的編碼向?qū)е?，提供了多種實(shí)用加工濾鏡。圖示的數(shù)據(jù)加工代碼把主鍵分別為“user1”和“user2”的兩個結(jié)果集按照pk_corp字段為鍵進(jìn)行了左外連接，并在連接結(jié)果中保留了“user_name1”、“pk_corp”、“user_name2”三列。圖17-32數(shù)據(jù)加工代碼以下簡單介紹幾種數(shù)據(jù)加工算法的編碼向?qū)褂梅椒ǎ河刹樵儷@得結(jié)果集：根據(jù)查詢ID和參數(shù)獲得查詢結(jié)果集，如果沒有參數(shù)或不需要設(shè)置參數(shù)，則參數(shù)變量可取null。圖17-33獲得結(jié)果集向?qū)Ч皆O(shè)置：參數(shù)為公式列名、返回值數(shù)據(jù)類型和公式，如果公式列名不存在則新增一列，否則將該列的內(nèi)容更新為公式返回值。圖17-34公式

37、編輯圖17-35公式定義結(jié)果集連接：指定兩個結(jié)果集的連接字段、連接方式和連接后的保留字段，得到連接后的結(jié)果集。圖17-36結(jié)果集連接向?qū)ЫY(jié)果集聯(lián)合：指定兩個列結(jié)構(gòu)相兼容的結(jié)果集，得到聯(lián)合（union）后的結(jié)果。圖17-37結(jié)果集聯(lián)合向?qū) 數(shù)據(jù)穿透數(shù)據(jù)穿透：假定報表R1上存在某個區(qū)域A1，A1內(nèi)部有若干業(yè)務(wù)數(shù)據(jù)，如果以A1為出發(fā)點(diǎn)能夠切換到另一張報表R2的上的區(qū)域A2，而且A1和A2的內(nèi)部數(shù)據(jù)存在著某種內(nèi)在聯(lián)系，則稱可以從R1穿透到R2。當(dāng)這種穿透行為傳遞下去，就可以了解某張報表的數(shù)據(jù)明細(xì)，甚至追溯到它的數(shù)據(jù)來源。通常會把上述的區(qū)域選為一個表格行或者一個表單元，而上述的內(nèi)在聯(lián)系就稱為穿透規(guī)則。

38、一個查詢上可以定義多個穿透規(guī)則，每個規(guī)則需要指定穿透的目標(biāo)查詢，同時目標(biāo)查詢上要創(chuàng)建一個過濾型參數(shù)，用于接收穿透區(qū)域的取值。下圖所示的穿透規(guī)則表達(dá)了這樣的一個穿透意愿：當(dāng)用戶選中了當(dāng)前查詢結(jié)果的某行時，允許他按照“psncode”字段在選中行的取值穿透到另一個查詢（即目標(biāo)查詢，其ID為“rygzay”），而目標(biāo)查詢的接收參數(shù)為“psncode”。一個查詢可以設(shè)置多條穿透規(guī)則，由本查詢可穿透到的目標(biāo)查詢需要創(chuàng)建一個（或多個）過濾型參數(shù)，用于接收穿透值。下圖所示的穿透規(guī)則表達(dá)了以下的穿透意愿：如果用戶選中了當(dāng)前查詢結(jié)果的某行時，允許用戶根據(jù)選中行的psncode字段取值穿透到另一個查詢（即目標(biāo)查詢

39、，其ID為“rygzan”），而目標(biāo)查詢的接收參數(shù)為psncode圖17-38穿透規(guī)則定義向?qū)D17-39穿透規(guī)則代碼下圖顯示了一個由存貨庫存存量信息穿透到存貨信息再穿透到存貨分類信息的用例，其中第一步穿透是非等值穿透（相當(dāng)于“存貨名稱like 芯片%“），第二步是等值穿透（相當(dāng)于“存貨分類=產(chǎn)成品”）。按以“芯片“打頭的存貨名稱由庫存實(shí)收數(shù)量統(tǒng)計(jì)表穿透到存貨信息按存貨分類為“產(chǎn)成品“從存貨信息表穿透到存貨分類信息圖17-40穿透效果示意(5)查詢?yōu)g覽u 查詢執(zhí)行與展現(xiàn)在建立好查詢模型之后，系統(tǒng)提供預(yù)覽查詢執(zhí)行結(jié)果的功能。用戶首先輸入執(zhí)行查詢所需要的參數(shù)取值（如果有參數(shù)的話），查詢引擎會根據(jù)該

40、查詢對象的數(shù)據(jù)源去對應(yīng)的數(shù)據(jù)庫執(zhí)行查詢，在經(jīng)過查詢結(jié)果集評估（對于查詢記錄數(shù)過大的情況允許用戶優(yōu)化或放棄查詢）之后，把結(jié)果集展現(xiàn)給用戶。在展現(xiàn)之后，用戶可以繼續(xù)進(jìn)行穿透、交叉和儀表測試等工作。圖17-41查詢預(yù)覽u 數(shù)據(jù)導(dǎo)出導(dǎo)出功能支持將預(yù)覽數(shù)據(jù)導(dǎo)出為純文本文件，并可進(jìn)而用EXCEL打開。隨著產(chǎn)品的發(fā)展，數(shù)據(jù)導(dǎo)出的格式會逐步豐富。u 創(chuàng)建物化表查詢引擎可以根據(jù)預(yù)覽數(shù)據(jù)直接到數(shù)據(jù)庫創(chuàng)建物化表，同時將物化表的結(jié)構(gòu)注冊到DDC中，以后的SQL設(shè)計(jì)就可以基于新創(chuàng)建的物化表進(jìn)行，從而擴(kuò)充了查詢集成的范圍。至于如何保證物化表數(shù)據(jù)的同步刷新，可以參看抽取任務(wù)一節(jié)。圖17-42創(chuàng)建物化表17.6.1.3格式模

41、型設(shè)計(jì)界面模型節(jié)點(diǎn)的新建和管理參見前面章節(jié)。可以設(shè)計(jì)新的界面模型，也可以對已有的界面模型進(jìn)行修改。界面模型的設(shè)計(jì)包括引用查詢，界面可視化設(shè)計(jì)，綁定數(shù)據(jù)集，數(shù)據(jù)預(yù)處理等部分。界面模型設(shè)計(jì)態(tài)和運(yùn)行態(tài)的基本流程如下圖所示（設(shè)計(jì)態(tài)的操作并沒有嚴(yán)格意義上的順序限制）：圖17-45界面模型流程(1)引用查詢引用查詢是指將界面模型所要展現(xiàn)的查詢結(jié)果集對應(yīng)的查詢定義引用到模型中來。由于同一查詢定義根據(jù)不同參數(shù)設(shè)置也可以得到不同結(jié)果集，所以為了區(qū)分這種綁定基于同一查詢定義的不同結(jié)果集時，對每一個查詢結(jié)果集起一個單一的別名來標(biāo)志。圖17-46查詢引用界面對于查詢結(jié)果集之間存在的某種一對多的關(guān)系，還可以根據(jù)需要建立

42、主從關(guān)系（即主從表）：圖17-47主子連接設(shè)置界面(2)格式設(shè)計(jì)界面模型的格式設(shè)計(jì)包括了控件管理、行列格式設(shè)計(jì)以及數(shù)據(jù)預(yù)處理等三部分。分別介紹如下。表控件圖表圖17-48界面模型設(shè)計(jì)界面u 控件管理界面模型設(shè)計(jì)工具提供了最常用的控件，如圖17-48中控件欄中所示，分別是按鈕、復(fù)選框、組合框、標(biāo)簽、列表、單選框、多頁簽、表、文本域、文本框、樹、面板、分割欄、主子表和圖表，其中最重量級的控件是表和圖表，絕大部分的數(shù)據(jù)集展現(xiàn)工作是這兩個控件完成的。初始時，界面上只有一個面板，處于選中狀態(tài)，布局是默認(rèn)的BorderLayout。接下來的工作就是要在這個面板上定制界面，這個過程和一般的可視化設(shè)計(jì)大同小異

43、，在此不再贅述。圖17-49界面模型雙擊界面上的控件就可以打開屬性編輯器，可以編輯控件的相關(guān)屬性。屬性編輯器上邊是當(dāng)前界面的控件列表，下面列出了當(dāng)前選中控件的屬性列表。可以從控件列表中選中控件，也可以在界面上雙擊控件來選中控件進(jìn)行屬性編輯。圖17-50屬性編輯器所有控件都有背景色，前景色，字體和首選大小等普通屬性。其次，控件具有一些自己特有的屬性，如面板具有布局屬性，分割欄有分欄方向，分欄位置等特有屬性。下面介紹一下幾個主要的控件。展現(xiàn)數(shù)據(jù)集的最重要的控件表的幾個屬性：l 綁定數(shù)據(jù)集：可以將表綁定到引用查詢中的某一個查詢定義，那么在運(yùn)行態(tài)下表展現(xiàn)的就是該查詢定義對應(yīng)得到的查詢結(jié)果集。當(dāng)設(shè)置了表

44、的數(shù)據(jù)集綁定屬性后，該查詢定義中定義的列就相應(yīng)的出現(xiàn)在表里（此時沒有數(shù)據(jù)只有表頭）；l 列格式綁定：有時候會對表中的數(shù)據(jù)展現(xiàn)有各種格式上的要求，如預(yù)警顯示，精度設(shè)置，前綴后綴等等。這時可以設(shè)置表的列格式屬性，在列格式屬性里不但可以設(shè)置列綁定的列格式，也可以設(shè)置列的顯示順序，顯示與否等屬性（列格式的定義在后面的小節(jié)里專門講述）。圖17-51列格式設(shè)置l 列表頭綁定：當(dāng)表綁定了數(shù)據(jù)集之后，可以根據(jù)展現(xiàn)需要綁定列表頭格式，在表頭ID屬性里綁定表頭的ID之后，從界面上就可以看到表的列表頭已經(jīng)綁定到了表頭ID對應(yīng)的表頭格式（列表頭格式的設(shè)計(jì)在后面的小節(jié)里會專門講述）；l 行表頭綁定：如果表綁定的查詢定義

45、里定義了第一類（投影）交叉，則表還可以設(shè)置行表頭格式。關(guān)于行表頭的定義和設(shè)置在后面小節(jié)中講述。在設(shè)置了數(shù)據(jù)集綁定屬性之后，可以設(shè)定圖表控件的數(shù)據(jù)綁定屬性。在圖表控件上彈出右鍵菜單點(diǎn)擊數(shù)據(jù)綁定菜單項(xiàng)，可彈出數(shù)據(jù)綁定設(shè)置界面如圖17-52所示。在這里可以設(shè)置圖表的類型，以及綁定的系列等屬性。圖17-52圖表數(shù)據(jù)綁定設(shè)置界面文本框，文本域，標(biāo)簽等簡單控件提供了綁定到多種內(nèi)容的屬性，它可以綁定查詢結(jié)果集中某列，綁定查詢結(jié)果集對應(yīng)的參數(shù)，綁定環(huán)境變量等等。下面是設(shè)置一個文本框綁定到查詢參數(shù)的截圖。圖17-53綁定查詢參數(shù)的文本框u 行列格式設(shè)計(jì)行列格式設(shè)計(jì)包括列格式定義，列表頭定義和行表頭定義三部分，分

46、別說明如下。l 列格式定義圖17-54列格式定義界面可以定義四種類型的列格式：字符型，數(shù)值型，貨幣型，百分比型，后三種實(shí)際上都屬于數(shù)值型數(shù)據(jù)的不同展現(xiàn)。格式定義包括對齊方式，文本顏色，前導(dǎo)符號等等。對于數(shù)值型的列格式還可以設(shè)置保留小數(shù)位數(shù)，是否加千分位等格式。列格式的一個比較重要的設(shè)置就是設(shè)置預(yù)警顯示，預(yù)警顯示就是將符合指定條件的數(shù)據(jù)按指定顏色來顯示。如銷售額這樣的數(shù)據(jù)通過分段預(yù)警就很容易體現(xiàn)出高中低來，方便瀏覽數(shù)據(jù)。圖17-55中所示預(yù)警設(shè)置就是將大于2000的數(shù)用紅色顯示。預(yù)警條件中除支持用常量表達(dá)式進(jìn)行比較之外，也支持用含變量的表達(dá)式進(jìn)行比較，其中的變量名必須是表中某列的列名。如表中有A

47、，B，C三列，要求某行中A列中的值大于B列和C列的值之和時該行A列按某種指定顏色顯示，可以給A列綁定列格式，在列格式的預(yù)警設(shè)置中設(shè)置預(yù)警條件為>B+C(注：這里的A，B，C都是指的列名，不是列顯示名)。圖17-55預(yù)警設(shè)置界面l 列表頭定義圖17-56列表頭設(shè)計(jì)界面有時候?yàn)榱朔奖銡w類和分組列數(shù)據(jù)，需要定義多表頭來加強(qiáng)展現(xiàn)。如圖17-56，可以根據(jù)自己的需要定義一個多表頭用來展現(xiàn)，在這里，可以很方便的組合表中列，定義好的表頭格式存在數(shù)據(jù)庫中，并有一個表頭ID與之對應(yīng)。通過表頭ID該列表頭格式可以被表綁定（綁定了列表頭的表效果參見圖17-49）。小技巧：定義對應(yīng)某一查詢的列表頭展現(xiàn)時，可以通

48、過參照，選中該查詢來快速引入該查詢定義的列，而不用手工錄入。l 行表頭定義如果設(shè)置表的綁定數(shù)據(jù)集屬性到一個定義了第一類交叉的查詢，那么表中會出現(xiàn)與查詢中定義的列和行相對應(yīng)的列頭和行頭。默認(rèn)的列頭和行頭沒有任何附加的格式，如圖5-57所示。圖17-57綁定到第一類交叉查詢定義的表就像可以給列分組定義列表頭一樣，也可以給行分組定義行表頭。如圖17-58所示，類似于列表頭的定義，也可以合并行，還可以定義行號顯示與否，行號顯示的位置，甚至可以為定義好的表頭綁定列表頭。保存后可以看到界面上的表已經(jīng)顯示了新定義的行表頭格式。圖17-58行表頭設(shè)計(jì)界面圖17-59定義了行列表頭格式的表u 預(yù)定義數(shù)據(jù)處理界面

49、模型中支持兩種數(shù)據(jù)預(yù)定義的處理：定義公式列和定義小計(jì)合計(jì)。其中，公式列定義是基于查詢結(jié)果集的，定義的公式列對于所有綁定該查詢結(jié)果集的控件都是可見的。小計(jì)合計(jì)是基于表控件的，定義的小計(jì)合計(jì)只對該表起作用，不影響其他綁定到相同數(shù)據(jù)集的控件。l 公式列l(wèi) 點(diǎn)擊數(shù)據(jù)處理菜單下的公式列菜單項(xiàng)，就會彈出如圖17-60所示的公式列定義界面。圖17-60公式列定義界面在查詢結(jié)果集下拉框中選中要定義公式列的數(shù)據(jù)集，如果是添加公式列，則數(shù)據(jù)集中可選的列名會出現(xiàn)在可選列列表中（如果是預(yù)定義的公式列，則可選列是數(shù)據(jù)集的原始列，如果是運(yùn)行態(tài)定義的公式列，可選列還包括已經(jīng)定義的公式列），雙擊列項(xiàng)可將該列添加到公式表達(dá)式中

50、，公式列中可用函數(shù)和操作符也已列出，均是通過雙擊可以添加到表達(dá)式中。添加公式列需要指定公式列名和公式類型，也可以給公式列指定列格式。公式列表達(dá)式只支持Ascii字符，不支持中文，如果表達(dá)式有誤，則運(yùn)行態(tài)下公式列內(nèi)各行的值均為空。如果是刪除公式列，列表中列出了當(dāng)前數(shù)據(jù)集已定義的公式列，選中即可刪除。修改公式列界面與添加公式列類似。l 小計(jì)合計(jì)設(shè)計(jì)界面模型時在表控件的表頭上彈出右鍵菜單，點(diǎn)擊小計(jì)合計(jì)菜單項(xiàng)可彈出設(shè)置小計(jì)合計(jì)的界面，如圖17-61。圖17-61小計(jì)合計(jì)設(shè)置界面根據(jù)表綁定的查詢定義的不同，上圖中的界面會稍有區(qū)別，分別說明如下。普通表：可以設(shè)置根據(jù)什么列分組，進(jìn)行小計(jì)合計(jì)，以及分組列的升降序；可以設(shè)置小計(jì)合計(jì)計(jì)算與否，以及小計(jì)行和合計(jì)行標(biāo)志展現(xiàn)；可以設(shè)置需要小計(jì)合計(jì)的列，以及統(tǒng)計(jì)運(yùn)算的類型，目前支持的運(yùn)算類型有求和、最大值和最小值；可以設(shè)置統(tǒng)計(jì)數(shù)據(jù)放置在原始數(shù)據(jù)之前還是放置在后。第一類交叉表（投影交叉表）：定