一道数据库面试题（变态）

Question

问题是：
一个库存单：
goods   date         amounts
a      2009-1-3      100
a      2009-1-13      660
a      2009-3-3      100
a      2009-3-3     400
a      2009-4-3      300
a      2009-6-3      100
a      2009-8-3      100从 2009年1月 到 12月 都得显示每月的库存总量，没有记录的显示为零
如何写？
我用的是MYSQL写的，希望知道的高手给个答案

Answer 1

mysql> select * from t_bllizard;
+-------+------------+---------+
| goods | date       | amounts |
+-------+------------+---------+
| a     | 2009-01-03 |     100 |
| a     | 2009-01-13 |     660 |
| a     | 2009-03-03 |     100 |
| a     | 2009-03-03 |     400 |
| a     | 2009-04-03 |     300 |
| a     | 2009-06-03 |     100 |
| a     | 2009-08-03 |     100 |
+-------+------------+---------+
7 rows in set (0.00 sec)mysql> select date_format(`date`,'%Y-%m'),sum(amounts)
    -> from (
    -> select `date`,amounts from t_bllizard
    -> union all
    -> select '2009-01-01',0
    -> union all
    -> select '2009-02-01',0
    -> union all
    -> select '2009-03-01',0
    -> union all
    -> select '2009-04-01',0
    -> union all
    -> select '2009-05-01',0
    -> union all
    -> select '2009-06-01',0
    -> union all
    -> select '2009-07-01',0
    -> union all
    -> select '2009-08-01',0
    -> union all
    -> select '2009-09-01',0
    -> union all
    -> select '2009-10-01',0
    -> union all
    -> select '2009-11-01',0
    -> union all
    -> select '2009-12-01',0
    -> ) t
    -> group by date_format(`date`,'%Y-%m');
+-----------------------------+--------------+
| date_format(`date`,'%Y-%m') | sum(amounts) |
+-----------------------------+--------------+
| 2009-01                     |          760 |
| 2009-02                     |            0 |
| 2009-03                     |          500 |
| 2009-04                     |          300 |
| 2009-05                     |            0 |
| 2009-06                     |          100 |
| 2009-07                     |            0 |
| 2009-08                     |          100 |
| 2009-09                     |            0 |
| 2009-10                     |            0 |
| 2009-11                     |            0 |
| 2009-12                     |            0 |
+-----------------------------+--------------+
12 rows in set (0.00 sec)mysql>

Answer 2

1、可以用UNION　ALL将所有年月写入；
2、可以用临时表存入所有年月，再与工作表连接。
select date_format(`date`,'%Y-%m') as ndate,sum(amounts) as na
     from (
     select `date`,amounts from tt
     union all
     select '2009-01-01',0
     union all
     select '2009-02-01',0
     union all
     select '2009-03-01',0
     union all
     select '2009-04-01',0
     union all
     select '2009-05-01',0
     union all
     select '2009-06-01',0
     union all
     select '2009-07-01',0
     union all
     select '2009-08-01',0
     union all
     select '2009-09-01',0
     union all
     select '2009-10-01',0
     union all
     select '2009-11-01',0
     union all
     select '2009-12-01',0
     ) a1
     group by date_format(`date`,'%Y-%m');

Answer 3

假设LSB，字段DATE
内容：
DATE：2009－01－01
      .....
　　　2009－12－01
SELECT date_format(`date`,'%Y-%m'),SUM(MA) FROM (
SELECT A.*,B.goods,IFNULL(B.amounts,0) AS MA  FROM LSB A LEFT JOIN TT B
ON A.`DATE`=B.`DATE`) B
GROUP BY date_format(`date`,'%Y-%m');

Answer 4

如果GOODS类别多:lsb:
dd字段，日期型
2009－01－01
      
2009－12－01 SELECT a.dd,b.goods,sum(if(date_format(a.dd,'%Y-%m')=date_format(b.`date`,'%Y-%m'),b.amounts,0)) as na from lsb a,ttl b
group by a.dd,b.goods order by goods如果只有一种：
SELECT date_format(`date`,'%Y-%m'),SUM(MA) FROM (
SELECT A.*,B.goods,IFNULL(B.amounts,0) AS MA  FROM LSB A LEFT JOIN TT B
ON date_format(a.dd,'%Y-%m')=date_format(b.`date`,'%Y-%m')) B
GROUP BY date_format(`date`,'%Y-%m');