📄 vcg08.htm
字号:
<HTML>
<HEAD>
<TITLE>vcg08.htm </TITLE>
<LINK REL="ToC" HREF="index-1.htm" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/index.htm">
<LINK REL="Index" HREF="htindex.htm" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/htindex.htm">
<LINK REL="Next" HREF="vcgp3.htm" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/vcgp3.htm">
<LINK REL="Previous" HREF="vcg07.htm" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/vcg07.htm"></HEAD>
<BODY BGCOLOR="#FFFFFF" TEXT="#000000" LINK="#0000FF" VLINK="#800080">
<A NAME="I0"></A><P ALIGN=CENTER>
<A HREF="vcg07.htm" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/vcg07.htm" TARGET="_self"><IMG SRC="blanprev.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/blanprev.gif" WIDTH = 37 HEIGHT = 37 BORDER = 0 ALT="Previous Page"></A>
<A HREF="index-1.htm" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/index.htm" TARGET="_self"><IMG SRC="blantoc.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/blantoc.gif" WIDTH = 37 HEIGHT = 37 BORDER = 0 ALT="TOC"></A>
<A HREF="vcgp3.htm" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/vcgp3.htm" TARGET="_self"><IMG SRC="blannext.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/blannext.gif" WIDTH = 37 HEIGHT = 37 BORDER = 0 ALT="Next Page"></A>
<HR ALIGN=CENTER>
<P>
<UL>
<UL>
<UL>
<LI>
<A HREF="#E68E49" >Using Crosstab Queries to Present Summary Data</A>
<UL>
<LI>
<A HREF="#E69E155" >Summary Data Created by the GROUP BY Clause</A></UL>
<LI>
<A HREF="#E68E50" >Using TRANSFORM and PIVOT to Create Crosstab Queries</A>
<UL>
<LI>
<A HREF="#E69E156" >Creating Fixed Column Headers with the IN() Predicate</A>
<LI>
<A HREF="#E69E157" >Creating a Classification Crosstab Query</A></UL>
<LI>
<A HREF="#E68E51" >Defining Action Queries</A>
<LI>
<A HREF="#E68E52" >Using the SQL Action Queries</A>
<UL>
<LI>
<A HREF="#E69E158" >Creating New Tables with Make-Table Queries</A>
<LI>
<A HREF="#E69E159" >Appending Records to Existing Tables</A>
<LI>
<A HREF="#E69E160" >Updating Data Values in Tables</A>
<LI>
<A HREF="#E69E161" >Deleting Records from Tables</A></UL>
<LI>
<A HREF="#E68E53" >Summary</A></UL></UL></UL>
<HR ALIGN=CENTER>
<A NAME="E66E21"></A>
<H1 ALIGN=CENTER>
<CENTER>
<FONT SIZE=6 COLOR="#FF0000"><B>- 8 -</B>
<BR><B>Running Crosstab and Action Queries</B></FONT></CENTER></H1>
<BR>
<P>Up to this point in the book, you've been introduced to only the most basic SQL queries: SELECT queries. This chapter describes the five additional types of queries that you can execute with SQL's data manipulation language: crosstab, append, update, delete, and make-table queries. The crosstab query is a special form of SELECT query that summarizes data in spreadsheet style, most often in time-series format. You are likely to find that 75 percent or more of the decision-support applications you create for your firm or clients include crosstab queries. In many cases, the crosstab query result set serves as the datasource for graphs and charts.
<BR>
<P>The remaining four types of queries described in this chapter are called <I>action queries</I>. Microsoft Access introduced the action query category for manipulative (Data Manipulation Language or DML) queries, which update the data in existing tables and create new tables. Action queries can substitute for lengthy blocks of Visual C++ code when you need to modify the content of tables in your database or when you want to create a new database table.
<BR>
<BR>
<A NAME="E68E49"></A>
<H3 ALIGN=CENTER>
<CENTER>
<FONT SIZE=5 COLOR="#FF0000"><B>Using Crosstab Queries to Present Summary Data</B></FONT></CENTER></H3>
<BR>
<P>Even before the early days of the personal computer and the VisiCalc spreadsheet application, managers were accustomed to viewing summary data in the row-column format of accountants' worksheets. Worksheets that are used for comparative performance analysis fall into two basic categories:
<BR>
<UL>
<LI>Time-series format implies that column titles contain date intervals, such as months, quarters, or years, and that row titles designate the category of data being compared. The underlying detail data is grouped by both data category and time period. Time-series summary data often is used to prepare line graphs or bar charts with sales as the y-axis (vertical axis or <I>abscissa</I>) and time as the x-axis (horizontal axis or <I>ordinate</I>).
<BR>
<BR>
<LI>Classification format uses column titles with the names of individuals, regions, divisions, or other organizational categories and data categories for the row titles. This format is restricted to a single, predetermined time period. (Multiple time periods can be represented by "stacking" worksheets with an identical format that can be consolidated by adding the values of corresponding cells.) The most common graphical representation of data from classification worksheets is the pie chart.
<BR>
<BR>
</UL>
<P>Today's spreadsheet applications, such as Lotus 1-2-3 and Microsoft Excel, replace the drudgery of preparing handwritten worksheets with automated computer-based procedures. However, the majority of the detail information that is needed to prepare summary data for management is available only in the fields of tables of relational accounting databases. Chapter 4, "Optimizing the Design of Relational Databases," demonstrated that most spreadsheet data formats violate the "no repeating groups" rule of relational tables. Conventional ANSI SQL statements return data in relational format, not spreadsheet format. Thus, a substantial amount of data manipulation ordinarily is required to create a time-series or classification spreadsheet from relational data. In fact, the "denormalization" process is almost as complex as that described in Chapter 4 for normalizing spreadsheet data to relational form.
<BR>
<P>The following sections describe how summary data is returned by ANSI SQL and how you use Access SQL's TRANSFORM and PIVOT statements to automate the denormalization of relational data to spreadsheet format.
<BR>
<BR>
<A NAME="E69E155"></A>
<H4 ALIGN=CENTER>
<CENTER>
<FONT SIZE=4 COLOR="#FF0000"><B>Summary Data Created by the <I>GROUP BY</I> Clause</B></FONT></CENTER></H4>
<BR>
<P>Figure 8.1 illustrates the format of data returned by conventional SELECT queries that use the SQL aggregate function SUM() to prepare data from which you can create a time-series worksheet. The tables employed to create the query return set, shown in the Crosstab query window of Figure 8.1, are Categories, Products, Orders, and Order Details. These tables' values are derived from the Northwind Traders sample database that accompanies Access. The query return set totals orders that were received for each of eight categories of products for the four calendar quarters of 1996. Thus, the grid contains eight records with a Category Name field and four quarter fields.
<BR>
<P><B><A HREF="08vcg01.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/08vcg01.gif">Figure 8.1. Using two GROUP BY criteria to sum the values of orders by category and date.</A></B>
<BR>
<BLOCKQUOTE>
<BLOCKQUOTE>
<HR ALIGN=CENTER>
<BR>
<NOTE><B>NOTE</B>
<BR>
<BR>Figure 8.1 is a composite. Access 95 won't show both a query SQL statement and the result of the query at the same time. You can toggle between the two views using Access 95's View menu when a query is open.</NOTE>
<BR>
<HR ALIGN=CENTER>
</BLOCKQUOTE></BLOCKQUOTE>
<BLOCKQUOTE>
<BLOCKQUOTE>
<HR ALIGN=CENTER>
<BR>
<NOTE><B>NOTE</B>
<BR>
<BR>The Access 95 CROSSTAB 95.MDB database (located in the CHAPTR08 subdirectory on the CD that comes with this book) is used for all the examples in this chapter. CROSSTAB 95.MDB contains records for the Categories, Products, and Employees tables that are the same as those in NorthWind.MDB (from Access), except that unnecessary fields have been deleted. The order dates have been updated. Records for years other than 1995 and 1996 have been deleted from the Orders and Order Details tables.
<BR>
<BR>Access 95 was used to perform all the sample queries in this chapter.</NOTE>
<BR>
<HR ALIGN=CENTER>
</BLOCKQUOTE></BLOCKQUOTE>
<P>The query that was used to create the data shown in Figure 8.1 is rather lengthy and deserves further explanation. The following list describes each element of the Access SQL statement that appears in Figure 8.1:
<BR>
<UL>
<LI>The three AS predicates of the statement
<BR>
<BR>
</UL>
<UL>
<UL>
<PRE>
<FONT COLOR="#000080">SELECT Categories.[Category ID] AS Cat, Format([Order Date],"yyyy\Qq")
AS Period, Format(SUM([Order Details].[Unit Price]*[Order Details].[Quantity]),
"$#,##0.00") AS Sales</FONT></PRE></UL></UL>
<UL>
alias the Category ID field as Cat and the two formatted fields as Period and Sales, respectively.
<BR>
<BR>
<LI>The first Format() function causes the order date to appear as the year (yyyy) followed by a literal Q (\Q) and the number of the quarter (q) that corresponds to the order date. A backslash (\) preceding a character in a format string designates the character that follows as a literal character rather than a format identifier character.
<BR>
<BR>
<LI>The SUM() SQL aggregate function totals the product of the Unit Price and Quantity fields of the Order Details table, which is needed to compute the extended amount of each invoice line item.
<BR>
<BR>
<LI>The Format() function embedded within the SUM() function adds the dollar sign ($), comma thousands separator (#,##0), and trailing cents columns (.00) to the values in the Sales column. This function isn't required in Access 1.x applications because Currency values are automatically formatted with "#,##0.00". Visual C++ bound controls and the grid control don't automatically format the Currency data type.
<BR>
<BR>
<LI>FROM Categories, Products, Orders, [Order Details] identifies the tables in use. The trailing comma separates this statement from the three join statements that follow.
<BR>
<BR>
<LI>Categories INNER JOIN Products ON Categories.[Category ID] = Products.[Category ID] creates the equi-join needed to identify products by category, because the Order Details table contains a Product ID field but not a Category ID field. (You could eliminate this join by substituting Products.[Category ID] for Categories.[Category ID] in the SELECT statement. The join is included in the example because you might want to use the Category Name field rather than the Category ID field for the row titles.)
<BR>
<BR>
<LI>Products INNER JOIN [Order Details] ON Products.[Product ID] = [Order Details].[Product ID] creates the equi-join needed to join the Order Details table to the Products table so that the values in the Order Details table for each product category are associated with the Category ID value.
<BR>
<BR>
<LI>Orders INNER JOIN [Order Details] ON Orders.[Order ID] = [Order Details].[Order ID] creates a join so that you can use the following WHERE clause to limit to a range of dates the records of Order Details that are totaled for each category.
<BR>
<BR>
<LI>The WHERE Orders.[Order Date] Between #01/1/95# And #12/31/96# criterion limits the summation to records for the years 1995 and 1996. The WHERE clause in Figure 8.1 is enclosed in double parentheses, which are not necessary.
<BR>
<BR>
<LI>The GROUP BY Categories.[Category ID], Format([Order Date],"yyyy\Qq") clause results in data being grouped by product category and by the quarter of the year. The sequence of the grouping isn't significant in this query. It's customary to use the same Format() function in the GROUP BY clause as is used in the SELECT statement that displays the grouping value. The literal \Q, however, isn't necessary to achieve the proper grouping.
<BR>
<BR>
</UL>
<P>It's clear from the rows returned by the preceding query and illustrated in Figure 8.1 that a substantial rearrangement of worksheet cells is required if you import this data into a worksheet that is intended to display data in time-series format.
<BR>
<P>You can achieve a result that is similar to the preceding example with ANSI/ODBC SQL using Microsoft Query. The following ANSI/ODBC SQL statement returns sales for each of the eight categories by month in 96 rows and also illustrates the use of the scalar ODBC date function MONTH() in SQL statements that are processed by ODBC drivers:
<BR>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -