为你讲解几个基本SQL PLUS命令(2)

activity.lst

SQL>
SQL> select Name, Title, CheckoutDate, ReturnedDate,
2 ReturnedDate-CheckoutDate as DaysOut /*Count Days*/
3 from BOOKSHELF_CHECKOUT
4 order by Name,CheckoutDate;
Mon Aug 28                                 page 1
           Checkout Log for 1/1/02-3/31/02

                                                Days
NAME         TITLE          CHECKOUTDATE RETURNEDDATE Out
------------ --------       ------------ ------------ ----
DORAH TALBOT EITHER/OR      02-JAN-02    10-JAN-02    8.00
             POLAR EXPRESS  01-FEB-02    15-FEB-02    14.00
             GOOD DOG, CARL 01-FEB-02    15-FEB-02    14.00
             MY LEDGER      15-FEB-02    03-MAR-02    16.00
********************                                -------
avg                                                 13.00

EMILY TALBOT   ANNE OF GREEN GABLES 02-JAN-02 20-JAN-02 18.00
MIDNIGHT MAGIC 20-JAN-02     03-FEB-02        14.00
HARRY POTTER AND   03-FEB-02 14-FEB-02        11.00
              THE GOBLET OF FIRE

********************                                -------
avg                                                 14.33

FRED FULLER  JOHN ADAMS  01-FEB-02   01-MAR-02  28.00
TRUMAN           01-MAR-02     20-MAR-02       19.00
********************                                -------
avg                                                 23.50

GERHARDT KENTGEN  WONDERFUL LIFE  02-JAN-02 02-FEB-02 31.00
MIDNIGHT MAGIC     05-FEB-02     10-FEB-02       5.00
THE MISMEASURE OF   13-FEB-02     05-MAR-02       20.00
MAN

********************                                -------
avg                                                 18.67

JED HOPKINS INNUMERACY   01-JAN-02  22-JAN-02  21.00
TO KILL A   15-FEB-02    01-MAR-02   14.00
MOCKINGBIRD

********************                              -------
avg                                               17.50

PAT LAVAY  THE SHIPPING NEWS  02-JAN-02  12-JAN-02  10.00
THE MISMEASURE OF   12-JAN-02     12-FEB-02     31.00
MAN

********************                              -------
avg                                               20.50

ROLAND BRANDT THE SHIPPING NEWS 12-JAN-02   12-MAR-02  59.00
THE DISCOVERERS   12-JAN-02     01-MAR-02     48.00
WEST WITH THE NIGHT 12-JAN-02     01-MAR-02     48.00
********************                               -------
avg                                                51.67

                                                  -------
avg                                                22.58
from the Bookshelf
SQL>

start activity.sql
rem Bookshelf activity report
set headsep !
ttitle 'Checkout Log for 1/1/02-3/31/02'
btitle 'from the Bookshelf'
column Name format a20
column Title format a20 word_wrapped
column DaysOut format 999.99
column DaysOut heading 'Days!Out'

break on Name skip 1 on report
compute avg of DaysOut on Name
compute avg of DaysOut on report

set linesize 100
set pagesize 60
set newpage 0
set feedback off

spool activity.lst

select Name, Title, CheckoutDate, ReturnedDate,
ReturnedDate-CheckoutDate as DaysOut /**//*Count Days*/
from BOOKSHELF_CHECKOUT
order by Name,CheckoutDate;

spool off