一些SQL操作语句(7),Generating Summaries

mysql> select concat(rpad(owner,4,' '), time) from enq1 limit 10; +---------------------------------+ | concat(rpad(owner,4,' '), time) | +---------------------------------+ | 1 2008-05-15 | | 1 2008-05-22 | | 34 2008-05-22 | | 34 2008-05-22 | | 11 2008-05-22 | | 11 2008-05-22 | | 11 2008-05-22 | | 11 2008-05-23 | | 1 2008-05-23 | | 1 2008-05-23 | +---------------------------------+ 10 rows in set (0.00 sec) Dividing a Summary into Subgroups mysql> select owner, count(owner) from enq1 group by owner order by count(owner) desc; +-------+--------------+ | owner | count(owner) | +-------+--------------+ | 1 | 473 | | 11 | 142 | | 3 | 123 | | 33 | 121 | | 15 | 116 | | 4 | 70 | | 14 | 48 | | 50 | 41 | | 53 | 30 | | 36 | 26 | | 27 | 20 | | 70 | 18 | | 7 | 14 | | 52 | 11 | | 40 | 10 | | 41 | 9 | | 13 | 9 | | 48 | 8 | | 34 | 7 | | 58 | 7 | | 60 | 5 | | 46 | 4 | | 47 | 4 | | 72 | 4 | | 61 | 4 | | 12 | 4 | | 62 | 4 | | 17 | 3 | | 54 | 3 | | 38 | 3 | | 56 | 2 | | 22 | 2 | | 37 | 2 | | 68 | 2 | | 59 | 2 | | 45 | 2 | | 71 | 1 | | 57 | 1 | | 63 | 1 | | 42 | 1 | | 73 | 1 | | 49 | 1 | | 64 | 1 | | 65 | 1 | | 43 | 1 | | 66 | 1 | | 35 | 1 | | 55 | 1 | | 67 | 1 | | 28 | 1 | | 44 | 1 | | 39 | 1 | | 69 | 1 | +-------+--------------+ 53 rows in set (0.00 sec) mysql> SELECT SUM(id) AS 'id sum', -> AVG(id) AS 'average id' -> FROM enq1; +--------+------------+ | id sum | average id | +--------+------------+ | 939135 | 685.5000 | +--------+------------+ 1 row in set (0.01 sec) mysql> SELECT DISTINCT owner FROM enq1 ORDER BY owner; +-------+ | owner | +-------+ | 1 | | 3 | | 4 | | 7 | | 11 | | 12 | | 13 | | 14 | | 15 | | 17 | | 22 | | 27 | | 28 | | 33 | | 34 | | 35 | | 36 | | 37 | | 38 | | 39 | | 40 | | 41 | | 42 | | 43 | | 44 | | 45 | | 46 | | 47 | | 48 | | 49 | | 50 | | 52 | | 53 | | 54 | | 55 | | 56 | | 57 | | 58 | | 59 | | 60 | | 61 | | 62 | | 63 | | 64 | | 65 | | 66 | | 67 | | 68 | | 69 | | 70 | | 71 | | 72 | | 73 | +-------+ 53 rows in set (0.00 sec) mysql> SELECT COUNT(DISTINCT owner) FROM enq1; +-----------------------+ | COUNT(DISTINCT owner) | +-----------------------+ | 53 | +-----------------------+ 1 row in set (0.00 sec) mysql> SELECT COUNT(DISTINCT DAY(time)) FROM enq1; +---------------------------+ | COUNT(DISTINCT DAY(time)) | +---------------------------+ | 31 | +---------------------------+ 1 row in set (0.02 sec) mysql> SELECT COUNT(DISTINCT YEAR(time)) FROM enq1; +----------------------------+ | COUNT(DISTINCT YEAR(time)) | +----------------------------+ | 1 | +----------------------------+ 1 row in set (0.00 sec) mysql> SELECT COUNT(DISTINCT MONTH(time)) FROM enq1; +-----------------------------+ | COUNT(DISTINCT MONTH(time)) | +-----------------------------+ | 5 | +-----------------------------+ 1 row in set (0.00 sec) 找最大值 mysql> SELECT MAX(owner) FROM enq1; +------------+ | MAX(owner) | +------------+ | 73 | +------------+ 1 row in set (0.53 sec) mysql> SELECT MIN(owner) FROM enq1; +------------+ | MIN(owner) | +------------+ | 1 | +------------+ 1 row in set (0.00 sec) mysql> SELECT @min := MIN(owner) FROM enq1; +--------------------+ | @min := MIN(owner) | +--------------------+ | 1 | +--------------------+ 1 row in set (0.00 sec) mysql> SELECT @min AS 'FORMOSA enqs', time FROM enq1 WHERE owner = @min LIMIT 10 ; +--------------+------------+ | FORMOSA enqs | time | +--------------+------------+ | 1 | 2008-05-15 | | 1 | 2008-05-22 | | 1 | 2008-05-23 | | 1 | 2008-05-23 | | 1 | 2008-05-23 | | 1 | 2008-05-23 | | 1 | 2008-05-23 | | 1 | 2008-05-23 | | 1 | 2008-05-27 | | 1 | 2008-05-29 | +--------------+------------+ 10 rows in set (0.00 sec) mysql> SELECT MIN(time) AS earliest, MAX(time) AS latest FROM enq1; +------------+------------+ | earliest | latest | +------------+------------+ | 2008-05-15 | 2008-09-23 | +------------+------------+ 1 row in set (0.00 sec) mysql> SELECT MIN(time) AS earliest, MAX(time) AS latest FROM enq2; +------------+------------+ | earliest | latest | +------------+------------+ | 0000-00-00 | 2008-09-23 | +------------+------------+ 1 row in set (0.16 sec) mysql> SELECT id AS ID, MIN(time) AS earliest, MAX(time) AS latest FROM enq1; ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GR OUP columns is illegal if there is no GROUP BY clause mysql> SELECT @max := MAX(time) FROM enq2; +-------------------+ | @max := MAX(time) | +-------------------+ | 2008-09-23 | +-------------------+ 1 row in set (0.02 sec) mysql> SELECT @min := MIN(time) FROM enq2; +-------------------+ | @min := MIN(time) | +-------------------+ | 0000-00-00 | +-------------------+ 1 row in set (0.00 sec) mysql> SELECT id, time FROM enq2 WHERE time = @max; +------+------------+ | id | time | +------+------------+ | 1442 | 2008-09-23 | | 1481 | 2008-09-23 | | 1482 | 2008-09-23 | | 1483 | 2008-09-23 | | 1484 | 2008-09-23 | | 1487 | 2008-09-23 | | 1490 | 2008-09-23 | | 1491 | 2008-09-23 | | 1492 | 2008-09-23 | | 1493 | 2008-09-23 | | 1498 | 2008-09-23 | | 1499 | 2008-09-23 | | 1501 | 2008-09-23 | | 1505 | 2008-09-23 | +------+------------+ 14 rows in set (0.00 sec) mysql> SELECT * FROM enq1 LEFT JOIN enq2 ON enq1.type1id = enq2.type1id LIMIT 2\ G *************************** 1. row *************************** id: 1 time: 2008-05-15 ourref: 8051501 owner: 1 ownerno: xyz hullnoid: 34 type1id: 367 partsid: 1 QTY: 1 memo: A test memo LANGUAGEid: 1 makerid: 1 enq2s: 1 seriesid: 1 id: NULL time: NULL enq1id: NULL LANGUAGEid: NULL ORIGINid: NULL PRICEid: NULL PAYMENTid: NULL makerid: NULL DELIVERY: NULL type1id: NULL partsid: NULL QTY: NULL memo: NULL price: NULL discount: NULL discount0: NULL money: NULL price1: NULL makerref: NULL disc: NULL charges: NULL *************************** 2. row *************************** id: 2 time: 2008-05-22 ourref: 8052201 owner: 1 ownerno: hullnoid: 1 type1id: 73 partsid: 1=2=3=4=5=6=7=8=9=10=11=12=13=14=15=16=17=18=19=20=21=22=23=24=25 QTY: 8=40=40=40=40=40=10=20=20=20=20=20=4=2=5=2=4=40=10=40=1=10=10=16=8 memo: LANGUAGEid: 1 makerid: 1 enq2s: 2 seriesid: 1 id: 536 time: 2008-07-14 enq1id: 517 LANGUAGEid: 1 ORIGINid: 1 PRICEid: 1 PAYMENTid: 1 makerid: 144 DELIVERY: type1id: 73 partsid: 26=27=8=11=10=28 QTY: 8=6=10=10=14=10 memo: price: 16000=4000=35=30=23=320 discount: D discount0: 100 money: 1 price1: P1 makerref: disc: charges: 2 rows in set (0.00 sec) mysql> SELECT COUNT(*) AS 'Number of enq from 1' FROM enq1 WHERE owner = 1; +----------------------+ | Number of enq from 1 | +----------------------+ | 473 | +----------------------+ 1 row in set (0.00 sec) mysql> SELECT COUNT(*) AS 'Number of enq from 1' FROM enq1 WHERE owner = 3; +----------------------+ | Number of enq from 1 | +----------------------+ | 123 | +----------------------+ 1 row in set (0.00 sec) mysql> SELECT COUNT(*) AS 'Number of enq from 1' FROM enq1 WHERE owner = 4; +----------------------+ | Number of enq from 1 | +----------------------+ | 70 | +----------------------+ 1 row in set (0.00 sec) mysql> SHOW TABLE STATUS FROM cookbook LIKE 'main_type1'\G *************************** 1. row *************************** Name: main_type1 Engine: InnoDB Version: 10 Row_format: Compact Rows: 1162 Avg_row_length: 112 Data_length: 131072 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: 1297 Create_time: 2007-10-22 18:11:04 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: InnoDB free: 147456 kB 1 row in set (0.14 sec)