SQL 写法对比

with  MAX_CTE
        as (select max(DAY_FLOW_YEAR) as DAY_FLOW_YEAR,STAT_DAY
                from NS_KPI
                where (STAT_DAY between dateadd(day,1,dateadd(year,datediff(year,0,dateadd(year,0,getdate())),-1))
                                and     getdate()) and ZONE_ID = 'xd'
                group by DAY_FLOW_YEAR,STAT_DAY)
    select top 1 STAT_DAY,ZONE_ID,QUALIFIED_PRESS_RATE,AVG_PRESS,DAY_FLOW_YEAR,AVG_DAY_FLOW_DAY,TOTAL_FLOW_YEAR,
        MAX_HOUR_FLOW_HIS,CREATE_TIME
    from NS_KPI N
    where ZONE_ID = 'xd' and exists ( select 1
                                            from MAX_CTE
                                            where STAT_DAY = N.STAT_DAY )
    order by STAT_DAY desc;

 

select top 1 STAT_DAY,ZONE_ID,QUALIFIED_PRESS_RATE,AVG_PRESS,DAY_FLOW_YEAR,AVG_DAY_FLOW_DAY,TOTAL_FLOW_YEAR,
    MAX_HOUR_FLOW_HIS,CREATE_TIME
    from NS_KPI N
    where DAY_FLOW_YEAR = (select max(DAY_FLOW_YEAR)
                            from NS_KPI
                            where STAT_DAY between dateadd(day,1,
                                                            dateadd(year,datediff(year,0,dateadd(year,0,getdate())),
                                                                    -1))
                                            and     getdate() and ZONE_ID = 'xd')
    order by CREATE_TIME desc;

 

 

这两个语句可达到同样的效果，但是对于容易理解和复杂情况上有所出入，有时还是需要简易为主