对SQL多次使用函数的结果

Question

我正在尝试生成一个显示表中重复项的结果。我发现的一种获取重复项并显示它们的方法是通过内部联接再次运行select语句。但是，我的一列需要是函数的结果，我唯一能想到的就是使用别名，但是我不能在SELECT语句中使用两次别名。

我不确定运行此代码以获得所需副本的最佳方式是什么。

下方的我的代码

SELECT EXTRACT(YEAR_MONTH FROM date) as 'ndate', a.transponderID  
FROM dispondo_prod_disposition.event a
inner JOIN (SELECT EXTRACT(YEAR_MONTH FROM date) as ???, 
transponderID, COUNT(*)
FROM dispondo_prod_disposition.event
GROUP BY mdate, transponderID 
HAVING count(*) > 1 ) b
ON ndate = ???  
AND a.transponderID  = b.transponderID 
ORDER BY b.transponderID 

Answer 1

SELECT b.ndate, transponderID  
FROM       dispondo_prod_disposition.event a
INNER JOIN ( SELECT EXTRACT(YEAR_MONTH FROM date) as ndate, 
transponderID
FROM dispondo_prod_disposition.event
GROUP BY 1, 2
HAVING COUNT(*) > 1 ) b USING (transponderID)
WHERE b.ndate = ???  -- for example, WHERE b.ndate = 202201
ORDER BY transponderID