Postgresql:我如何连接同一个表与where条件

Question

我有一个表statistic。表中有step_index，如1、2、3和4。我需要查询添加week2和total_request2，其中周是37，并与相同的step_index连接自表。

的例子:

step_index|type    |year|week|total_request|
----------+--------+----+----+-------------+
1|Blasting|2021|  38|            1|
2|Blasting|2021|  38|            1|
3|Blasting|2021|  38|            1|
4|Blasting|2021|  38|            1|
1|Blasting|2021|  37|            6|
2|Blasting|2021|  37|            6|
3|Blasting|2021|  37|            6|
4|Blasting|2021|  37|            6|

结果应该是:

step_index|type    |year|week|total_request|week2|total_request2|
----------+--------+----+----+-------------+-----+--------------+
1|Blasting|2021|  38|            1|   37|             6|
2|Blasting|2021|  38|            1|   37|             6|
3|Blasting|2021|  38|            1|   37|             6|
4|Blasting|2021|  38|            1|   37|             6|

我尝试了内连接和with子句，但没有结果week2和total_request2。

Answer 1

WITH CTE(STEP_INDEX,TYPE,YEAR,WEEK,TOTAL_REQUEST) AS
(
SELECT 1,'BLASTING',2021,38,1  UNION ALL 
SELECT 2,'BLASTING',2021,38,1  UNION ALL 
SELECT 3,'BLASTING',2021,38,1  UNION ALL 
SELECT 4,'BLASTING',2021,38,1  UNION ALL 
SELECT 1,'BLASTING',2021,37,6  UNION ALL 
SELECT 2,'BLASTING',2021,37,6  UNION ALL
SELECT 3,'BLASTING',2021,37,6  UNION ALL
SELECT 4,'BLASTING',2021,37,6  
)
SELECT C.STEP_INDEX,C.TYPE,C.YEAR,C.WEEK,C.TOTAL_REQUEST,
X.WEEK AS WEEK_2,X.TOTAL_REQUEST AS TOTAL_REQUEST_2
FROM CTE AS C
LEFT JOIN LATERAL
(
SELECT C2.STEP_INDEX,C2.TYPE,C2.YEAR,C2.WEEK,C2.TOTAL_REQUEST
FROM CTE AS C2
WHERE C.STEP_INDEX=C2.STEP_INDEX AND C.WEEK-1=C2.WEEK AND C.YEAR=C2.YEAR
)X ON TRUE
WHERE C.YEAR=2021 AND C.WEEK=38

基于您的样本数据

Answer 2

根据您提供的内容，以下是我对您想要的内容的最佳猜测。如果你能明确主键和一些更具体的信息，你正在寻找什么，我可以修改我的查询更好。

select s.step_index
,s.type
,s.year
,s.week
,s.total_request
,s2.week
,s2.total_request2
from statistic s
,statistic s2
where s2.week = 37
and s2.step_index = s.step_index
and s.week = 38

Answer 3

您可以将其作为显式JOIN来执行。正确的语法应该是:

select s.step_index, s.type, s.year, s.week, s.total_request,
s2.week as week2, s2.total_request as total_request2
from statistic s join
statistic s2
on s2.year = s.year and
s2.step_index = s.step_index and
s2.week = 37 and
s.week = 38