📄 ch05.htm
字号:
-------------------- ---------
Ma Bell 150
Reading R.R. 245.34
Ma Bell 200.32
Local Utilities 98
Joes Stale $ Dent 150
Cash 60
Abes Cleaners 24.35
Cash 25
Joans Gas 25.1
Abes Cleaners 10.5
Cash 34
11 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The ordering in this list is identical to the ordering of the list at the beginning
of the section (without <TT>ASC</TT>) because <TT>ASC</TT> is the default. This query
also shows that the expression used after the <TT>ORDER BY</TT> clause does not have
to be in the <TT>SELECT</TT> statement. Although you selected only <TT>PAYEE</TT>
and <TT>AMOUNT</TT>, you were still able to order the list by <TT>CHECK#</TT>.</P>
<P>You can also use <TT>ORDER BY</TT> on more than one field. To order <TT>CHECKS</TT>
by <TT>PAYEE</TT> and <TT>REMARKS</TT>, you would query as follows:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT *
</B>2<B> FROM CHECKS
</B>3<B> ORDER BY PAYEE, REMARKS;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF"> CHECK# PAYEE AMOUNT REMARKS
-------- -------------------- -------- ------------------
20 Abes Cleaners 10.5 All Dry Clean
9 Abes Cleaners 24.35 X-Tra Starch
8 Cash 60 Trip to Boston
21 Cash 34 Trip to Dayton
16 Cash 25 Wild Night Out
17 Joans Gas 25.1 Gas
5 Joes Stale $ Dent 150 Groceries
4 Local Utilities 98 Gas
3 Ma Bell 200.32 Cellular Phone
1 Ma Bell 150 Have sons next time
2 Reading R.R. 245.34 Train to Chicago
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Notice the entries for Cash in the <TT>PAYEE</TT> column. In the previous <TT>ORDER
BY</TT>, the <TT>CHECK#</TT>s<TT> </TT>were in the order 16, 21, 8. Adding the field
<TT>REMARKS</TT> to the <TT>ORDER BY</TT> clause puts the entries in alphabetical
order according to <TT>REMARKS</TT>. Does the order of multiple columns in the <TT>ORDER
BY</TT> clause make a difference? Try the same query again but reverse <TT>PAYEE</TT>
and <TT>REMARKS</TT>:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT *
</B>2<B> FROM CHECKS
</B> 3<B> ORDER BY REMARKS, PAYEE;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT><FONT COLOR="#0066FF"></FONT></H5>
<PRE><FONT COLOR="#0066FF"> CHECK# PAYEE AMOUNT REMARKS
-------- -------------------- -------- --------------------
20 Abes Cleaners 10.5 All Dry Clean
3 Ma Bell 200.32 Cellular Phone
17 Joans Gas 25.1 Gas
4 Local Utilities 98 Gas
5 Joes Stale $ Dent 150 Groceries
1 Ma Bell 150 Have sons next time
2 Reading R.R. 245.34 Train to Chicago
8 Cash 60 Trip to Boston
21 Cash 34 Trip to Dayton
16 Cash 25 Wild Night Out
9 Abes Cleaners 24.35 X-Tra Starch
11 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>As you probably guessed, the results are completely different. Here's how to list
one column in alphabetical order and list the second column in reverse alphabetical
order:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT *
</B>2<B> FROM CHECKS
</B>3 <B> ORDER BY PAYEE ASC, REMARKS DESC;</B>
CHECK# PAYEE AMOUNT REMARKS
-------- -------------------- -------- ------------------
9 Abes Cleaners 24.35 X-Tra Starch
20 Abes Cleaners 10.5 All Dry Clean
16 Cash 25 Wild Night Out
21 Cash 34 Trip to Dayton
8 Cash 60 Trip to Boston
17 Joans Gas 25.1 Gas
5 Joes Stale $ Dent 150 Groceries
4 Local Utilities 98 Gas
1 Ma Bell 150 Have sons next time
3 Ma Bell 200.32 Cellular Phone
2 Reading R.R. 245.34 Train to Chicago
11 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>In this example <TT>PAYEE</TT> is sorted alphabetically, and <TT>REMARKS</TT>
appears in descending order. Note how the remarks in the three checks with a <TT>PAYEE</TT>
of Cash are sorted.
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>TIP:</B></FONT><B> </B>If you know that a column you want
to order your results by is the first column in a table, then you can type <TT>ORDER
BY 1</TT> in place of spelling out the column name. See the following example.
<HR>
</P>
</BLOCKQUOTE>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT *
</B> 2<B> FROM CHECKS
</B>3<B> ORDER BY 1;</B>
CHECK# PAYEE AMOUNT REMARKS
-------- -------------------- -------- ------------------
1 Ma Bell 150 Have sons next time
2 Reading R.R. 245.34 Train to Chicago
3 Ma Bell 200.32 Cellular Phone
4 Local Utilities 98 Gas
5 Joes Stale $ Dent 150 Groceries
8 Cash 60 Trip to Boston
9 Abes Cleaners 24.35 X-Tra Starch
16 Cash 25 Wild Night Out
17 Joans Gas 25.1 Gas
20 Abes Cleaners 10.5 All Dry Clean
21 Cash 34 Trip to Dayton
11 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>This result is identical to the result produced by the <TT>SELECT</TT> statement
that you used earlier today:</P>
<PRE><FONT COLOR="#0066FF">SELECT * FROM CHECKS ORDER BY CHECK#;
</FONT></PRE>
<H2><FONT COLOR="#000077">The GROUP BY Clause</FONT></H2>
<P>On Day 3 you learned how to use aggregate functions (<TT>COUNT</TT>, <TT>SUM</TT>,
<TT>AVG</TT>, <TT>MIN</TT>, and <TT>MAX</TT>). If you wanted to find the total amount
of money spent from the slightly changed <TT>CHECKS</TT> table, you would type:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT *
FROM CHECKS;
</B></FONT></PRE>
<P>Here's the modified table:</P>
<H5>OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">CHECKNUM PAYEE AMOUNT REMARKS
======== =========== =============== ======================
1 Ma Bell 150 Have sons next time
2 Reading R.R. 245.34 Train to Chicago
3 Ma Bell 200.33 Cellular Phone
4 Local Utilities 98 Gas
5 Joes Stale $ Dent 150 Groceries
16 Cash 25 Wild Night Out
17 Joans Gas 25.1 Gas
9 Abes Cleaners 24.35 X-Tra Starch
20 Abes Cleaners 10.5 All Dry Clean
8 Cash 60 Trip to Boston
21 Cash 34 Trip to Dayton
30 Local Utilities 87.5 Water
31 Local Utilities 34 Sewer
25 Joans Gas 15.75 Gas
</FONT></PRE>
<P>Then you would type:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT SUM(AMOUNT)
FROM CHECKS;</B>
SUM
===============
1159.87
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>This statement returns the sum of the column <TT>AMOUNT</TT>. What if you wanted
to find out how much you have spent on each <TT>PAYEE</TT>? SQL helps you with the
<TT>GROUP BY</TT> clause. To find out whom you have paid and how much, you would
query like this:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT PAYEE, SUM(AMOUNT)
FROM CHECKS
GROUP BY PAYEE;</B>
PAYEE SUM
==================== ===============
Abes Cleaners 34.849998
Cash 119
Joans Gas 40.849998
Joes Stale $ Dent 150
Local Utilities 219.5
Ma Bell 350.33002
Reading R.R. 245.34
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The <TT>SELECT</TT> clause has a normal column selection, <TT>PAYEE</TT>, followed
by the aggregate function <TT>SUM(AMOUNT</TT>). If you had tried this query with
only the <TT>FROM CHECKS</TT> that follows, here's what you would see:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT PAYEE, SUM(AMOUNT)
FROM CHECKS;
</B>
Dynamic SQL Error
-SQL error code = -104
-invalid column reference
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>SQL is complaining about the combination of the normal column and the aggregate
function. This condition requires the <TT>GROUP BY</TT> clause. <TT>GROUP BY</TT>
runs the aggregate function described in the <TT>SELECT</TT> statement for each grouping
of the column that follows the <TT>GROUP BY</TT> clause. The table <TT>CHECKS</TT>
returned 14 rows when queried with <TT>SELECT * FROM CHECKS</TT>. The query on the
same table, <TT>SELECT PAYEE, SUM(AMOUNT) FROM CHECKS GROUP BY PAYEE</TT>, took the
14 rows in the table and made seven groupings, returning the <TT>SUM</TT> of each
grouping.</P>
<P>Suppose you wanted to know how much you gave to whom with how many checks. Can
you use more than one aggregate function?</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT PAYEE, SUM(AMOUNT), COUNT(PAYEE)
FROM CHECKS
GROUP BY PAYEE;</B>
PAYEE SUM COUNT
==================== =============== ===========
Abes Cleaners 34.849998 2
Cash 119 3
Joans Gas 40.849998 2
Joes Stale $ Dent 150 1
Local Utilities 219.5 3
Ma Bell 350.33002 2
Reading R.R. 245.34 1
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>This SQL is becoming increasingly useful! In the preceding example, you were able
to perform group functions on unique groups using the <TT>GROUP BY </TT>clause. Also
notice that the results were ordered by payee. <TT>GROUP BY</TT> also acts like the
<TT>ORDER BY</TT> clause. What would happen if you tried to group by more than one
column? Try this:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT PAYEE, SUM(AMOUNT), COUNT(PAYEE)
FROM CHECKS
GROUP BY PAYEE, REMARKS;
</B>
PAYEE SUM COUNT
==================== =============== ===========
Abes Cleaners 10.5 1
Abes Cleaners 24.35 1
Cash 60 1
Cash 34 1
Cash 25 1
Joans Gas 40.849998 2
Joes Stale $ Dent 150 1
Local Utilities 98 1
Local Utilities 34 1
Local Utilities 87.5 1
Ma Bell 200.33 1
Ma Bell 150 1
Reading R.R. 245.34 1
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The output has gone from 7 groupings of 14 rows to 13 groupings. What is different
about the one grouping with more than one check associated with it? Look at the entries
for Joans Gas:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT PAYEE, REMARKS
FROM CHECKS
WHERE PAYEE = 'Joans Gas';</B>
PAYEE REMARKS
==================== ====================
Joans Gas Gas
Joans Gas Gas
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>You see that the combination of <TT>PAYEE</TT> and <TT>REMARKS</TT> creates identical
entities, which SQL groups together into one line with the <TT>GROUP BY</TT> clause.
The other rows produce unique combinations of <TT>PAYEE</TT> and <TT>REMARKS</TT>
and are assigned their own unique groupings.</P>
<P>The next example finds the largest and smallest amounts, grouped by <TT>REMARKS</TT>:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT MIN(AMOUNT), MAX(AMOUNT)
FROM CHECKS
GROUP BY REMARKS;</B>
MIN MAX
=============== ===============
245.34 245.34
10.5 10.5
200.33 200.33
15.75 98
150 150
150 150
34 34
60 60
34 34
87.5 87.5
25 25
24.35 24.35
</FONT></PRE>
<P>Here's what will happen if you try to include in the <TT>select</TT> statement
a column that has several different values within the group formed by <TT>GROUP BY</TT>:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT PAYEE, MAX(AMOUNT), MIN(AMOUNT)
FROM CHECKS
GROUP BY REMARKS;</B>
Dynamic SQL Error
-SQL error code = -104
-invalid column reference
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>This query tries to group <TT>CHECKS</TT> by <TT>REMARK</TT>. When the query finds
two records with the same <TT>REMARK</TT> but different <TT>PAYEE</TT>s, such as
the rows that have <TT>GAS</TT> as a <TT>REMARK</TT> but have <TT>PAYEE</TT>s of
<TT>LOCAL</TT> <TT>UTILITIES</TT> and <TT>JOANS</TT> <TT>GAS</TT>, it throws an error.</P>
<P>The rule is, Don't use the <TT>SELECT</TT> statement on columns that have multiple
values for the <TT>GROUP BY</TT> clause column. The reverse is not true. You can
use <TT>GROUP BY</TT> on columns not mentioned in the <TT>SELECT</TT> statement.
For example:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT PAYEE, COUNT(AMOUNT)
FROM CHECKS
GROUP BY PAYEE, AMOUNT;
</B>
PAYEE COUNT
==================== ===========
Abes Cleaners 1
Abes Cleaners 1
Cash 1
Cash 1
Cash HTTP/1.0 502 Proxy Error ( Connection timed out )
Via: educationser (MSProxy/1.0) for www.mcp.com
Content-Type: text/html
<HTML>
<HEAD>
<TITLE>HTTP Proxy Report Message</TITLE>
</HEAD>
<BODY><H1>HTTP Proxy reports: <H1><H3>The proxy server has encountered an error (Connection timed out).<H3></BODY>
</HTML>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -