如何简化这个Sql查询

The Table - Query has 2 columns (functionId, depFunctionId)

表 - 查询有2列(functionId,depFunctionId)

I want all values that are either in functionid or in depfunctionid

我想要所有在functionid或depfunctionid中的值

I am using this:

我用这个:

select distinct depfunctionid from Query
union 
select distinct functionid from Query

How to do it better?

怎么做得更好?

4 个解决方案

#1

I think that's the best you'll get.

我认为这是你能得到的最好的。

#2

Thats as good as it gets I think...

这就像我认为的那样好......

#3

Lose the DISTINCT clauses, as your UNION (vs UNION ALL) will take care of removing duplicates.

丢失DISTINCT子句,因为您的UNION(vs UNION ALL)将负责删除重复项。

An alternative - but perhaps less clear and probably with the same execution plan - would be to do a FULL JOIN across the 2 columns.

另一种选择 - 但可能不那么明确,可能具有相同的执行计划 - 将在两列中进行全加入。

SELECT 
    COALESCE(Query1.FunctionId, Query2.DepFunctionId) as FunctionId
FROM Query as Query1
FULL OUTER JOIN Query as Query2 ON
    Query1.FunctionId = Query2.DepFunctionId

#4

I am almost sure you can loose the distinct's. When you use UNION instead of UNION ALL, duplicated results are thrown away.

我几乎可以肯定你可以放弃那些不同的东西。使用UNION而不是UNION ALL时,会丢弃重复的结果。

It all depends on how heavy your inline view query is. The key for a better perfomance would be to execute only once, but that is not possible given the data that it returns.

这完全取决于您的内联视图查询的重量。更好性能的关键是只执行一次,但鉴于它返回的数据,这是不可能的。

If you do it like this :

如果你这样做:

select depfunctionid , functionid from Query
group by depfunctionid , functionid

It is very likely that you'll get repeated results for depfunctionid or functionid.

你很可能会得到depfunctionid或functionid的重复结果。

I may be wrong, but it seems to me that you're trying to retrieve a tree of dependencies. If thats the case, I personally would try to use a materialized path approach.

我可能错了,但在我看来,你正在尝试检索依赖树。如果是这样,我个人会尝试使用物化路径方法。

If the materialized path is stored in a self referencing table name, I would retrieve the tree using something like

如果物化路径存储在自引用表名中,我将使用类似的方法检索树

select asrt2.function_id
from   a_self_referencig_table asrt1,
       a_self_referencig_table asrt2
where  asrt1.function_name = 'blah function'
and    asrt2.materialized_path like (asrt1.materialized_path || '%')
order  by asrt2.materialized_path, asrt2.some_child_node_ordering_column

This would retrieved the whole tree in the proper order. What sucks is having to construct the materialized path based on the function_id and parent_function_id (or in your case, functionid and depfunctionid), but a trigger could take care of it quite easily.

这将以正确的顺序检索整个树。糟糕的是必须基于function_id和parent_function_id(或者在你的情况下,functionid和depfunctionid)构造物化路径,但触发器可以很容易地处理它。

#1