Select-sql JoJo talk-second week

Second week of August 4, 2001

A. Sales Details

Sales details are reflected in the Order Details table, but lack of ProductName, so we connect the procucts and Order Details table can get complete sales details. This last week's final query has been completely done, and here it is repeated. It is also noted that the calculated formula for the actual amount of sales is: quantity * unit Price * (1-discount).

In SQL Server, this statement is as follows:

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 The [order Details]. Productid=products. PRODUCTID

There is no change in VFP (the author has changed the Order Details table to Order_Details in Visual FoxPro last week), the following statement:

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 the products on Order_Details. Productid=products. PRODUCTID

B. Total Sales

Each sales document corresponds to a number of product sales, then the total amount of the sales document how to calculate it? It's simple. As long as the sales amount of the same document is accumulated in the order details, it is implemented in SQL Server as follows:

SELECT [Order DETAILS]. Orderid,sum ([Order DETAILS]. Unitprice*[order DETAILS]. quantity* (1-[order DETAILS). DISCOUNT) as Subtotalfrom [order DETAILS]

The same is true in 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. Only want to know the sales number is: 10248 sales total?

The result set we obtained in the example above is the total sales totals for all sales orders, and now we are going to filter out all other document totals on this basis as long as the sales number is: 10248 sales. There are two ways to do this in SQL Server: First, aggregate all the documents separately (completed in the example above), filter the unqualified aggregate information, and then filter all the unqualified sales details, and then add the remaining sales details.

Procedure one: (with having keyword)

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 has [order DETAILS]. orderid=10248

Procedure two: (with the WHERE keyword)

SELECT [Order DETAILS]. Orderid,sum ([Order DETAILS]. Unitprice*[order DETAILS]. quantity* (1-[order DETAILS). DISCOUNT)) as SUBTOTAL
From [the Order Details] WHERE [order Details]. orderid=10248 GROUP by [order DETAILS]. ORDERID

This can also be accomplished in the Visual FoxPro in these two ways:

Procedure one: (with having keyword)

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 has order_details. orderid=10248

Procedure two: (with the WHERE keyword)

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

Sales of d.1997 Products

Let's take a look at the implementation in SQL Server:

SELECT CATEGORIES. Categoryname,products. PRODUCTNAME,
SUM ([Order DETAILS]. Unitprice*[order DETAILS]. quantity* (1-[order DETAILS). DISCOUNT)) as ProductSales
From (CATEGORIES INNER JOIN the 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

You may ask, where can the where sentence be replaced with the HAVING clause, as in the example above? No, because the data set is merged after grouping, the ShippedDate field is no longer present (or understood to have no meaning), it is not possible to use it as the basis for filtering grouped results of the HAVING clause. So our practice in this case is to connect all the tables, filter out the ShippedDate data, and then group by product category, grouped by product name if the category is the same, and finally get 1997 Total product sales information.

The same is true in Visual FoxPro:

SELECT CATEGORIES. Categoryname,products. PRODUCTNAME,;
SUM (Order_Details. Unitprice*order_details. quantity* (1-order_details. DISCOUNT)) as ProductSales;
From (CATEGORIES INNER JOIN the 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

You may have noticed that the date representation in SQL Server is very different from the Visual FoxPro. In SQL Server, the date (which can be considered a string) is represented by a single quotation mark as a delimiter, and the following are all legal: ' 1997-12-31 '; 19971231 '.

The size of the SQL Server comparison date, in addition to the >, =, <, can also be implemented with the between keyword and the DateDiff () function, respectively, as follows:

Use the Between keyword:

SELECT CATEGORIES. Categoryname,products. PRODUCTNAME,
SUM ([Order DETAILS]. Unitprice*[order DETAILS]. quantity* (1-[order DETAILS). DISCOUNT)) as ProductSales
From (CATEGORIES INNER JOIN the 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

Using the DateDiff () function

SELECT CATEGORIES. Categoryname,products. PRODUCTNAME,
SUM ([Order DETAILS]. Unitprice*[order DETAILS]. quantity* (1-[order DETAILS). DISCOUNT)) as ProductSales
From (CATEGORIES INNER JOIN the 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

What are the other date comparison methods in Visual FoxPro? Using the Between keyword is OK, as follows:

SELECT CATEGORIES. Categoryname,products. PRODUCTNAME,;
SUM (Order_Details. Unitprice*order_details. quantity* (1-order_details. DISCOUNT)) as ProductSales;
From (CATEGORIES INNER JOIN the 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

This week's content is here, this time we explained the problem of grouping, and discussed the two ideas of packet filtering, this is a matter of concern; there is the way in which the date is represented in SQL Server and the comparison method of date values.