oracle查询问题

有没有一种方法统计出每种类型中各种商品名称的总数量,相当于总统计:得到这样的表结构
下面附带有oracle表

商品类型    总数量      商品名称    数量
电脑          56         戴尔       16
                             IBM        30
                              HP         10

相机          12         自能       5
                             索尼       7

2个回答

[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]

oyzj642
oyzj642 嗯,谢谢!
7 年多之前 回复
ll89308839
ll89308839 select typename,goodsname,sum(goodsnum) as counts from goodstype t1,buyinfo t2 where t1.id=t2.goodtypeid group by typename,goodsname
7 年多之前 回复
oyzj642
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
ll89308839 你要把表结构发过来,不要dmp的啊,这个还要建表空间
7 年多之前 回复
ll89308839
ll89308839 主要是下面这句啊,上面我懒得创建表,就弄了个数据视图罢了 select goods_type,goods_name,sum(counts) from temp group by goods_type,goods_name
7 年多之前 回复
oyzj642
oyzj642 我数据库表里有N种名称的数据,这样写好麻烦,有没有更好的办法?况且还要转换为Java代码
7 年多之前 回复

贴个表结构的截图呗,电脑上么有oracle

oyzj642
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 年多之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问