select product_line,sum(defect_qty),sum(inspect_qty)
from fr_badness
group by product_line现在就是 sum(defect_qty),sum(inspect_qty)要过滤掉重复的条码,也就是重复的条码只算一次,相同条码的defect_qty和inspect_qty 一定是相同的,不同的分组一定没有相同的条码,如:product_line barcode defect_qty inspect_qty position create_date
a 1101 2 10 t1 2011-10-11 21:05:09
a 1101 2 10 t2 2011-10-11 21:06:09
a 1102 9 20 t3 2011-10-11 21:07:09
b 1103 3 12 t4 2011-10-11 21:08:09
b 1103 3 12 t5 2011-10-11 21:07:09
a 1101 2 10 t1 2011-10-11 21:09:09
就是a的barcode为1101统计时只计算一次,b也是一样
统计结果应为:
product_line sum(defect_qty) sum(inspect_qty)
a 11 30
b 3 12
from fr_badness
group by product_line现在就是 sum(defect_qty),sum(inspect_qty)要过滤掉重复的条码,也就是重复的条码只算一次,相同条码的defect_qty和inspect_qty 一定是相同的,不同的分组一定没有相同的条码,如:product_line barcode defect_qty inspect_qty position create_date
a 1101 2 10 t1 2011-10-11 21:05:09
a 1101 2 10 t2 2011-10-11 21:06:09
a 1102 9 20 t3 2011-10-11 21:07:09
b 1103 3 12 t4 2011-10-11 21:08:09
b 1103 3 12 t5 2011-10-11 21:07:09
a 1101 2 10 t1 2011-10-11 21:09:09
就是a的barcode为1101统计时只计算一次,b也是一样
统计结果应为:
product_line sum(defect_qty) sum(inspect_qty)
a 11 30
b 3 12
解决方案 »
- 请教:oracle 创建主键自增触发器时 总是弹出2,3,4 怎么回事?
- 记录profile的数据表是哪个呢?
- 【求高手扫盲】oracle中的role和system privilege
- linux下安装ORACLE字符集设置的问题
- Oracle Spatial中金字塔重采样方法问题
- insert时,对于blob, 一定要使用empty_blob吗?
- 把oracle8.0.5一个用户的所有数据存到另一个用户下怎么做?
- 在查询的时候要关联好几张表,不只两张,是用from a,b,c where ....,还是用left join
- oracle9i如何创建数据库?
- 如何遍历临时表(表名不确定)
- 用主键读数据,花了25ms,算慢吗
- 麻烦谁有metalink账户,帮忙查个bug吧,谢谢
from
(
select distinct
w.product_line ,
w.barcode ,
w.defect_qty ,
w.inspect_qty
from fr_badness w
) t
group by product_line;
from
(with a as
(select 'a' as product_line , '1101' as barcode, '2' as defect_qty,
'10' as inspect_qty, 't1' as position, sysdate as create_date from dual
union all
select 'a' as product_line , '1101' as barcode, '2' as defect_qty,
'10' as inspect_qty, 't2' as position, sysdate as create_date from dual
union all
select 'a' as product_line , '1102' as barcode, '9' as defect_qty,
'20' as inspect_qty, 't3' as position, sysdate as create_date from dual
union all
select 'b' as product_line , '1103' as barcode, '3' as defect_qty,
'12' as inspect_qty, 't4' as position, sysdate as create_date from dual
union all
select 'b' as product_line , '1103' as barcode, '3' as defect_qty,
'12' as inspect_qty, 't5' as position, sysdate as create_date from dual
union all
select 'a' as product_line , '1101' as barcode, '2' as defect_qty,
'10' as inspect_qty, 't1' as position, sysdate as create_date from dual
)
select distinct product_line, barcode, defect_qty ,inspect_qty
from a) b
group by b.product_line
order by b.product_line
from (select product_line,defect_qty,inspect_qty from fr_badness group by product_line,defect_qty,inspect_qty)
group by product_line
----------------------------------------------------------------
-- Author :qiudf(day day up)
-- Date :2011-10-12 9:10
-- Verstion:
----------------------------------------------------------------
--> 测试数据:[a]
with a as
(select 'a' as product_line , '1101' as barcode, '2' as defect_qty,
'10' as inspect_qty, 't1' as position, sysdate as create_date from dual
union all
select 'a' as product_line , '1101' as barcode, '2' as defect_qty,
'10' as inspect_qty, 't2' as position, sysdate as create_date from dual
union all
select 'a' as product_line , '1102' as barcode, '9' as defect_qty,
'20' as inspect_qty, 't3' as position, sysdate as create_date from dual
union all
select 'b' as product_line , '1103' as barcode, '3' as defect_qty,
'12' as inspect_qty, 't4' as position, sysdate as create_date from dual
union all
select 'b' as product_line , '1103' as barcode, '3' as defect_qty,
'12' as inspect_qty, 't5' as position, sysdate as create_date from dual
union all
select 'a' as product_line , '1101' as barcode, '2' as defect_qty,
'10' as inspect_qty, 't1' as position, sysdate as create_date from dual
)
select product_line,sum(defect_qty),sum(inspect_qty)
from
(
select product_line,barcode,defect_qty,inspect_qty,row_number() over(partition by product_line,barcode,defect_qty,inspect_qty order by product_line) rn from a
)
where rn=1
group by product_line--result:
a 11 30
b 3 12
from 你的表
select product_line,sum(defect_qty),sum(inspect_qty)
from
(
select product_line,barcode,defect_qty,inspect_qty,row_number() over(partition by product_line,barcode,defect_qty,inspect_qty order by product_line) rn from a --改成你用的表名,其他都不用改了,如果列名一样的问
)
where rn=1
group by product_line
from
(
select product_line,barcode,defect_qty,inspect_qty,row_number() over(partition by product_line,barcode,defect_qty,inspect_qty order by product_line) rn from a --改成你用的表名,其他都不用改了,如果列名一样的问
)
where rn=1
group by product_line