SQLStory摘录（三）————可扩展

<script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

<script type="text/javascript"> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

面向集合的结构化设计。这一点很多人都知道，可真正能够活用的就太少了。举一个简单的例子：
例1-3：有一个简单的数据表Orders，存储某商店的订单信息：
CREATETABLE[dbo].[ORDERS](
[ID][int]IDENTITY(1,1)NOTNULL,
[CustomerID][int]NOTNULL,
[OrderDate][datetime]NOTNULL
)ON[PRIMARY]
GO
CREATECLUSTEREDINDEX[CU_INX_OrderDate]ON[dbo].[ORDERS]([OrderDate])WITHFILLFACTOR=50ON[PRIMARY]
GO
ALTERTABLE[dbo].[ORDERS]WITHNOCHECKADD
CONSTRAINT[PK_ORDERS]PRIMARYKEYNONCLUSTERED([ID])
ON[PRIMARY]
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", 1 <script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

<script type="text/javascript"> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>