SELECT (A.NO || '++'||A.Name) as 项目名, C.NO,D.NO,E.NO FROM PROJ A ,PROJ_OBJOF B,PRODUCT C,EPART D,EPART E,EBOM F,PRODUCT_OF_PART G WHERE ( A.DEL=0 AND A.WKAID<>'3' AND B.DEL=0 AND B.WKAID<>'3' AND C.DEL=0 AND C.WKAID<>'3' AND D.DEL=0 AND D.WKAID<>'3' AND E.DEL=0 AND E.WKAID<>'3' AND F.DEL=0 AND F.WKAID<>'3' AND G.DEL=0 AND G.WKAID<>'3' ) AND ( B.ITEMID1=A.ID AND B.ITEMID2=C.ID AND G.ITEMID1=C.ID AND G.ITEMID2=D.ID AND F.PID=D.ID AND F.CID=E.ID) AND (A.NO like '%M16') 调了下格式,发现where子句中好多and 眼都花了。。
tb 你的表…… 差了点……,希望能帮到楼主…… select 项目名,板件号,模具号 from(select (case when 板件号 is null and 模具号 is null then 项目名 end)项目名, decode(模具号,null,板件号) 板件号, 模具号, rownum rn from( select 项目名,板件号,模具号 from tb group by rollup(项目名,板件号,模具号)) where 项目名 is not null or 板件号 is not null or 模具号 is not null )order by rn desc
FROM PROJ A ,PROJ_OBJOF B,PRODUCT C,EPART D,EPART E,EBOM F,PRODUCT_OF_PART G
WHERE ( A.DEL=0 AND A.WKAID<>'3' AND B.DEL=0 AND B.WKAID<>'3' AND C.DEL=0 AND C.WKAID<>'3' AND D.DEL=0 AND D.WKAID<>'3' AND E.DEL=0 AND E.WKAID<>'3' AND F.DEL=0 AND F.WKAID<>'3' AND G.DEL=0 AND G.WKAID<>'3' ) AND ( B.ITEMID1=A.ID AND B.ITEMID2=C.ID AND G.ITEMID1=C.ID AND G.ITEMID2=D.ID AND F.PID=D.ID AND F.CID=E.ID) AND (A.NO like '%M16')
调了下格式,发现where子句中好多and 眼都花了。。
差了点……,希望能帮到楼主……
select 项目名,板件号,模具号
from(select
(case when 板件号 is null and 模具号 is null then 项目名 end)项目名,
decode(模具号,null,板件号) 板件号,
模具号,
rownum rn
from(
select 项目名,板件号,模具号 from tb group by rollup(项目名,板件号,模具号))
where
项目名 is not null or
板件号 is not null or
模具号 is not null
)order by rn desc