表a,有字段:顾客id(id)商品名称(xpmc),价格(price),用一条语句写出每个顾客每个商品金额汇总;
表b,有字段:入库时间(time),商品id(id),数量(sl),用一条语句统计出2010年12个月的每个商品汇总数量;
这2个语句怎么写。请教各位
表b,有字段:入库时间(time),商品id(id),数量(sl),用一条语句统计出2010年12个月的每个商品汇总数量;
这2个语句怎么写。请教各位
1、
select id,xpmc,sum(price)
from a
group by id,xpmc2、
select to_char(time,'yyyy-mm'),id,sum(sl)
from b
where to_char(time,'yyyy-mm')='2010-12'
group by to_char(time,'yyyy-mm'),id
2010年12个月是表示2010年12个月相加吧!如果是,语句如下:
2: select id,sum(sl) as 商品数量汇总 from 表b group by id having to_char(time,'yyyy')='2010'
如果表示的第12个月,语句如下:
2: select id,sum(sl) as 商品数量汇总 from 表b group by id having to_char(time,'yyyy-mm')='2010-12'
create table a(
customer_id int,
goods_name varchar2(50),
goods_price number(5,2),
goods_num number
);
insert into a(customer_id,goods_name,goods_price,goods_num)
values(5568,'佳洁士-盐白',5.4,10);
insert into a(customer_id,goods_name,goods_price,goods_num)
values(8814,'舒肤佳',5.0,5);
insert into a(customer_id,goods_name,goods_price,goods_num)
values(7788,'妮维雅-男生',50.5,1);
insert into a(customer_id,goods_name,goods_price,goods_num)
values(5568,'金龙鱼-花生油',88,12);
insert into a(customer_id,goods_name,goods_price,goods_num)
values(7788,'startking手表',350,1);--用一条语句写出每个顾客每个商品金额汇总SQL> select '顾客:'||customer_id||'购买了'||goods_num||'件'||goods_name||',总金额为:'||sum(goods_price*goods_num)
2 from a
3 group by customer_id,goods_name,goods_num;
/*
'顾客:'||CUSTOMER_ID||'购买了'
--------------------------------------------------------------------------------
顾客:5568购买了10件佳洁士-盐白,总金额为:54
顾客:5568购买了12件金龙鱼-花生油,总金额为:1056
顾客:7788购买了1件妮维雅-男生,总金额为:50.5
顾客:7788购买了1件startking手表,总金额为:350
顾客:8814购买了5件舒肤佳,总金额为:25
*/create table b(
store_time date,
goods_id int,
goods_num number
);insert into b(store_time,goods_id,goods_num)
values(date'2010-05-01',1002,500);
insert into b(store_time,goods_id,goods_num)
values(date'2010-06-23',2105,5000);
insert into b(store_time,goods_id,goods_num)
values(date'2010-08-25',1105,1000);
insert into b(store_time,goods_id,goods_num)
values(date'2010-10-11',2105,50);
insert into b(store_time,goods_id,goods_num)
values(date'2010-10-20',1569,1230);
insert into b(store_time,goods_id,goods_num)
values(date'2010-11-15',1002,1000);
insert into b(store_time,goods_id,goods_num)
values(date'2010-12-19',1452,5000);
insert into b(store_time,goods_id,goods_num)
values(date'2010-12-25',1105,300);--用一条语句统计出2010年12个月的每个商品汇总数量;
SQL> select goods_id,sum(goods_num)
2 from b
3 group by goods_id;
/*
GOODS_ID SUM(GOODS_NUM)
--------------------------------------- --------------
1002 1500
1105 1300
1452 5000
1569 1230
2105 5050
*/
create table a
(
id varchar(20),
xpmc varchar(20),
price integer
)
insert into a values('王安石','宣纸',150);
insert into a values('苏试','宣纸',250);
insert into a values('王安石','毛笔',150);
insert into a values('王安石','矛台',900);
select id ,sum(price) from a group by id /*问題2 表b,有字段:入库时间(time),商品id(id),数量(sl),用一条语句统计出2010年12个月的每个商品汇总数量;*/
create table b
(
time varchar(10),
id varchar(20),
sl int
)
insert into b values('2010-11-12','毛笔',150);
insert into b values('2010-12-10','毛笔',250);
insert into b values('2010-12-11','宣纸',350);
insert into b values('2010-12-12','美酒',450);
insert into b values('2010-12-12','房产',550);
insert into b values('2010-12-12','丫环',650);select id,max(sl),time from b where substr(time,1,7)='2010-12' group by id,time order by id,time;
第二题的数据不能明确
应该如下:
/*问题1 表a,有字段:顾客id(id)商品名称(xpmc),价格(price),用一条语句写出每个顾客每个商品金额汇总;*/
create table a
(
id varchar(20),
xpmc varchar(20),
price integer
)
insert into a values('王安石','宣纸',150);
insert into a values('苏试','宣纸',250);
insert into a values('王安石','毛笔',150);
insert into a values('王安石','矛台',900);
select id ,sum(price) from a group by id /*问題2 表b,有字段:入库时间(time),商品id(id),数量(sl),用一条语句统计出2010年12个月的每个商品汇总数量;*/
create table b
(
time varchar(10),
id varchar(20),
sl int
)
insert into b values('2010-11-12','毛笔',150);
insert into b values('2010-12-10','毛笔',250);
insert into b values('2010-12-11','宣纸',350);
insert into b values('2010-12-12','美酒',450);
insert into b values('2010-12-12','房产',550);
insert into b values('2010-12-12','丫环',650);
insert into b values('2010-12-13','丫环',750);select max(id),sum(sl),time from b where substr(time,1,7)='2010-12' group by time order by time;