来自《SQL学习指南 第二版》笔记
有表transaction如下:
mysql> select * from transaction;
+--------+---------------------+------------+-------------+--------+---------------+---------------------+---------------------+
| txn_id | txn_date | account_id | txn_type_cd | amount | teller_emp_id | execution_branch_id | funds_avail_date |
+--------+---------------------+------------+-------------+--------+---------------+---------------------+---------------------+
| 1 | 2008-01-05 00:00:00 | 3 | DBT | 100.00 | NULL | NULL | 2008-01-05 00:00:00 |
| 2 | 2008-01-05 00:00:00 | 15 | DBT | 100.00 | NULL | NULL | 2008-01-05 00:00:00 |
| 3 | 2008-01-05 00:00:00 | 17 | DBT | 100.00 | NULL | NULL | 2008-01-05 00:00:00 |
| 4 | 2008-01-05 00:00:00 | 23 | DBT | 100.00 | NULL | NULL | 2008-01-05 00:00:00 |
| 5 | 2008-01-05 00:00:00 | 1 | DBT | 100.00 | NULL | NULL | 2008-01-05 00:00:00 |
| 6 | 2008-01-05 00:00:00 | 4 | DBT | 100.00 | NULL | NULL | 2008-01-05 00:00:00 |
| 7 | 2008-01-05 00:00:00 | 7 | DBT | 100.00 | NULL | NULL | 2008-01-05 00:00:00 |
| 8 | 2008-01-05 00:00:00 | 10 | DBT | 100.00 | NULL | NULL | 2008-01-05 00:00:00 |
| 9 | 2008-01-05 00:00:00 | 13 | DBT | 100.00 | NULL | NULL | 2008-01-05 00:00:00 |
| 10 | 2008-01-05 00:00:00 | 14 | DBT | 100.00 | NULL | NULL | 2008-01-05 00:00:00 |
| 11 | 2008-01-05 00:00:00 | 18 | DBT | 100.00 | NULL | NULL | 2008-01-05 00:00:00 |
| 12 | 2008-01-05 00:00:00 | 21 | DBT | 100.00 | NULL | NULL | 2008-01-05 00:00:00 |
| 13 | 2008-01-05 00:00:00 | 24 | DBT | 100.00 | NULL | NULL | 2008-01-05 00:00:00 |
| 14 | 2008-01-05 00:00:00 | 28 | DBT | 100.00 | NULL | NULL | 2008-01-05 00:00:00 |
| 15 | 2008-01-05 00:00:00 | 8 | DBT | 100.00 | NULL | NULL | 2008-01-05 00:00:00 |
| 16 | 2008-01-05 00:00:00 | 12 | DBT | 100.00 | NULL | NULL | 2008-01-05 00:00:00 |
| 17 | 2008-01-05 00:00:00 | 22 | DBT | 100.00 | NULL | NULL | 2008-01-05 00:00:00 |
| 18 | 2008-01-05 00:00:00 | 2 | DBT | 100.00 | NULL | NULL | 2008-01-05 00:00:00 |
| 19 | 2008-01-05 00:00:00 | 5 | DBT | 100.00 | NULL | NULL | 2008-01-05 00:00:00 |
| 20 | 2008-01-05 00:00:00 | 11 | DBT | 200| NULL | NULL | 2008-01-05 00:00:00 |
| 21 | 2008-01-05 00:00:00 | 19 | DBT | 100.00 | NULL | NULL | 2008-01-05 00:00:00 |
+--------+---------------------+------------+-------------+--------+---------------+---------------------+---------------------+
21 rows in set (0.00 sec)
下面我们统计2008年每一日的开户数量,其中生成的日期表放入了子查询中(因篇幅原因,只显示了2008年3月1日之前的)
mysql> select days.dt,count(t.txn_id) from transaction as t right outer join
-> (select date_add('2008-01-02',interval (ones.num+tens.num+hundreds.num) day) as dt
-> from
-> (select 0 num union all
-> select 1 num union all
-> select 2 num union all
-> select 3 num union all
-> select 4 num union all
-> select 5 num union all
-> select 6 num union all
-> select 7 num union all
-> select 8 num union all
-> select 9 num ) as ones
-> cross join
-> (select 0 num union all
-> select 10 num union all
-> select 20 num union all
-> select 30 num union all
-> select 40 num union all
-> select 50 num union all
-> select 60 num union all
-> select 70 num union all
-> select 80 num union all
-> select 90 num ) tens
-> cross join
-> (select 0 num union all
-> select 100 num union all
-> select 200 num union all
-> select 300 num )hundreds
-> where date_add('2008-01-02',interval (ones.num+tens.num+hundreds.num) day)<'2008-03-01') as days
-> on days.dt=t.txn_date
-> group by days.dt
-> order by 1;
+------------+-----------------+
| dt | count(t.txn_id) |
+------------+-----------------+
| 2008-01-02 | 0 |
| 2008-01-03 | 0 |
| 2008-01-04 | 0 |
| 2008-01-05 | 21 |
| 2008-01-06 | 0 |
| 2008-01-07 | 0 |
| 2008-01-08 | 0 |
| 2008-01-09 | 0 |
| 2008-01-10 | 0 |
| 2008-01-11 | 0 |
| 2008-01-12 | 0 |
| 2008-01-13 | 0 |
| 2008-01-14 | 0 |
| 2008-01-15 | 0 |
| 2008-01-16 | 0 |
| 2008-01-17 | 0 |
| 2008-01-18 | 0 |
| 2008-01-19 | 0 |
| 2008-01-20 | 0 |
| 2008-01-21 | 0 |
| 2008-01-22 | 0 |
| 2008-01-23 | 0 |
| 2008-01-24 | 0 |
| 2008-01-25 | 0 |
| 2008-01-26 | 0 |
| 2008-01-27 | 0 |
| 2008-01-28 | 0 |
| 2008-01-29 | 0 |
| 2008-01-30 | 0 |
| 2008-01-31 | 0 |
| 2008-02-01 | 0 |
| 2008-02-02 | 0 |
| 2008-02-03 | 0 |
| 2008-02-04 | 0 |
| 2008-02-05 | 0 |
| 2008-02-06 | 0 |
| 2008-02-07 | 0 |
| 2008-02-08 | 0 |
| 2008-02-09 | 0 |
| 2008-02-10 | 0 |
| 2008-02-11 | 0 |
| 2008-02-12 | 0 |
| 2008-02-13 | 0 |
| 2008-02-14 | 0 |
| 2008-02-15 | 0 |
| 2008-02-16 | 0 |
| 2008-02-17 | 0 |
| 2008-02-18 | 0 |
| 2008-02-19 | 0 |
| 2008-02-20 | 0 |
| 2008-02-21 | 0 |
| 2008-02-22 | 0 |
| 2008-02-23 | 0 |
| 2008-02-24 | 0 |
| 2008-02-25 | 0 |
| 2008-02-26 | 0 |
| 2008-02-27 | 0 |
| 2008-02-28 | 0 |
| 2008-02-29 | 0 |
+------------+-----------------+
59 rows in set (0.00 sec)