--交叉一般來講是分組統計的一種,形式更復雜,顯示更清淅,但數據庫本身并沒有提供實---現交叉表的功能,自己創建交叉表不僅要對過程、游標、臨時表、動態SQL等非常熟悉,而--且思路也要清淅,本例以PUBS.DBO.SALES表的數據做樣本: CREATE?PROCEDURE?UP_TEST( @T1?VARCHAR(30),@T2?VARCHAR(30), @T3?VARCHAR(30),@T4?VARCHAR(30))?AS --T1?表名,T2,T3是交叉表的兩上分類字段,T4是匯總字段 --T2是行字段,T3列字段 BEGIN DECLARE?@SQL?VARCHAR(7999),@FIELD?VARCHAR(30) ?SELECT?@SQL='SELECT?DISTINCT?'+@T3+'?FROM?'+@T1 ?CREATE?TABLE?#FIELD(FIELD?VARCHAR(30)) ?--將列字段提取到臨時表#FIELD中 ?INSERT?INTO?#FIELD?EXEC(@SQL) ?SELECT?@SQL='CREATE?TABLE?CROSS_TEST('+@T2+'?VARCHAR(30),' ?DECLARE?CUR_FIELD?CURSOR?LOCAL?FOR?SELECT?*?FROM?#FIELD ?OPEN?CUR_FIELD ?FETCH?CUR_FIELD?INTO?@FIELD ?WHILE?@@FETCH_STATUS=0?BEGIN ???SELECT?@FIELD='['+@FIELD+']' SELECT?@SQL=@SQL+@FIELD+'?DECIMAL(8,2)?DEFAULT?0,' ???FETCH?CUR_FIELD?INTO?@FIELD ?END ?SELECT?@SQL=LEFT(@SQL,LEN(@SQL)-1)+')' ?--創建臨時交叉表CROSS_TEST ?EXEC(@SQL) ?SELECT?@SQL='INSERT?INTO?CROSS_TEST('+@T2+')?SELECT?DISTINCT?'+@T2+'?FROM?'+@T1 --將行數據存入交叉表#CROSS_TEST EXEC(@SQL) ?--創建分組數據表TEMP ?SELECT?@SQL='CREATE?TABLE?TEMP('+@T2+'?VARCHAR(30),'+@T3+'?VARCHAR(30),'+@T4+'?DECIMAL(8,2))' ?EXEC(@SQL)? ?--將交叉匯總數據放入交叉表 ?SELECT?@SQL='SELECT??'+@T2+','+@T3+',?SUM(QTY)?QTY?FROM?'+@T1?+'?GROUP?BY?'+@T2+','+@T3 ?INSERT?INTO?TEMP?EXEC(@SQL) ?--將匯總數據寫入交叉表 ?DECLARE?CUR_SUM?CURSOR?LOCAL?FOR?SELECT?*?FROM?TEMP ?DECLARE?@F1?VARCHAR(30),@F2?VARCHAR(30),@QTY?DECIMAL(8,2),@Q1?VARCHAR(30) ?OPEN?CUR_SUM ?FETCH?CUR_SUM?INTO?@F1,@F2,@QTY ?WHILE?@@FETCH_STATUS=0?BEGIN ???SELECT?@F2='['+@F2+']',@Q1=CAST(@QTY?AS?VARCHAR(30)) ???SELECT?@SQL='UPDATE?CROSS_TEST?SET?'+@F2+'='+@Q1+'?WHERE?'+@T2+'='''+@F1+'''' ???EXEC(@SQL) ???FETCH?CUR_SUM?INTO?@F1,@F2,@QTY ?END ?CLOSE?CUR_SUM ?SELECT?*?FROM?CROSS_TEST ?DROP?TABLE?TEMP? ?DROP?TABLE?CROSS_TEST ?DROP?TABLE?#FIELD END -------------------------------------------------------- EXEC?UP_TEST?'SALES','TITLE_ID','STOR_ID','QTY' /*說明:字段加中括號為了處理字段中含有特殊字符,值得注意得是要實現交叉表的表必須有兩個分類,本例只支持分類字段的數據類型是字符型的,最大的問題就是高亮顯示這行的WHERE條件啦,字符類型字段查詢時條件必須加單引號,如果是數值類型就可以直接寫,所以數值類型的分類字段更容易實現一些,更可以融合在一個過程中。通常大家看到的交叉表都有行匯總與列匯總等信息,本例就沒有實現,最后一點工作大家自己練練手吧。*/
? 版權聲明
文章版權歸作者所有,未經允許請勿轉載。
THE END