📄 vcg08.htm
字号:
<PRE>
<FONT COLOR="#000080">SELECT Categories."Category ID" AS Cat,
{fn MONTH(Orders."Order Date")} AS Month,
Sum("Order Details"."Unit Price" * "Order Details".Quantity) AS Sales
FROM Categories, "Order Details", Orders, Products
WHERE Products."Category ID" = Categories."Category ID"
AND "Order Details"."Order ID" = Orders."Order ID"
AND "Order Details"."Product ID" = Products."Product ID"
GROUP BY Categories."Category ID", {fn MONTH(Orders."Order Date")}</FONT></PRE>
<P>Figure 8.2 shows the SQL window of MS Query displaying the preceding SQL statement and 13 of the last records of the query result table created by executing the statement.
<BR>
<P><B><A HREF="08vcg02.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/08vcg02.gif">Figure 8.2. An ANSI/ODBC SQL statement that summarizes category sales by month.</A></B>
<BR>
<P>You can use MS Query's conventional SQL processing method, in which you write the SQL statement in MS Query's SQL window or choose File | Execute SQL from MS Query to send an SQL statement directly to the ODBC driver using SQL pass-through. Figure 8.3 shows MS Query's Execute SQL dialog box, ready to execute the SQL statement in Figure 8.2.
<BR>
<P><B><A HREF="08vcg03.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/08vcg03.gif">Figure 8.3. MS Query's Execute SQL window, which is used to implement SQL pass-through.</A></B>
<BR>
<P>The rows that return when you specify monthly data require even more rearrangement than the quarterly data returned from the Access query.
<BR>
<BLOCKQUOTE>
<BLOCKQUOTE>
<HR ALIGN=CENTER>
<BR>
<NOTE><B>NOTE</B>
<BR>
<BR>MS Query can't represent the SQL statement graphically and therefore will give you an error message. The query will be successful regardless.</NOTE>
<BR>
<HR ALIGN=CENTER>
</BLOCKQUOTE></BLOCKQUOTE>
<BR>
<A NAME="E68E50"></A>
<H3 ALIGN=CENTER>
<CENTER>
<FONT SIZE=5 COLOR="#FF0000"><B>Using <I>TRANSFORM</I> and <I>PIVOT</I> to Create Crosstab Queries</B></FONT></CENTER></H3>
<BR>
<P>Access SQL's crosstab query feature solves the data cell rearrangement problem. Two Access SQL keywords, TRANSFORM and PIVOT, handle the denormalization of the data. The elements of an Access SQL crosstab query are as follows:
<BR>
<UL>
<LI>The object of the TRANSFORM predicate specifies the values for the data cells and must contain one of the SQL aggregate functions, such as SUM() or AVG(). You use the Format() function to determine the data's appearance.
<BR>
<BR>
<LI>The SELECT statement designates the row titles. The field you specify with the SELECT statement must be the same as the GROUP BY field.
<BR>
<BR>
<LI>The FROM statement specifies each table involved in the query. The tables may be identified by the INNER JOIN statements, without the conventional comma-separated <I>table_names</I> list.
<BR>
<BR>
<LI>The GROUP BY clause aggregates row data. Only one field is permitted in the GROUP BY clause of a conventional crosstab query.
<BR>
<BR>
<LI>The optional ORDER BY clause sorts the rows by the value of the single field specified in the SELECT and GROUP BY statements. You don't need to add the ORDER BY clause if the field specified by the SELECT statement is a primary key field and you want the rows ordered by the primary key.
<BR>
<BR>
<LI>The PIVOT statement determines the column grouping and supplies the column titles. Column titles consist of the value of the grouping criterion. The object of the PIVOT predicate takes the place of the second GROUP BY field of the SQL statements of the two examples in the preceding section.
<BR>
<BR>
</UL>
<P>Figure 8.4 shows the initial syntax of the Access SQL statement that creates the crosstab query return set shown in Figure 8.5. The statement in the SQL window of Figure 8.4 consists of the SQL statement illustrated in Figure 8.1, plus the necessary modifications to make the statement conform to the rules of crosstab queries given in the preceding list.
<BR>
<P><B><A HREF="08vcg04.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/08vcg04.gif">Figure 8.4. Executing a crosstab query with Microsoft Access.</A></B>
<BR>
<BLOCKQUOTE>
<BLOCKQUOTE>
<HR ALIGN=CENTER>
<BR>
<NOTE><B>NOTE</B>
<BR>
<BR>Figure 8.4 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>
<P><B><A HREF="08vcg05.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/08vcg05.gif">Figure 8.5. The crosstab query result set from the SQL statement in Figure 8.4.</A></B>
<BR>
<P>The CROSSTAB 95.MDB database includes a QueryDef object, qryCategoryByMonth (Crosstab), that creates the query result set shown in Figure 8.5. Select the query tab in the Database window in Access. When a query is open, the query toolbar is displayed. The first three buttons let you change from the Design, SQL, and Datasheet view modes. You can then display the SQL statement. After that, click the Datasheet view toolbar button to execute the query. When you execute the crosstab query shown in Figure 8.4, the Access database engine's query optimizer modifies the FROM and INNER JOIN statements as follows:
<BR>
<PRE>
<FONT COLOR="#000080">TRANSFORM Format(Sum([Order Details].[Unit Price] *
[Order Details].[Quantity]), "$#,##0") AS Sales
SELECT Categories.[Category Name]
FROM (Categories INNER JOIN Products ON Categories.[Category ID] =
Products.[Category ID])
INNER JOIN (Orders INNER JOIN [Order Details]
ON Orders.[Order ID] = [Order Details].[Order ID])
ON Products.[Product ID] = [Order Details].[Product ID]
WHERE ((Orders.[Order Date] Between #01/1/91# And #12/31/91#))
GROUP BY Categories.[Category Name]
ORDER BY Categories.[Category Name]
PIVOT Format([Order Date],"yyyy\Qq");</FONT></PRE>
<P>The preceding syntax example, which substitutes nested INNER JOIN syntax for individual INNER JOIN statements, more closely follows the SQL-92 syntax for JOIN statements. Each of the tables specified in the original FROM clause appears in the INNER JOIN statements, so it's not necessary to provide a separate <I>table_names</I> list. The first INNER JOIN of the FROM statement uses conventional INNER JOIN syntax. The succeeding nested INNER JOIN causes a join to be created between the result of the first INNER JOIN and the criterion of the last ON statement. The second and third INNER JOINs use the second ON criterion. Nested INNER JOIN statements are quite difficult to interpret correctly and are even more difficult to write.
<BR>
<P>A simpler approach to specifying INNER JOINs uses a compound WHERE clause. The following WHERE clause syntax achieves the same result as the INNER JOIN syntax of the preceding example:
<BR>
<PRE>
<FONT COLOR="#000080">TRANSFORM Format(Sum([Order Details].[Unit Price] *
[Order Details].[Quantity]), "$#,##0") AS Sales
SELECT Categories.[Category Name]
FROM Categories, [Order Details], Orders, Products
WHERE Products.[Category ID] = Categories.[Category ID]
AND [Order Details].[Order ID] = Orders.[Order ID]
AND [Order Details].[Product ID] = Products.[Product ID]
AND Orders.[Order Date] Between #01/1/91# And #12/31/91#
GROUP BY Categories.[Category Name]
ORDER BY Categories.[Category Name]
PIVOT Format([Order Date],"yyyy\Qq");</FONT></PRE>
<BLOCKQUOTE>
<BLOCKQUOTE>
<HR ALIGN=CENTER>
<BR>
<NOTE><B>TIP</B>
<BR>
<BR>If you use Access to create and test the text for SQL statements to generate crosstab queries, you'll find that Visual C++ executes Access 1.x crosstab query syntax without generating an error. However, your code will be simpler and more readable if you use the WHERE clause to create equi-joins.</NOTE>
<BR>
<HR ALIGN=CENTER>
</BLOCKQUOTE></BLOCKQUOTE>
<BR>
<A NAME="E69E156"></A>
<H4 ALIGN=CENTER>
<CENTER>
<FONT SIZE=4 COLOR="#FF0000"><B>Creating Fixed Column Headers with the <I>IN()</I> Predicate</B></FONT></CENTER></H4>
<BR>
<P>If you change the PIVOT statement in the preceding example from PIVOT Format([Order Date],"yyyy\Qq") to PIVOT Format([Order Date],"mmm yy") to create a monthly rather than a quarterly crosstab query, the column titles appear in the strange sequence shown in Figure 8.6. The PIVOT statement automatically orders the columns alphabetically.
<BR>
<P><B><A HREF="08vcg06.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/08vcg06.gif">Figure 8.6. Monthly column headings sorted alphabetically.</A></B>
<BR>
<P>You could solve the column sequence problem by changing the PIVOT statement to PIVOT Format([Order Date],"mm/yy") to provide column titles 01/95 through 12/96, but many people prefer three-letter abbreviations for months. Access SQL lets you use the IN() predicate to create fixed column names that appear in any sequence you specify. The following example demonstrates a PIVOT...IN statement that creates monthly column titles for any year:
<BR>
<PRE>
<FONT COLOR="#000080">PIVOT Format([Order Date],"mmm")
IN ("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep",
"Oct", "Nov", "Dec");</FONT></PRE>
<BLOCKQUOTE>
<BLOCKQUOTE>
<HR ALIGN=CENTER>
<BR>
<NOTE><B>CAUTION</B>
<BR>
<BR>You can't use the IN() predicate with SQL statements that use the full INNER JOIN syntax of Access, as shown in Figure 8.4. If you attempt to execute such a query, you receive the error message Can't repeat field_name in FROM statement.</NOTE>
<BR>
<HR ALIGN=CENTER>
</BLOCKQUOTE></BLOCKQUOTE>
<P>The preceding PIVOT statement gives the more satisfactory result shown in Figure 8.7.
<BR>
<P><B><A HREF="08vcg07.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/08vcg07.gif">Figure 8.7. The query from Figure 8.6 with fixed column heads.</A></B>
<BR>
<P>The only restriction on the values of the IN() predicate's arguments is that, except for case, each value must exactly match the values returned by the formatted PIVOT statement. If you misspell an argument value, you will find that the data rows of the column with the mistaken header are empty.
<BR>
<BLOCKQUOTE>
<BLOCKQUOTE>
<HR ALIGN=CENTER>
<BR>
<NOTE><B>NOTE</B>
<BR>
<BR>The Access 1.x documentation states that crosstab queries with fixed column heads execute faster than queries without the IN() predicate. In practice, the difference in execution speed is difficult to measure. Using the IN() predicate doesn't result in a demonstrable improvement in the performance of the preceding query when executed against the CROSSTAB 95.MDB or NorthWind.MDB database.</NOTE>
<BR>
<HR ALIGN=CENTER>
</BLOCKQUOTE></BLOCKQUOTE>
<BR>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -