select b.top_lvl_itm_num,
b.grp_lvl_itm_num,
b.cmpnt_lvl_itm_num,
b.lvl,
b.grp_lvl_plt,
b.cmpnt_lvl_plt ,
a.sys_category from v_lttt_cost b
left join v_lttt_sys_category_map a on b.top_lvl_itm_num=a.top_lvl_itm_num and
b.grp_lvl_itm_num=b.grp_lvl_itm_num and b.cmpnt_lvl_itm_num=b.cmpnt_lvl_itm_num and b.lvl=a.lvl
where b.grp_lvl_itm_num='MV' and
b.cmpnt_lvl_itm_num="ON1433' and
b.lvl=12 and
b.grp_lvl_plt='1110'
这是我正在使用的代码。我本应该只回11排,但现在却给了我121排。左边的表格有11行。
b.cmpnt_lvl_itm_num="ON1433'
此处使用"ON1433'",应该有错误
您在联接的两侧都使用相同的别名b
,这导致了更多的数据。
我修改了查询:
select b.top_lvl_itm_num,
b.grp_lvl_itm_num,
b.cmpnt_lvl_itm_num,
b.lvl,
b.grp_lvl_plt,
b.cmpnt_lvl_plt ,
a.sys_category
from v_lttt_cost b
left join v_lttt_sys_category_map a
on b.top_lvl_itm_num=a.top_lvl_itm_num and
b.grp_lvl_itm_num=a.grp_lvl_itm_num and
b.cmpnt_lvl_itm_num=a.cmpnt_lvl_itm_num and
b.lvl=a.lvl
where b.grp_lvl_itm_num='MV' and
b.cmpnt_lvl_itm_num='ON1433' and
b.lvl=12 and
b.grp_lvl_plt='1110'
UPDATE:从您的注释中,即使在放入LEFT JOIN之后,您的数据也会相乘11倍,这意味着右表中的JOIN组合有很多行(在本例中为11行(。如果您的数据中有重复项,您可以使用DISTINCT
子句来筛选出重复项。