创建视图的语句如下：
create or replace view v_materialdemand_1
(p_production_id, ad_client_id, ad_org_id, m_material_id, sourcetype, m_productbom_id, bomproductvalue, bomproductname, uomname, m_productparent_id, qtybom, qtydemand, qtyhanded, isapproved, p_materialdemand_id, qtyreplace, ispackmaterial, created, createdby, updated, updatedby, demandproductvalue, demandproductname, m_requisitionline_id, stdprecision, qtydifference)
as
select b.p_production_id, b.ad_client_id, b.ad_org_id,
b.m_material_id, b.sourcetype, b.m_productbom_id,
mp.value bomproductvalue, mp.name bomproductname, min(ut.name) uomname,
b.m_productparent_id,
sum(b.qtybom) qtybom,
sum(b.qtydemand) qtydemand,
sum(b.qtyhandled) qtyhandled ,
min(b.isApproved) isApproved, max(b.p_materialdemand_id) p_materialdemand_id,
sum(b.qtyreplace ) qtyreplace,
min(b.ispackmaterial),
min(b.created), min(b.createdby), min(b.updated), min(b.updatedby),
min(p2.value), min(p2.name),
min(b.m_requisitionline_id), max(cu.stdprecision), sum(b.qtydifference)
FROM p_materialdemand b
inner join m_product mp on b.m_productbom_id = mp.m_product_id
inner join m_product p2 on b.m_material_id = p2.m_product_id
left join c_uom cu on cu.c_uom_id = p2.c_uom_id
left join c_uom_trl ut on ut.c_uom_id = cu.c_uom_id
where b.isactive = 'Y' --and b.p_production_id = 1004237
group by b.p_production_id, b.m_material_id, b.sourcetype, b.m_productbom_id, b.m_productparent_id,
b.ad_client_id, b.ad_org_id, mp.value, mp.name, cu.name
union
select
min(p_production_id), min(ad_client_id) ad_client_id, min(ad_org_id) ad_org_id,
min(m_material_id) m_material_id, min(sourcetype) sourcetype, min(m_productbom_id) m_productbom_id,
min(bomproductvalue) bomproductvalue, min(bomproductname) bomproductname, min(uomname) uomname,
min(m_productparent_id) m_productparent_id,
sum(qtybom) qtybom,
sum(qtydemand) qtydemand,
sum(qtyhandled) qtyhandled,
min(isapproved) isapproved, max(p_materialdemand_id) p_materialdemand_id,
sum(qtyreplace) qtyreplace,
min(ispackmaterial) ispackmaterial, min(created) created, min(createdby) createdby,
min(updated) updated, min(updatedby) updatedby, min(materialvalue) materialvalue, min(materialname) materialname,
min(m_requisitionline_id) m_requisitionline_id, max(stdprecision), sum(qtydifference) qtydifference
from (
select min(x.p_production_id) p_production_id, min(a.ad_client_id) ad_client_id, min(a.ad_org_id) ad_org_id,
min(a.m_material_id) m_material_id, min(a.sourcetype) sourcetype, min(a.m_productbom_id) m_productbom_id,
min(p3.value) bomproductvalue, min(p3.name) bomproductname, min(ut.name) uomname,
min(a.m_productparent_id) m_productparent_id,
sum(x.qty) / sum(nvl(x.qtydemand,0)) * min(a.qtybom) qtybom,
sum(x.qty) / sum(nvl(x.qtydemand,0)) * min(a.qtydemand) qtydemand,
sum(x.qty) / sum(nvl(x.qtydemand,0)) * min(a.qtyhandled) qtyhandled,
min(a.isapproved) isapproved, min(a.p_materialdemand_id) p_materialdemand_id,
sum(x.qty) / sum(x.qtydemand) * min(a.qtyreplace) qtyreplace,
min(a.ispackmaterial) ispackmaterial, min(a.created) created, min(a.createdby) createdby,
min(a.updated) updated, min(a.updatedby) updatedby, min(p2.value) materialvalue, min(p2.name) materialname,
min(x.m_requisitionline_id) m_requisitionline_id, max(u.stdprecision) stdprecision,
sum(x.qty) / sum(x.qtydemand) * min(a.qtydifference) qtydifference
from p_materialdemand a
inner join
(select dm.p_materialdemand_id,dm.p_production_id,dm.qty,d.p_production_id p_productionparent_id,
d.m_material_id,d.qtydemand,dm.m_requisitionline_id from p_materialdemandmatching dm
inner join p_materialdemand d on (d.p_materialdemand_id = dm.p_materialdemand_id) where dm.isactive = 'Y') x
on (a.p_production_id = x.p_productionparent_id and a.m_productparent_id = x.m_material_id)
inner join m_product p2 on (p2.m_product_id = a.m_material_id)
inner join m_product p3 on (p3.m_product_id = a.m_productbom_id)
left join c_uom u on (u.c_uom_id = p2.c_uom_id)
left join c_uom_trl ut on ut.c_uom_id = u.c_uom_id
where a.isactive = 'Y'
group by a.p_materialdemand_id, x.p_production_id) z --where z.p_production_id = 1004237
group by z.p_production_id, z.m_material_id, z.sourcetype, z.m_productbom_id, z.m_productparent_id如果用创建视图的Select语句（把上面注释的二个where条件放出来）搜出的qtybom和用下面的语句搜出的结果不一样：
select * from v_materialdemand_1 md where md.p_production_id = 1004237
请高手指点。
create or replace view v_materialdemand_1
(p_production_id, ad_client_id, ad_org_id, m_material_id, sourcetype, m_productbom_id, bomproductvalue, bomproductname, uomname, m_productparent_id, qtybom, qtydemand, qtyhanded, isapproved, p_materialdemand_id, qtyreplace, ispackmaterial, created, createdby, updated, updatedby, demandproductvalue, demandproductname, m_requisitionline_id, stdprecision, qtydifference)
as
select b.p_production_id, b.ad_client_id, b.ad_org_id,
b.m_material_id, b.sourcetype, b.m_productbom_id,
mp.value bomproductvalue, mp.name bomproductname, min(ut.name) uomname,
b.m_productparent_id,
sum(b.qtybom) qtybom,
sum(b.qtydemand) qtydemand,
sum(b.qtyhandled) qtyhandled ,
min(b.isApproved) isApproved, max(b.p_materialdemand_id) p_materialdemand_id,
sum(b.qtyreplace ) qtyreplace,
min(b.ispackmaterial),
min(b.created), min(b.createdby), min(b.updated), min(b.updatedby),
min(p2.value), min(p2.name),
min(b.m_requisitionline_id), max(cu.stdprecision), sum(b.qtydifference)
FROM p_materialdemand b
inner join m_product mp on b.m_productbom_id = mp.m_product_id
inner join m_product p2 on b.m_material_id = p2.m_product_id
left join c_uom cu on cu.c_uom_id = p2.c_uom_id
left join c_uom_trl ut on ut.c_uom_id = cu.c_uom_id
where b.isactive = 'Y' --and b.p_production_id = 1004237
group by b.p_production_id, b.m_material_id, b.sourcetype, b.m_productbom_id, b.m_productparent_id,
b.ad_client_id, b.ad_org_id, mp.value, mp.name, cu.name
union
select
min(p_production_id), min(ad_client_id) ad_client_id, min(ad_org_id) ad_org_id,
min(m_material_id) m_material_id, min(sourcetype) sourcetype, min(m_productbom_id) m_productbom_id,
min(bomproductvalue) bomproductvalue, min(bomproductname) bomproductname, min(uomname) uomname,
min(m_productparent_id) m_productparent_id,
sum(qtybom) qtybom,
sum(qtydemand) qtydemand,
sum(qtyhandled) qtyhandled,
min(isapproved) isapproved, max(p_materialdemand_id) p_materialdemand_id,
sum(qtyreplace) qtyreplace,
min(ispackmaterial) ispackmaterial, min(created) created, min(createdby) createdby,
min(updated) updated, min(updatedby) updatedby, min(materialvalue) materialvalue, min(materialname) materialname,
min(m_requisitionline_id) m_requisitionline_id, max(stdprecision), sum(qtydifference) qtydifference
from (
select min(x.p_production_id) p_production_id, min(a.ad_client_id) ad_client_id, min(a.ad_org_id) ad_org_id,
min(a.m_material_id) m_material_id, min(a.sourcetype) sourcetype, min(a.m_productbom_id) m_productbom_id,
min(p3.value) bomproductvalue, min(p3.name) bomproductname, min(ut.name) uomname,
min(a.m_productparent_id) m_productparent_id,
sum(x.qty) / sum(nvl(x.qtydemand,0)) * min(a.qtybom) qtybom,
sum(x.qty) / sum(nvl(x.qtydemand,0)) * min(a.qtydemand) qtydemand,
sum(x.qty) / sum(nvl(x.qtydemand,0)) * min(a.qtyhandled) qtyhandled,
min(a.isapproved) isapproved, min(a.p_materialdemand_id) p_materialdemand_id,
sum(x.qty) / sum(x.qtydemand) * min(a.qtyreplace) qtyreplace,
min(a.ispackmaterial) ispackmaterial, min(a.created) created, min(a.createdby) createdby,
min(a.updated) updated, min(a.updatedby) updatedby, min(p2.value) materialvalue, min(p2.name) materialname,
min(x.m_requisitionline_id) m_requisitionline_id, max(u.stdprecision) stdprecision,
sum(x.qty) / sum(x.qtydemand) * min(a.qtydifference) qtydifference
from p_materialdemand a
inner join
(select dm.p_materialdemand_id,dm.p_production_id,dm.qty,d.p_production_id p_productionparent_id,
d.m_material_id,d.qtydemand,dm.m_requisitionline_id from p_materialdemandmatching dm
inner join p_materialdemand d on (d.p_materialdemand_id = dm.p_materialdemand_id) where dm.isactive = 'Y') x
on (a.p_production_id = x.p_productionparent_id and a.m_productparent_id = x.m_material_id)
inner join m_product p2 on (p2.m_product_id = a.m_material_id)
inner join m_product p3 on (p3.m_product_id = a.m_productbom_id)
left join c_uom u on (u.c_uom_id = p2.c_uom_id)
left join c_uom_trl ut on ut.c_uom_id = u.c_uom_id
where a.isactive = 'Y'
group by a.p_materialdemand_id, x.p_production_id) z --where z.p_production_id = 1004237
group by z.p_production_id, z.m_material_id, z.sourcetype, z.m_productbom_id, z.m_productparent_id如果用创建视图的Select语句（把上面注释的二个where条件放出来）搜出的qtybom和用下面的语句搜出的结果不一样：
select * from v_materialdemand_1 md where md.p_production_id = 1004237
请高手指点。
而select * from v_materialdemand_1 md where md.p_production_id = 1004237 后的条件加在了整个视图的结果集上
我用下面的语句：
select * from (select * from ... where ... group by ...
union select * from ... where ... group by ...)
where p_production_id = 1004237
得出的结果和
select * from v_materialdemand_1 md where md.p_production_id = 1004237得出的结果是一样的，但是和：
select * from ... where p_production_id = 1004237 group by ...
union select * from ... where p_production_id = 1004237 group by ...得出的结果不一样
请问原来的视图哪个地方有问题？正确的结果应该是最后这个SQL：
select * from ... where p_production_id = 1004237 group by ...
union select * from ... where p_production_id = 1004237 group by ...得出的结果
用union all代替union, 结果是一样的。
中间表z会搜出3条记录，用select * from v_materialdemand_1 md where md.p_production_id = 1004237
搜出的结果只会取z表的一条记录，而不会将z表加总，但我明明group by了z表，
group by z.p_production_id, z.m_material_id, z.sourcetype, z.m_productbom_id, z.m_productparent_id 这5个字段在z表的3条记录的值都是相同的，所以应该要加总，
但视图偏偏只取了z表的一条记录，这就是我想不明白的地方。
将与问题无关的子查询放入视图，简化下，方便找出问题
既然union上面的结果集为空，那么上面的先不要
create view view_z as
select min(x.p_production_id) p_production_id, min(a.ad_client_id) ad_client_id, min(a.ad_org_id) ad_org_id,
min(a.m_material_id) m_material_id, min(a.sourcetype) sourcetype, min(a.m_productbom_id) m_productbom_id,
min(p3.value) bomproductvalue, min(p3.name) bomproductname, min(ut.name) uomname,
min(a.m_productparent_id) m_productparent_id,
sum(x.qty) / sum(nvl(x.qtydemand,0)) * min(a.qtybom) qtybom,
sum(x.qty) / sum(nvl(x.qtydemand,0)) * min(a.qtydemand) qtydemand,
sum(x.qty) / sum(nvl(x.qtydemand,0)) * min(a.qtyhandled) qtyhandled,
min(a.isapproved) isapproved, min(a.p_materialdemand_id) p_materialdemand_id,
sum(x.qty) / sum(x.qtydemand) * min(a.qtyreplace) qtyreplace,
min(a.ispackmaterial) ispackmaterial, min(a.created) created, min(a.createdby) createdby,
min(a.updated) updated, min(a.updatedby) updatedby, min(p2.value) materialvalue, min(p2.name) materialname,
min(x.m_requisitionline_id) m_requisitionline_id, max(u.stdprecision) stdprecision,
sum(x.qty) / sum(x.qtydemand) * min(a.qtydifference) qtydifference
from p_materialdemand a
inner join
(select dm.p_materialdemand_id,dm.p_production_id,dm.qty,d.p_production_id p_productionparent_id,
d.m_material_id,d.qtydemand,dm.m_requisitionline_id from p_materialdemandmatching dm
inner join p_materialdemand d on (d.p_materialdemand_id = dm.p_materialdemand_id) where dm.isactive = 'Y') x
on (a.p_production_id = x.p_productionparent_id and a.m_productparent_id = x.m_material_id)
inner join m_product p2 on (p2.m_product_id = a.m_material_id)
inner join m_product p3 on (p3.m_product_id = a.m_productbom_id)
left join c_uom u on (u.c_uom_id = p2.c_uom_id)
left join c_uom_trl ut on ut.c_uom_id = u.c_uom_id
where a.isactive = 'Y'
group by a.p_materialdemand_id, x.p_production_id;
然后结果集很小的话，这样比较一下
select 1 flag,view_z.* from view_z
union all
(select 2,
min(p_production_id), min(ad_client_id) ad_client_id, min(ad_org_id) ad_org_id,
min(m_material_id) m_material_id, min(sourcetype) sourcetype, min(m_productbom_id) m_productbom_id,
min(bomproductvalue) bomproductvalue, min(bomproductname) bomproductname, min(uomname) uomname,
min(m_productparent_id) m_productparent_id,
sum(qtybom) qtybom,
sum(qtydemand) qtydemand,
sum(qtyhandled) qtyhandled,
min(isapproved) isapproved, max(p_materialdemand_id) p_materialdemand_id,
sum(qtyreplace) qtyreplace,
min(ispackmaterial) ispackmaterial, min(created) created, min(createdby) createdby,
min(updated) updated, min(updatedby) updatedby, min(materialvalue) materialvalue, min(materialname) materialname,
min(m_requisitionline_id) m_requisitionline_id, max(stdprecision), sum(qtydifference) qtydifference
from view_z z where z.p_production_id = 1004237
group by z.p_production_id, z.m_material_id, z.sourcetype, z.m_productbom_id, z.m_productparent_id )
union all
(select 3,md.* from(
select
min(p_production_id), min(ad_client_id) ad_client_id, min(ad_org_id) ad_org_id,
min(m_material_id) m_material_id, min(sourcetype) sourcetype, min(m_productbom_id) m_productbom_id,
min(bomproductvalue) bomproductvalue, min(bomproductname) bomproductname, min(uomname) uomname,
min(m_productparent_id) m_productparent_id,
sum(qtybom) qtybom,
sum(qtydemand) qtydemand,
sum(qtyhandled) qtyhandled,
min(isapproved) isapproved, max(p_materialdemand_id) p_materialdemand_id,
sum(qtyreplace) qtyreplace,
min(ispackmaterial) ispackmaterial, min(created) created, min(createdby) createdby,
min(updated) updated, min(updatedby) updatedby, min(materialvalue) materialvalue, min(materialname) materialname,
min(m_requisitionline_id) m_requisitionline_id, max(stdprecision), sum(qtydifference) qtydifference
from view_z z
group by z.p_production_id, z.m_material_id, z.sourcetype, z.m_productbom_id, z.m_productparent_id )md
where md.p_production_id = 1004237)
order by 1,2
楼主，试试，把p_production_id = 1004237
放在 FROM p_materialdemand b
和 from p_materialdemand a
后面？
放在 FROM p_materialdemand b
和 from p_materialdemand a
后面？p_production_id = 1004237这个条件在视图的语句都是没有的，我是为了检查用视图得出的结果为何不对才加进去的，用select ... union select ...得出的结果是对的，但用select (select ... union select ...) where p_production_id = 1004237得出的结果和视图查出的结果是一样的，都是错误的。
[/Quote]
既然union上面的结果集为空，那么上面的先不要
create view view_z as ...
然后结果集很小的话，这样比较一下
SQL codeselect1 flag,view_z.*from view_zunionall where p_production_id = 1004237
(select2,min(p_production_id),min(ad_client_id) ad_client_id,min(ad_org_id) ad_org_id,min(m_material_id) m_material_id,min(sourcetype) sourcetype,min(m_productbom_id) m_productbom_id,min(bomproductvalue) bomproductvalue,min(bomproductname) bomproductname,min(uomname) uomname,min(m_productparent_id) m_productparent_id,sum(qtybom) qtybom,sum(qtydemand) qtydemand,sum(qtyhandled) qtyhandled,min(isapproved) isapproved,max(p_materialdemand_id) p_materialdemand_id,sum(qtyreplace) qtyreplace,min(ispackmaterial) ispackmaterial,min(created) created,min(createdby) createdby,min(updated) updated,min(updatedby) updatedby,min(materialvalue) materialvalue,min(materialname) materialname,min(m_requisitionline_id) m_requisitionline_id,max(stdprecision),sum(qtydifference) qtydifferencefrom view_z zwhere z.p_production_id=1004237groupby z.p_production_id, z.m_material_id, z.sourcetype, z.m_productbom_id, z.m_productparent_id )unionall
(select3,md.*from(selectmin(p_production_id) p_production_id,min(ad_client_id) ad_client_id,min(ad_org_id) ad_org_id,min(m_material_id) m_material_id,min(sourcetype) sourcetype,min(m_productbom_id) m_productbom_id,min(bomproductvalue) bomproductvalue,min(bomproductname) bomproductname,min(uomname) uomname,min(m_productparent_id) m_productparent_id,sum(qtybom) qtybom,sum(qtydemand) qtydemand,sum(qtyhandled) qtyhandled,min(isapproved) isapproved,max(p_materialdemand_id) p_materialdemand_id,sum(qtyreplace) qtyreplace,min(ispackmaterial) ispackmaterial,min(created) created,min(createdby) createdby,min(updated) updated,min(updatedby) updatedby,min(materialvalue) materialvalue,min(materialname) materialname,min(m_requisitionline_id) m_requisitionline_id,max(stdprecision),sum(qtydifference) qtydifferencefrom view_z zgroupby z.p_production_id, z.m_material_id, z.sourcetype, z.m_productbom_id, z.m_productparent_id ) md where md.p_production_id=1004237) order by 1,2
[/Quote]这样搜出的结果：
标记为1的有3条记录，
标记为2的记录是3条记录的总和，是对的，
标记为3的记录只取了一条记录，是错的。
用你的比较语句，搜出的结果：
标记为1的有3条记录，
标记为2的记录是3条记录的总和，是对的，
标记为3的记录只取了一条记录，是错的。
请问是什么原？该如何修正视图的SQL？
union all
select 2,p_production_id,m_material_id,sourcetype,m_productbom_id,m_productparent_id,sum(qtybom) qtybom from view_z z where z.p_production_id = 1004237
group by z.p_production_id, z.m_material_id, z.sourcetype, z.m_productbom_id, z.m_productparent_id
union all
select 3,md.* from (select p_production_id,m_material_id,sourcetype,m_productbom_id,m_productparent_id,sum(qtybom) qtybom from view_z z
group by z.p_production_id, z.m_material_id, z.sourcetype, z.m_productbom_id, z.m_productparent_id ) md
where md.p_production_id = 1004237
order by 1
用上面的语句，标记为2，3的都是1的合计，都是对的。
很奇怪，用14楼的搜出来标记为3的为何不对，我只是去掉了一些字段。
select 3,md.* from (select min(p_production_id) p_production_id, min(ad_client_id) ad_client_id, min(ad_org_id) ad_org_id,
min(m_material_id) m_material_id, min(sourcetype) sourcetype, min(m_productbom_id) m_productbom_id,
min(bomproductvalue) bomproductvalue, min(bomproductname) bomproductname, min(uomname) uomname,
min(m_productparent_id) m_productparent_id,
sum(qtybom) qtybom,
sum(qtydemand) qtydemand,
sum(qtyhandled) qtyhandled,
min(isapproved) isapproved,
max(p_materialdemand_id) p_materialdemand_id,
sum(qtyreplace) qtyreplace,
min(ispackmaterial) ispackmaterial, min(created) created, min(createdby) createdby,
min(updated) updated, min(updatedby) updatedby, min(materialvalue) materialvalue, min(materialname) materialname,
min(m_requisitionline_id) m_requisitionline_id, max(stdprecision), sum(qtydifference) qtydifference
from view_z z
group by z.p_production_id, z.m_material_id, z.sourcetype, z.m_productbom_id, z.m_productparent_id ) md
where md.p_production_id = 1004237
上面搜出来的结果，qtybom没有加总，如果注释qtybom后面的select字段，qtybom就会加总。
请哪位高手说是什么原因？
这个问题是很奇怪，检查下字段对应，字段名没有问题？因为代码太长，可能有些地方会疏忽了
where md.p_production_id = 1004237 如果这个p_production_id就是z里面的那个p_production_id，那么min(p_production_id)外面的min()可以去掉，注意group by外面子句里是否加上了p_production_id（这个是关键）
如果还没有找到问题所在，建个临时表而不是试图来存储Z生成的数据，再试试。
逐步缩小范围找出问题所在
至于是否显示qtybom后面的字段没有什么影响，group by后面选择的字段才决定得到的结果
视图内外进行2次group by本身没什么问题，除非是BUG
终于搞定了，把group by的几个字段，在select中不加min()函数，就OK了，看来如果后面group by了，前面select这些字段时，如果加min()函数取值可能就会出问题。