2008最后一问:求sql语句，请大家帮忙解决！

Question

最近在做课程设计，遇到了一个问题：
首先说意义那张数据库表：
create table shopper_inf
(goods_id varchar(10),
shopper_id varchar(14),
shopper_num int,
shopper_total float,
shopper_date date,
foreign key(goods_id) references goods_inf(goods_id)
);
goods_id 商品编号
shopper_id 账单号
shopper_num 购物数量
shopper_total 购物金额
shopper_date 结账日期,格式为：yyyy-MM-dd
现在我就是想查询某一个月、最近几天、某一年的账单信息，但是由于本人能力有限，不知道这三个查询语句应该怎么写，请各位大侠帮忙指导一下。谢谢！

Answer 1

-- 给你个例子:-- 某一个月: 200812
 SELECT *
   FROM SHOPPER_INF
  WHERE TO_CHAR(SHOPPER_DATE,'YYYYMM') = '200812';
 
-- 最近几天: 3天
 SELECT *
   FROM SHOPPER_INF
  WHERE SHOPPER_DATE >= TRUNC(SYSDATE-3,'DD');
  
-- 某一年: 2008年
 SELECT *
   FROM SHOPPER_INF
  WHERE TO_CHAR(SHOPPER_DATE,'YYYY') = '2008';

Answer 2

--1:
select goods_id,sum(shopper_total) from shopper_inf where to_char(shopper_date,'yyyymm')='200808' group by goods_id;--2:
select * from shopper_inf where to_char(shopper_date,'yyyymm')>=20081225 and to_char(shopper_date,'yyyymm')<20090101; --3:
select goods_id,sum(shopper_total) from shopper_inf where to_char(shopper_date,'yyyy')='2008' group by goods_id;

Answer 3

SELECT GOODS_ID,shopper_id,SUM(shopper_num ),SUM(shopper_total) FROM T 
WHERE TO_CHAR(shopper_date,'YYYY-MM')='2008-12'
GROUP BY GOODS_ID,shopper_id;SELECT GOODS_ID,shopper_id,SUM(shopper_num ),SUM(shopper_total) FROM T
WHERE  TO_CHAR(shopper_date,'YYYY-MM-DD') BETWEEN '2008-12-01' AND '2008-12-05'
GROUP BY GOODS_ID,shopper_id,TO_CHAR(shopper_date,'YYYY-MM-DD');SELECT GOODS_ID,shopper_id,SUM(shopper_num ),SUM(shopper_total) FROM T 
WHERE TO_CHAR(shopper_date,'YYYY')='2008'
GROUP BY GOODS_ID,shopper_id;

Answer 4

--某一个月: 200812(2008-12)
SELECT * FROM shopper_inf WHERE TO_CHAR(shopper_date , 'YYYYMM') = '200812';
SELECT * FROM shopper_inf WHERE TO_CHAR(shopper_date , 'YYYY-MM') = '2008-12'; 
--最近几天: 3天
SELECT * FROM SHOPPER_INF WHERE shopper_date >= TRUNC(SYSDATE - 3 , 'DD');
SELECT * FROM shopper_inf WHERE shopper_date >= SYSDATE - 3 ;
  
--某一年: 2008年
SELECT * FROM shopper_inf WHERE TO_CHAR(shopper_date , 'YYYY') = '2008';