[code="sql"]
with temp as (
select 'computer' as goods_type ,'dell' as goods_name,6 as counts from dual
union
select 'computer' as goods_type ,'dell' as goods_name,5 as counts from dual
union
select 'computer' as goods_type ,'dell' as goods_name,5 as counts from dual
union
select 'computer' as goods_type ,'ibm' as goods_name,3 as counts from dual
union
select 'computer' as goods_type ,'ibm' as goods_name,10 as counts from dual
union
select 'computer' as goods_type ,'hp' as goods_name,10 as counts from dual
union
select 'camera' as goods_type ,'zineng' as goods_name,5 as counts from dual
union
select 'camera' as goods_type ,'zineng' as goods_name,10 as counts from dual
union
select 'camera' as goods_type ,'zineng' as goods_name,5 as counts from dual
union
select 'camera' as goods_type ,'sony' as goods_name,7 as counts from dual
)
select goods_type,goods_name,sum(counts) from temp group by goods_type,goods_name
[/code]
-
- ll89308839 select typename,goodsname,sum(goodsnum) as counts from goodstype t1,buyinfo t2 where t1.id=t2.goodtypeid group by typename,goodsname
- 7 年多之前 回复
-
- oyzj642 SQL> desc goodstype; Name Type Nullable Default Comments -------- ------------- -------- ------- -------- ID NUMBER TYPENAME NVARCHAR2(50) SQL> desc buyinfo; Name Type Nullable Default Comments ---------- --------------- -------- ------- -------- ID NUMBER GOODTYPEID NUMBER GOODSNAME NVARCHAR2(50) Y GOODSNUM NUMBER Y BUYDATE DATE Y BUYMAN VARCHAR2(50) Y SUPPLIER VARCHAR2(100) Y PRICE NUMBER(8,2) Y TOTLEPRICE NUMBER(8,2) Y PARAM NVARCHAR2(1000) Y
- 7 年多之前 回复
-
- ll89308839 你要把表结构发过来，不要dmp的啊，这个还要建表空间
- 7 年多之前 回复
-
- ll89308839 主要是下面这句啊，上面我懒得创建表，就弄了个数据视图罢了 select goods_type,goods_name,sum(counts) from temp group by goods_type,goods_name
- 7 年多之前 回复
-
- oyzj642 SQL> desc goodstype; Name Type Nullable Default Comments -------- ------------- -------- ------- -------- ID NUMBER TYPENAME NVARCHAR2(50) SQL> desc buyinfo; Name Type Nullable Default Comments ---------- --------------- -------- ------- -------- ID NUMBER GOODTYPEID NUMBER GOODSNAME NVARCHAR2(50) Y GOODSNUM NUMBER Y BUYDATE DATE Y BUYMAN VARCHAR2(50) Y SUPPLIER VARCHAR2(100) Y PRICE NUMBER(8,2) Y TOTLEPRICE NUMBER(8,2) Y PARAM NVARCHAR2(1000) Y
- 7 年多之前 回复
