个人中心
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-25 17:49:40 24 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

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


更多回答

24 4 0
文章推荐： Reorder relaxed atomic operations on the same object(对同一对象上的松散原子操作进行重新排序)
文章推荐： Unable to inject @GrpcClient in quarkus "The following beans match by type, but none have matching qualifiers"(无法在Quarkus中注入@GrpcClient“以下Bean按类型匹配，但没有一个具有匹配的限定符”)
文章推荐： Micronaut - Exception: manualy registered singleton by adding framework dependecy(Microronaut-例外：通过添加框架依赖项手动注册Singleton)
文章推荐： What are the cases when the smart cast doesn't work?(智能演员阵容不起作用的情况是什么？)
bug小助手
个人简介

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

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