sql - 将多个 SELECT 查询合并到一个 VIEW 中

Question

结合这些查询

我想要一个结合多个查询的视图。就像是

SELECT
  products.id AS id
FROM products

SELECT
  publications.visible AS online_visible
FROM products
INNER JOIN publications ON publications.product_id = products.id
WHERE publication.name = 'online'

SELECT
  publications.visible AS retail_visible
FROM products
INNER JOIN publications ON publications.product_id = products.id
WHERE publication.name = 'retail'

SELECT
  SUM(sales.quantity) AS year_sales
FROM products
LEFT OUTER JOIN sales ON sales.product_id = products.id
WHERE sales.date > current_date - interval '365' day
GROUP BY products.id

SELECT
  SUM(sales.quantity) AS month_sales
FROM products
LEFT OUTER JOIN sales ON sales.product_id = products.id
WHERE sales.date > current_date - interval '30' day
GROUP BY products.id

期望的最终结果

我最终想要的是一张像

| ID |online_visible|retail_visible|month_sales|year_sales|
|----|--------------|--------------|-----------|----------|
|  1 |   false      |    true      |     35    |   420    |

吉文斯

• 我已经单独测试了每个查询，所以我知道它们每个都有效。
• 您可能会认为这里的任何错字都是由于我试图简化我的问题造成的，而不是我的问题的原因。
• 很可能我的问题以前被问过，但我不知道足够的 SQL 来提出正确的问题

我试过的

我尝试了许多组合，我使用 UNION 或 UNION ALL 来组合查询。

结合我使每个 SELECT 包含所有其他 SELECT 但具有空值。像这样

SELECT
  products.id AS id,
  null::boolean AS online_visible,
  null::boolean AS retail_visible,
  null:int AS month_sales,
  null:int AS year_sales
FROM products

其他类似

标签： sqlpostgresqlsql-view

Answer 1

您可以使用此查询获得所需的结果：

WITH products_publications AS (
  SELECT
    products.id AS id,
    MAX(CASE WHEN publication.name = 'online' THEN publications.visible END) 
      AS online_visible,
    MAX(CASE WHEN publication.name = 'retail' THEN publications.visible END) 
      AS retail_visible
  FROM products
  INNER JOIN publications ON publications.product_id = products.id
  WHERE publication.name IN ('online', 'retail')
  GROUP BY products.id
), products_sales AS (
  SELECT
    products.id AS id,
    SUM(CASE WHEN sales.date > current_date - interval '30' day 
        THEN sales.quantity END) AS month_sales,
    SUM(CASE WHEN sales.date > current_date - interval '365' day 
        THEN sales.quantity END) AS year_sales
  FROM products
  LEFT OUTER JOIN sales ON sales.product_id = products.id
  GROUP BY products.id
)
SELECT
  p.id,
  pp.online_visible,
  pp.retail_visible,
  ps.month_sales,
  ps.year_sales
FROM products p
LEFT JOIN products_publications pp ON pp.id = p.id
LEFT JOIN products_sales ps ON ps.id = p.id