第一个查询数据在第二查询中使用

Question

我有一个查询 get_product ：

select A.product_id,
A.name, A.description, A.type_id,
B.series_name
product_data A
inner join
series B
on A.series_raw_id = B.series_raw_id 
where A.product_id = 503061
and A.registration_type_id = 4
order by B.series_name 

和第二个查询

select B.series_name,
A.TEMPACC_STATUS 
FROM 
ACCESS_FACT A
inner join 
**get_product** B
on A.TEMPACC_PRODUCT_ID = B.product_id
where A.TEMPACC_DATE_ID between 6717 and 6808 
and A.reason_id_total = 0
group by Series_name, 
STATUS

在第二个查询中，我们使用第一个查询中的数据（get_product是第一个查询）。我如何在这里找到该表？

Answer 1

您可以将使用 strause。

例如，

WITH get_product AS
  (SELECT A.product_id,
    A.name,
    A.description,
    A.type_id,
    B.series_name product_data A
  INNER JOIN series B
  ON A.series_raw_id         = B.series_raw_id
  WHERE A.product_id         = 503061
  AND A.registration_type_id = 4
  ORDER BY B.series_name
  )
SELECT B.series_name,
  A.TEMPACC_STATUS
FROM ACCESS_FACT A
INNER JOIN get_product B
ON A.TEMPACC_PRODUCT_ID = B.product_id
WHERE A.TEMPACC_DATE_ID BETWEEN 6717 AND 6808
AND A.reason_id_total = 0
GROUP BY Series_name,
  STATUS;

或者，您可以使用 inline View

SELECT B.series_name,
  A.TEMPACC_STATUS
FROM ACCESS_FACT A
INNER JOIN
  (SELECT A.product_id,
    A.name,
    A.description,
    A.type_id,
    B.series_name product_data A
  INNER JOIN series B
  ON A.series_raw_id         = B.series_raw_id
  WHERE A.product_id         = 503061
  AND A.registration_type_id = 4
  ORDER BY B.series_name
  ) B ON A.TEMPACC_PRODUCT_ID = B.product_id
WHERE A.TEMPACC_DATE_ID BETWEEN 6717 AND 6808
AND A.reason_id_total = 0
GROUP BY Series_name,
  STATUS;