Select-sql周周谈-第二周

Select-sql周周谈-第二周2007-05-092001年8月4日第二周

A. 销售明细

销售明细情况在order Details表中都有反映，只是缺少了ProductName，所以我们连接Procucts与Order Details 表就能得到完整的销售明细情况。这其实在上周的最后一个查询已经完全做到了，这里就重复一次。还有提请大家注意的是，关于销售实际金额的计算公式为：数量＊单价＊（1-折扣）。

在SQL Server中，这句语句如下：

SELECT [ORDER DETAILS].ORDERID,[ORDER DETAILS].PRODUCTID,PRODUCTS.PRODUCTNAME,
[ORDER DETAILS].UNITPRICE,[ORDER DETAILS].QUANTITY,[ORDER DETAILS].DISCOUNT,
[ORDER DETAILS].UNITPRICE*[ORDER DETAILS].QUANTITY*（1-[ORDER DETAILS].DISCOUNT） AS EXTENDEDPRICE
FROM [ORDER DETAILS] INNER JOIN PRODUCTS ON [ORDER DETAILS].PRODUCTID=PRODUCTS.PRODUCTID

在VFP中也没有什么变化（上周笔者已经在Visual FoxPro中把Order Details 表更名为Order_Details），语句如下：

SELECT ORDER_DETAILS.ORDERID,ORDER_DETAILS.PRODUCTID,PRODUCTS.PRODUCTNAME,;
ORDER_DETAILS.UNITPRICE,ORDER_DETAILS.QUANTITY,ORDER_DETAILS.DISCOUNT,;
ORDER_DETAILS.UNITPRICE*ORDER_DETAILS.QUANTITY*（1-ORDER_DETAILS.DISCOUNT） AS EXTENDEDPRICE;
FROM ORDER_DETAILS INNER JOIN PRODUCTS ON ORDER_DETAILS.PRODUCTID=PRODUCTS.PRODUCTID

B. 销售合计

每一张销售单据中对应着数条明细产品销售，那么销售单据的金额合计怎么计算呢？很简单只要在Order Details中把同一张单据的销售金额累计在一起,在SQL Server实现如下：

SELECT [ORDER DETAILS].ORDERID,SUM（[ORDER DETAILS].UNITPRICE*[ORDER DETAILS].QUANTITY*（1-[ORDER DETAILS].DISCOUNT）） AS SUBTOTALFROM [ORDER DETAILS]

在Visual FoxPro中实现起来也是一样的：

SELECT ORDER_DETAILS.ORDERID,;
SUM（ORDER_DETAILS.UNITPRICE*ORDER_DETAILS.QUANTITY*（1-ORDER_DETAILS.DISCOUNT）） AS SUBTOTAL;
FROM ORDER_DETAILS GROUP BY ORDER_DETAILS.ORDERID

C.只想知道销售单号为：10248的销售合计怎么办？

上例中我们得到的结果集是所有销售单的销售合计情况，现在我们要在此基础上过滤掉其他所有的单据合计，只要销售单号为：10248的销售情况。在SQL Server中有两种做法：其一，先分别合计所有单据（上例中已完成），在过滤不合条件的合计信息；其二，先过滤所有不合条件的销售明细，在合计剩下的销售明细。

做法一：（用Having关键字）

SELECT [ORDER DETAILS].ORDERID,SUM（[ORDER DETAILS].UNITPRICE*[ORDER DETAILS].QUANTITY*（1-[ORDER DETAILS].DISCOUNT）） AS SUBTOTAL
FROM [ORDER DETAILS] GROUP BY [ORDER DETAILS].ORDERID HAVING [ORDER DETAILS].ORDERID=10248做法二：（用Where关键字）

SELECT [ORDER DETAILS].ORDERID,SUM（[ORDER DETAILS].UNITPRICE*[ORDER DETAILS].QUANTITY*（1-[ORDER DETAILS].DISCOUNT）） AS SUBTOTAL
FROM [ORDER DETAILS] WHERE [ORDER DETAILS].ORDERID=10248 GROUP BY [ORDER DETAILS].ORDERID

在Visual FoxPro中同样可以以这两种方法实现：

做法一：（用Having关键字）

SELECT ORDER_DETAILS.ORDERID,；
SUM（ORDER_DETAILS.UNITPRICE*ORDER_DETAILS.QUANTITY*（1-ORDER_DETAILS.DISCOUNT）） AS SUBTOTAL;
FROM ORDER_DETAILS GROUP BY ORDER_DETAILS.ORDERID HAVING ORDER_DETAILS.ORDERID=10248

做法二：（用Where关键字）

SELECT ORDER_DETAILS.ORDERID,;
SUM（ORDER_DETAILS.UNITPRICE*ORDER_DETAILS.QUANTITY*（1-ORDER_DETAILS.DISCOUNT）） AS SUBTOTAL;
FROM ORDER_DETAILS WHERE ORDER_DETAILS.ORDERID=10248 GROUP BY ORDER_DETAILS.ORDERID

D.1997的产品销售情况

先来看看SQL Server中的实现吧：

SELECT CATEGORIES.CATEGORYNAME,PRODUCTS.PRODUCTNAME,
SUM（[ORDER DETAILS].UNITPRICE*[ORDER DETAILS].QUANTITY*（1-[ORDER DETAILS].DISCOUNT）） AS PRODUCTSALES
FROM （CATEGORIES INNER JOIN PRODUCTS ON CATEGORIES.CATEGORYID=PRODUCTS.CATEGORYID）
INNER JOIN
（ORDERS INNER JOIN [ORDER DETAILS] ON ORDERS.ORDERID=[ORDER DETAILS].ORDERID）
ON PRODUCTS.PRODUCTID=[ORDER DETAILS].PRODUCTID
WHERE ORDERS.SHIPPEDDATE>="19970101" AND ORDERS.SHIPPEDDATE<="19971231"
GROUP BY CATEGORIES.CATEGORYNAME,PRODUCTS.PRODUCTNAME

你也许会问，这里的Where字句能不能用Having字句替换，就像上例中那样？不行，因为数据集合经过分组合并以后，ShippedDate字段已经不存在（也可以理解为不具备任何意义）了，这是用它作为Having字句的过滤分组结果的依据就不可能了。所以本例中我们的做法是：连接所有的表，过滤掉ShippedDate不合要求的数据，然后按产品类别分组，如果类别相同就按产品名称分组，最终得到1997年的产品销售合计信息。

在Visual FoxPro中也是一样的实现：

SELECT CATEGORIES.CATEGORYNAME,PRODUCTS.PRODUCTNAME,;
SUM（ORDER_DETAILS.UNITPRICE*ORDER_DETAILS.QUANTITY*（1-ORDER_DETAILS.DISCOUNT）） AS PRODUCTSALES;
FROM （CATEGORIES INNER JOIN PRODUCTS ON CATEGORIES.CATEGORYID=PRODUCTS.CATEGORYID）;
INNER JOIN;
（ORDERS INNER JOIN ORDER_DETAILS ON ORDERS.ORDERID=ORDER_DETAILS.ORDERID）;
ON PRODUCTS.PRODUCTID=ORDER_DETAILS.PRODUCTID ;
WHERE ORDERS.SHIPPEDDATE>={^1997-01-01} AND ORDERS.SHIPPEDDATE<={^1997-12-31} ;
GROUP BY CATEGORIES.CATEGORYNAME,PRODUCTS.PRODUCTNAME

大家也许已经注意到了SQL Server中的日期表示与Visual FoxPro中很不一样。在SQL Server中以单引号为分隔符表示日期（可以认为是一个字符串），以下的写法都是合法的："1997-12-31";"19971231"。

在SQL Server比较日期的大小，除了可以用">、＝、<"号之外，也可以用Between关键字与DateDiff（）函数实现，分别如下：

用Between关键字：

SELECT CATEGORIES.CATEGORYNAME,PRODUCTS.PRODUCTNAME,
SUM（[ORDER DETAILS].UNITPRICE*[ORDER DETAILS].QUANTITY*（1-[ORDER DETAILS].DISCOUNT）） AS PRODUCTSALES
FROM （CATEGORIES INNER JOIN PRODUCTS ON CATEGORIES.CATEGORYID=PRODUCTS.CATEGORYID）
INNER JOIN
（ORDERS INNER JOIN [ORDER DETAILS] ON ORDERS.ORDERID=[ORDER DETAILS].ORDERID）
ON PRODUCTS.PRODUCTID=[ORDER DETAILS].PRODUCTID
WHERE ORDERS.SHIPPEDDATE BETWEEN "1997-01-01" AND "1997-12-31"
GROUP BY CATEGORIES.CATEGORYNAME,PRODUCTS.PRODUCTNAME

用DateDiff（）函数

SELECT CATEGORIES.CATEGORYNAME,PRODUCTS.PRODUCTNAME,
SUM（[ORDER DETAILS].UNITPRICE*[ORDER DETAILS].QUANTITY*（1-[ORDER DETAILS].DISCOUNT）） AS PRODUCTSALES
FROM （CATEGORIES INNER JOIN PRODUCTS ON CATEGORIES.CATEGORYID=PRODUCTS.CATEGORYID）
INNER JOIN
（ORDERS INNER JOIN [ORDER DETAILS] ON ORDERS.ORDERID=[ORDER DETAILS].ORDERID）
ON PRODUCTS.PRODUCTID=[ORDER DETAILS].PRODUCTID
WHERE DATEDIFF（DD,ORDERS.SHIPPEDDATE,"1997-01-01"）<=0 AND DATEDIFF（DD,ORDERS.SHIPPEDDATE,"1997-12-31"）>=0
GROUP BY CATEGORIES.CATEGORYNAME,PRODUCTS.PRODUCTNAME

在Visual FoxPro中还其他的日期比较方式呢？用Between关键字是可以的，如下：

SELECT CATEGORIES.CATEGORYNAME,PRODUCTS.PRODUCTNAME,;
SUM（ORDER_DETAILS.UNITPRICE*ORDER_DETAILS.QUANTITY*（1-ORDER_DETAILS.DISCOUNT）） AS PRODUCTSALES;
FROM （CATEGORIES INNER JOIN PRODUCTS ON CATEGORIES.CATEGORYID=PRODUCTS.CATEGORYID）;
INNER JOIN;
（ORDERS INNER JOIN ORDER_DETAILS ON ORDERS.ORDERID=ORDER_DETAILS.ORDERID）;
ON PRODUCTS.PRODUCTID=ORDER_DETAILS.PRODUCTID ;
WHERE ORDERS.SHIPPEDDATE BETWEEN {^1997-01-01} AND {^1997-12-31} ;
GROUP BY CATEGORIES.CATEGORYNAME,PRODUCTS.PRODUCTNAME

本周的内容就到这里了，这次我们讲解了分组的问题，并讨论了分组过滤的两种思路，这是值得关注的问题；还有就是在SQL Server中的日期表示方式以及日期值的比较方法。