我有这些查询:
select b.type,b.name,a.name_b,a.start_time
from runinfo a left JOIN definition b
on a.sched_table=b.parent_table
and a.name_b=b.name
where sched_table not like 'PLAN'
and to_char(start_time,'YYYYMMDD') = to_char(current_date-1,'YYYYMMDD')
order by start_time desc;
表是:
DEFINITION
----------
TYPE: COMMAND
NAME: DAVE
.........
RUNINFO
-------
START_TIME: 2019/08/15 23:59
NAME_B: DAVE
.........
START_TIME: 2019/08/15 23:58
NAME_B: DAVE
........
我得到重复的行,我听不懂:
TYPE NAME START_TIME NAME_B
COMMAND DAVE 2019/08/15 23:59 DAVE
COMMAND DAVE 2019/08/15 23:59 DAVE
COMMAND DAVE 2019/08/15 23:58 DAVE
COMMAND DAVE 2019/08/15 23:58 DAVE
为什么每条记录都有两行?
感谢和抱歉我的英语!
我想你需要一个INNER JOIN
而不是LEFT JOIN
-
SELECT DISTINCT b.type,b.name,a.name_b,a.start_time
FROM runinfo a
INNER JOIN definition b ON a.sched_table=b.parent_table
AND a.name_b=b.name
WHERE sched_table NOT LIKE '%PLAN%'
AND TO_CHAR(start_time,'YYYYMMDD') = TO_CHAR(current_date-1,'YYYYMMDD')
ORDER BY start_time DESC;
另外,我还更新了您的Like谓词。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句