Oracle left join

最新推荐文章于 2024-07-30 17:55:15 发布

最新推荐文章于 2024-07-30 17:55:15 发布 · 173 阅读

文章标签：

#oracle #join

Oracle小记专栏收录该内容

72 篇文章

订阅专栏

本文通过具体案例展示了如何使用SQL的LEFT JOIN进行多表连接，包括ON和WHERE子句的应用，以及如何从多个表中选择特定字段进行聚合运算。

left join可以连接两表数据可以用on 和 where 连接两表数据

             select tp.processname,
                    tp.nodename,
                    tp.plantime,
                    tk.maxtime,
                    tk.mintime,
                    tk.avgtime
               from (SELECT DISTINCT t1.processname,
                                     t4.nodename,
                                     to_char(t3.day / 3600 / 24,
                                             '9999999990.99') AS plantime
                       FROM losd_process  t1,
                            losi_taskplan t2,
                            losi_itemplan t3,
                            losd_node     t4
                      WHERE t1.process_id = t2.task_id
                        AND t2.board_id = t3.board_id
                        AND t1.process_id = t4.process_id
                        AND t4.node_id = t3.element_id
                        AND t4.nodetype = 2
                        AND t3.elementtype = 1) tp
               left join (select s.nodename,
                                 s.processname,
                                 to_char(max(s.time), '9999999990.99') maxtime,
                                 to_char(min(s.time), '9999999990.99') mintime,
                                 to_char(avg(s.time), '9999999990.99') avgtime
                            from (select (t.finishtime - t.receivetime) as time,
                                         n.nodename,
                                         p.processname
                                    from losi_token   t,
                                         losd_node    n,
                                         losd_process p
                                   where t.nodetype = 2
                                     and t.node_id = n.node_id
                                     and n.process_id = p.process_id
                                     and t.finishtime is not null) s
                           group by s.nodename, s.processname) tk
                 on (tp.processname = tk.processname and
                    tp.nodename = tk.nodename)