MSSQL网站制作之SQL Story摘录（三）――――可扩大计划...
既能够作为一个单独的应用程序应用在客户端服务器网络环境中，也能够作为一个库而嵌入到其他的软件中。计划面向汇合的布局化计划。这一点良多人都晓得，可真正可以活用的就太少了。举一个复杂的例子：
例1-3：有一个复杂的数据表Orders，存储某商铺的定单信息：
CREATETABLE.(
IDENTITY(1,1)NOTNULL,
NOTNULL,
NOTNULL
)ON
GO
CREATECLUSTEREDINDEXON.()WITHFILLFACTOR=50ON
GO
ALTERTABLE.WITHNOCHECKADD
CONSTRAINTPRIMARYKEYNONCLUSTERED()
ON
GO
表中如今有以下数据：
IDCustomerIDOrderDate
----------------------------------------------------------------------------
111999-1-4
2101999-3-5
3221999-5-2
421999-6-7
522000-3-6
71012001-5-3
8102001-6-5
61012002-4-2
那末，我们怎样天生一个1999-2002的年度定单数报表呢（四年只要8个定单？我为了演示便利才如许做的，这其实不代表实在的情形：P）？如今，我给出实践报表的数据格局，读者们请先试一下这个语句的写法
CustomerID1999200020012002
--------------------------------------
11000
21100
101010
221000
1010011
最直不雅的设法，是在前台，用别的言语完成这一功效。不外有一个举措，能够用SQL言语来完成它。并且纷歧定比你想像的更庞大：
SELECTCustomerID,
SUM(CASEWHENYEAR(isnull(OrderDate,0))=1999THEN1ELSE0END)AS"1999",
SUM(CASEWHENYEAR(isnull(OrderDate,0))=2000THEN1ELSE0END)AS"2000",
SUM(CASEWHENYEAR(isnull(OrderDate,0))=2001THEN1ELSE0END)AS"2001",
SUM(CASEWHENYEAR(isnull(OrderDate,0))=2002THEN1ELSE0END)AS"2002"
FROMORDERS
GROUPBYCustomerID
我想这时候会有伴侣提出InterBase不撑持Case的成绩。不外即便云云，我仍是要向人人保举这类写法。由于它幽美、简便，不但我们读着好懂，还能够很便利地写出程序来主动天生它。现实上，Case关头字已经是SQL尺度之一，局势所趋，会有愈来愈多的数据库体系撑持它的。
那末它又是怎样来的呢？我在计划这个语句时是如许的思绪：
1、我们必要一个同时在工夫和客户两个坐标轴上睁开的报表；
2、纵向上，我们要为每位客户创建一行数据，这个对照好办，我们起首断定了这个语句会有一个基础框架
SELECTCustomerID,
………………
FROMORDERS
GROUPBYCustomerID
假如不辨别年度，已下语句就是我们要的了局
SELECTCustomerID,
COUNT(ID)ASORDERS_COUNT,
FROMORDERS
GROUPBYCustomerID
3、设一切定单为一选集，那末这个汇合的总数用以下语句来统计：
SELECTCOUNT(ID)FROMORDERS
横向上，我们为每年度的定单数界说一列，以1999年为例，取年份为1999年的定单子集的元素数为
SELECTSUM(CASEWHENYEAR(isnull(OrderDate,0))=1999THEN1ELSE0END)AS"1999"
FROMORDERS
别的年份依此类推，我们失掉每年的定单数：
SELECTSUM(CASEWHENYEAR(isnull(OrderDate,0))=1999THEN1ELSE0END)AS"1999",
SUM(CASEWHENYEAR(isnull(OrderDate,0))=2000THEN1ELSE0END)AS"2000",
SUM(CASEWHENYEAR(isnull(OrderDate,0))=2001THEN1ELSE0END)AS"2001",
SUM(CASEWHENYEAR(isnull(OrderDate,0))=2002THEN1ELSE0END)AS"2002"
FROMORDERS
其前往了局以下：
1999200020012002
--------------------------------------------
4121
(所影响的行数为1行)
4、顾及到干系型数据库“诡异”的NULL值成绩后，综合2、3步，我们得出终极的语句：
SELECTCustomerID,
SUM(CASEWHENYEAR(isnull(OrderDate,0))=1999THEN1ELSE0END)AS"1999",
SUM(CASEWHENYEAR(isnull(OrderDate,0))=2000THEN1ELSE0END)AS"2000",
SUM(CASEWHENYEAR(isnull(OrderDate,0))=2001THEN1ELSE0END)AS"2001",
SUM(CASEWHENYEAR(isnull(OrderDate,0))=2002THEN1ELSE0END)AS"2002"
FROMORDERS
GROUPBYCustomerID
如今这个报表布局明晰分明。扩大性极强。好比来岁我们必要2003年的统计数据，只需再依葫芦画瓢，来一列
SUM(CASEWHENYEAR(isnull(OrderDate,0))=2003THEN1ELSE0END)AS"2003"
加在最初就能够了，它是选集中的2003年纪据的子集。另有，用来判别空值的isnull函数纷歧定一切的数据库都有，不妨，只需在Case的分支里加一行
WHENOrderDateisNULLTHEN0
就能够了。基于这个头脑，我们能够很简单地写出一个存储历程，只需给定起迄年份，就能够天生一个完全的年度报表。因为一切的运算都在服务器端运转，而且是跟着数据检索一次就完成了。它的速率快于客户真个报表。并且传输的数据量也少，能够无效加重收集负载。
在《SQLServer6.5手艺内情》中，有一个相似的例子。不外作者利用的语句布局比我的庞大，他的例子中，From关头字是从一个子查询导出表当选择的数据，这让我百思不得其解。大概6.5版的MSSQLServer还不撑持我的写法，大概那样写功能更好。作者并没有申明，我也一向没无机会打仗到MSSQLServer6.5。
关于InterBase，我还没有举措用充足文雅的语句天生这个报表。这次要是因为InterBase不撑持Case。不外，假如你对语句的功能和美感请求不高的话，上面这个语句能够完成与以上的SQLServer版原形同的功效：
SELECTO.CUSTOMERID,
(SELECTCOUNT(I.ID)
FROMORDERSI
WHERE(I.CUSTOMERID=O.CUSTOMERID)
AND(EXTRACT(YEARFROMI.ORDERDATE)=1999))
asCOUNT_1999,
(SELECTCOUNT(I.ID)
FROMORDERSI
WHERE(I.CUSTOMERID=O.CUSTOMERID)
AND(EXTRACT(YEARFROMI.ORDERDATE)=2000))
asCOUNT_2000,
(SELECTCOUNT(I.ID)
FROMORDERSI
WHERE(I.CUSTOMERID=O.CUSTOMERID)
AND(EXTRACT(YEARFROMI.ORDERDATE)=2001))
asCOUNT_2001,
(SELECTCOUNT(I.ID)
FROMORDERSI
WHERE(I.CUSTOMERID=O.CUSTOMERID)
AND(EXTRACT(YEARFROMI.ORDERDATE)=2002))
asCOUNT_2002
FROMORDERSO
GROUPBYO.CUSTOMERID
按照SQLServer版本，我们完成了InterBase版的年度报表。分歧的是因为利用了相干子查询统计数据，它的效力会差一些(幸亏你不必要立即更新你的年度报表吧)。不外因为它一样是基于面向汇合的计划构架，最少我们包管了它的可扩大性。只是很分明的，当子查询版本中增添一列年度统计，所带来的开支增加会比Case版本多良多。假如你对速率请求较高，仍是在客户端另写程序天生吧。
InterBase数据库的用户会在这个示例中碰到良多不中意的中央：不撑持主动标识列、没有聚簇索引、没有Case、没有……更可爱的是，这个数据体系的开放源码版本没有附带ODBC或ADO驱动，在失掉一个收费的数据库体系后，我们却要为它花几十美圆往买一套ODBC驱动？
不外，InterBase正在失掉开放源码社区的撑持，Borland公司也经由过程DBExpress和InterClient手艺来为InterBase供应开放的接口（今朝DBExpress驱动基础上也只存在于Borland高贵的企业版开辟工具中:(）。只需每个InterBase的程序员和用户都为这个属于我们本人的软件做出奉献，它的出路还很光亮。
面向汇合的计划办法固然只合用于特定的方针，并非通用的软件计划办法。但也不是一言半语能说清的，今后的章节中，我们会一向实践这类计划办法，还会有专门的章节会商这个成绩。当时，我们的示例数据库也建立的对照完全了，我大概会给出更有用的年度定单统计报表。如今，我们先复杂地总结一下：
1、界说我们要天生的了局集的布局；
2、找出了局集的数据来历，界说选集；
3、界说了局集的取值局限，界说所取的子集；
4、完成操纵。
也就是说在php本地调用pdoprepare中的mysql_real_escape_string来操作query，使用的是本地单字节字符集，即编码为xbfx5cx27，并带入到mysql中查询，由于使用setnames设置了连接字符集. 大家注意一点。如下面的例子： 总感觉自己还是不会SQL 财务软件要用SQL也只是后台的数据库而已,软件都是成品的,当然多学东西肯定是有好处的.. 同样会为索引视图等应用带来麻烦。看看行级和事务级的快照数据放在tempdb中，就能感觉到目前架构的尴尬。 其中最有名的应该是row_number了。这个终于解决了用临时表生成序列号的历史，而且SQLServer2005的row_number比Oracle的更先进。因为它把Orderby集成到了一起，不用像Oracle那样还要用子查询进行封装。 发几份SQL课件，以飨阅者 SQLServer的异构移植功能个人感觉最好了。（如果对比过SQLServer的链接服务器和Oracle的透明网关的朋友会发现SQLServer的sp_addlinkedserver(openquery)异构数据库系列比Oracle真是强太多了。） 一直以来个人感觉SQLServer的优化器要比Oracle的聪明。SQL2005的更是比2k聪明了不少。（有次作试验发现有的语句在200万级时还比50万级的相同语句要快show_text的一些提示没有找到解释。一直在奇怪。）
页:
[1]