个人中心
gpt4 book ai didi

How can I "SELECT out" multiple differently-defined rows from a large join table without re-defining it for each row(如何从大型连接表中“选择”出多个定义不同的行，而不为每一行重新定义它)

转载 作者：bug小助手 更新时间：2023-10-26 20:09:36 26 4
gpt4 key购买 nike



I am using postgres.

我用的是Postgres。


What I have right now is like this:

我现在的情况是这样的：


SELECT <statement> FROM (
<MASSIVE JOIN STATEMENT>
)
UNION ALL
SELECT <different statement> FROM (
<SAME MASSIVE JOIN STATEMENT>
)

What I would really like is to have is a way to accomplish the same thing without having to put in the same massive join statement multiple times. Is there a way to accomplish this?

我真正想要的是有一种方法来完成相同的事情，而不必多次输入相同的大量Join语句。有什么办法可以做到这一点吗？


Thank You!!

谢谢你！！


I tried giving the table an alias and referencing the alias in the second SELECT statement, but it didn't work. Postgres says the relation does not exist.

我尝试为表提供别名，并在第二个SELECT语句中引用该别名，但不起作用。波斯格雷斯说，这种关系并不存在。


更多回答
优秀答案推荐

Given its the same source for both selects, have you tried using a CTE?

鉴于这两个选择的来源相同，您尝试过使用CTE吗？


e.g:

例如：


WITH CTE AS (
  -- Your MASSIVE JOIN STATEMENT here
)

SELECT <statement> FROM CTE
UNION ALL
SELECT <different statement> FROM CTE;


You can make a table VIEW for the massive SELECT.

您可以为海量选择创建一个表视图。


However inspect the massive SELECT. In a similar case I had an outer join that could be simplified by a GROUP BY. That would be lucky though.

然而，检查一下海量精选。在一个类似的例子中，我有一个外连接，可以通过GROUP BY来简化。不过，这将是幸运的。


As you are doing ... UNION ALL ..., you probably have SELECT 1, ... UNION ALL SELECT 2, ... too. Maybe some of the joins are just for references.

就像你现在所做的那样。联合所有...，您可能已经选择了1，...联合全选2，...也是。也许有些联接仅供参考。


Not a very satisfactory answer but the question seems to indicate that a simplification should be possible rather than a super pivot table or stored procedure.

不是一个非常令人满意的答案，但这个问题似乎表明，应该可以进行简化，而不是超级透视表或存储过程。



Another way is by wrapping your original query in a subquery and then selecting from it in the UNION ALL part, you can get it working.

另一种方法是将原始查询包装在子查询中，然后在Union all部件中进行选择，这样就可以使其正常工作。


SELECT <statement>
FROM (
  SELECT <columns>
  FROM (
    -- your massive join statement goes here
    -- you can also include any filters or conditions you need
    SELECT <columns>
    FROM <your_tables>
    WHERE <your_conditions>
  ) AS subquery1

  UNION ALL

  SELECT <different statement>
  FROM (
    -- the same massive join statement
    SELECT <columns>
    FROM <your_tables>
    WHERE <your_conditions>
  ) AS subquery2
) AS combined_query;

This avoids repeating the massive join statement while achieving the desired outcome

这避免了在实现所需结果的同时重复大量连接语句


更多回答

26 4 0
文章推荐： Get params for category and page(获取类别和页面的参数)
文章推荐： flutter's text with gradient color appears differently in ios and android(在iOS和Android中，带有渐变颜色的Ffltter文本显示方式不同)
文章推荐： How do I create a function such that a method can be called directly on the function return value?(如何创建一个函数，以便可以在函数返回值上直接调用方法？)
文章推荐： Illegal use of undefined type(非法使用未定义的类型)
bug小助手
个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章
滴滴打车优惠券免费领取
滴滴打车优惠券
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com