企业开源网站程序,网站制作:网推宝|百度,wordpress视频页面模板,网站设计公司业务怎么做为了说明问题#xff0c;我们用SqlServer自带的事例数据库(Northwind)来进行验证#xff0c;所有的例子请放到Northwind中运行#xff0c;我可能会省略Use语句#xff0c;所引用的表#xff0c;都是Northwind中的#xff0c;下面我就不再说明了这里指的交叉表#xff0c…为了说明问题我们用SqlServer自带的事例数据库(Northwind)来进行验证所有的例子请放到Northwind中运行我可能会省略Use语句所引用的表都是Northwind中的下面我就不再说明了这里指的交叉表就是象Access的交叉表查询一样的效果比如Employees表中City字段代表了城市的名称TitleOfCourtesy代表称呼我们希望按照City和TitleOfCourtesy的情况来统计ReportsTo字段的合计数(本统计没有任何实际意义只是挑选一些记录包含重复内容的字段来说明情况)并显示成以下格式(TitleOfCourtesy作为行City作为列)探讨这个问题之前我们首先来看一下如何建立静态的交叉表也就是说列数固定的交叉表这种情况其实只要一句简单的Select查询就可以搞定SELECTTitleOfCourtesy,SUM(CASECityWHENLondonTHENReportsToELSENULLEND)AS[London City],SUM(CASECityWHENRedmondTHENReportsToELSENULLEND)AS[Redmond City],SUM(CASECityWHENSeattleTHENReportsToELSENULLEND)AS[Seattle City]FROMEmployeesGROUPBYTitleOfCourtesy其中利用了CASE语句判断如果是相应的列则取需要统计的ReportsTo数值否则取NULL然后再合计其中有两个常见问题说明一下a、用NULL而不用0是有道理的假如用0虽然求和函数SUM可以取到正确的数但类似COUNT函数(取记录个数)结果就不对了因为Null不算一条记录而0要算同理空字串()也是这样总之在这里应该用NULL,这样任何函数都没问题。b、假如在视图的设计界面保存以上的查询则会报错“没有输出列”从而无法保存其实只要在查询前面加上一段Create View ViewName AS ...ViewName是你准备给查询起的名称...就是我们的查询然后运行一下就可以生成视图了对于其他一些设计器不支持的语法也可以这样保存。以上查询作用也很大对于很多情况比如按照季度统计、按照月份统计等列头内容固定的情况这样就行了但往往大多数情况下列头内容是不固定的象City用户随时可能删除、添加一些城市这种情况我们就需要用存储过程来解决总体思路其实很简单首先检索列头信息形成一个游标然后遍历游标将上面查询语句里Case判断的内容用游标里的值替代形成一条新的Sql查询然后执行返回结果就可以了以下是我写的一个存储过程供大家参考CREATEprocedureCorssTabstrTabNameasvarchar(50)Employees,--此处放表名strColasvarchar(50)City,--表头分组依据字段strGroupasvarchar(50)TitleOfCourtesy,--分组字段strNumberasvarchar(50)ReportsTo,--被统计的字段strSumasvarchar(10)Sum--运算方式ASDECLAREstrSqlasvarchar(1000),strTmpColasvarchar(100)EXECUTE(DECLARE corss_cursor CURSOR FOR SELECT DISTINCTstrColfromstrTabNamefor read only)--生成游标beginSETnocountONSETstrsqlselectstrGroup,strSum(strNumber) AS [strSumofstrNumber]--查询的前半段OPENcorss_cursorwhile(00)BEGINFETCHNEXTFROMcorss_cursor--遍历游标将列头信息放入变量strTmpColINTOstrTmpColif(fetch_status0)breakSETstrsqlstrsql,strSum(CASEstrColWHENstrTmpColTHENstrNumberELSE Null END) AS [strTmpColstrCol]--构造查询ENDSETstrsqlstrsqlfromstrTabnamegroup bystrGroup--查询结尾EXECUTE(strsql)--执行IFerror0RETURNerror--如果出错返回错误代码CLOSEcorss_cursorDEALLOCATEcorss_cursorRETURN0--释放游标返回0表示成功endGO几点说明a、这是一个通用存储过程使用时strTabName、strCol、strGroup、strNumber、strSum几个变量设置一下就可以用到其他表上其中结果集的第二列我加了个合计列b、为了测试方便我在存储过程中设置了默认值就是前面提到的Employees表这样直接运行时就可以出来我上面提到的结果。c、使用时可以把上面的代码复制到企业管理器的查询设计界面Sql窗格或者查询分析器里运行一下(注意正确选择NorthWind数据库)就可以生成一个存储过程CorssTab然后直接运行CorssTab如果出现本文前面类似的窗格就表示运行成功了。d、假如用于其它表首先需要在你的用户数据库里生成此存储过程(当然也可以放到Master里然后再加个变量DataBase赋值为数据库名称然后在上面代码打开指定数据库这样所有的数据库都可以调用它)当你调用时采取以下格式CorssTab strTabName Orders, strCol DATEPART(yy, OrderDate),strGroup CustomerID, strNumber OrderID, strSum Count上面这条语句统计了NorthWind中Orders表里每个客户年度订单数量大家可以运行试一下效果虽然列头显示的名称不恰当但基本效果出来了相信大家通过对我的代码再作简单修改可以达到满意的交叉表效果。
