在SQL中分组并更改列

截断日期可以有效地删除时间元素。但是GROUP BY子句中的列必须与投影中的非聚合列匹配,因此需要截断 gen_date 在SELECT子句中:

SELECT pm_no, revision, trunc(gen_date) as gen_date, count(*)         
FROM calendar_plan_tab 
WHERE gen_date IS NOT NULL AND gen_type='1'
Group by pm_no, revision,trunc(gen_date)
HAVING COUNT(*) > 1;

“没有其他方法可以选择原始gen\u日期了吗?”

带分析的内联视图如何 count() 哪个在主查询中用作筛选器?

select * from (
    SELECT pm_no, revision, gen_date
            , count() over (partition by pm_no, revision, trunc(gen_date)) as gen_date_cnt        
    FROM calendar_plan_tab 
    WHERE gen_date IS NOT NULL AND gen_type='1'
    )
where gen_date_cnt > 1
;

根据您的数据和精确要求,您可能需要在外部投影中使用不同的。

select distinct  pm_no, revision, gen_date, gen_date_cnt from ( 
   ....

您可以这样执行:

SELECT pm_no, revision,to_char(gen_date,'dd/mm/yyyy'), count(*) 
FROM 
calendar_plan_tab 
WHERE gen_date IS NOT NULL AND gen_type='1' Group by pm_no, 
revision,to_char(gen_date,'dd/mm/yyyy') HAVING COUNT(*) > 1;