Oracle查询修改

Question

ORACLE QUERY

CREATE OR REPLACE FORCE VIEW inventory (
"ARTICLENO ",
"EAN",
"NAME",
"BRAND"
) AS
SELECT
m.ARTICLENO,
m.EAN,
m.NAME,
p.BRAN
FROM
ITEM   m,
P_ITEM p
WHERE
m.ean = p.ean OR m.ARTICLE=p.ARTICLE;

问题在m.article=p。文章我只需要一个记录当比较而不是全部)

Table = ITEM: as m

x

有一些错误的列名(p.b an和p.p article)，但似乎你只需要选择DISTINCT记录…

WITH
item AS
(
Select 1 "ARTICLENO", 100 "EAN", 'a' "NAME" From Dual UNION ALL
Select 2 "ARTICLENO", Null "EAN", 'x' "NAME" From Dual
),
p_item AS
(
Select 1 "ARTICLENO", 100 "EAN", 'a' "NAME", 'b1' "BRAND" From Dual UNION ALL
Select 2 "ARTICLENO", Null "EAN", 'x' "NAME", 'b2' "BRAND" From Dual UNION ALL
Select 2 "ARTICLENO", Null "EAN", 'x' "NAME", 'b2' "BRAND" From Dual UNION ALL
Select 2 "ARTICLENO", Null "EAN", 'x' "NAME", 'b2' "BRAND" From Dual 
)
SELECT DISTINCT
m.ARTICLENO,
m.EAN,
m.NAME,
p.BRAND
FROM
ITEM   m
INNER JOIN
P_ITEM p ON(m.ean = p.ean OR m.ARTICLENO = p.ARTICLENO)