sql 选取分组中的第一条，显示聚合以外的列，having字句的使用

分组中的第一条：
select * from
(
select     row_number() over(partition by 列1,列2,...   order by 列1,列2,...) as rownum    -- 排序并分组
, *         -- 所需显示的字段
from 表
) as T
where T.rownum = 1

显示聚合以外的列：

SELECT  a.examroomnum ,

         a.positionnum ,

         a.人数 ,

         a.suminterviewscore ,

         a.avginterviewscore ,

         b.Department

 FROM    ( SELECT    examroomnum ,

                     positionnum ,

                     COUNT(*) AS 人数 ,

                     SUM(interviewscore) suminterviewscore ,

                     AVG(interviewscore) avginterviewscore

           FROM      examinee

           GROUP BY  examroomnum ,

                     positionnum

         ) a

         LEFT JOIN examinee b ON a.examroomnum = b.examroomnum

                                 AND a.positionnum = b.positionnum

 

having字句的使用：

HAVING语句通常与GROUP BY语句联合使用，用来过滤由GROUP BY语句返回的记录集。

HAVING语句的存在弥补了WHERE关键字不能与聚合函数联合使用的不足。

语法：

SELECT column1, column2, ... column_n, aggregate_function (expression)FROM tablesWHERE predicatesGROUP BY column1, column2, ... column_nHAVING condition1 ... condition_n;

同样使用本文中的学生表格，如果想查询平均分高于80分的学生记录可以这样写：

SELECT id, COUNT(course) as numcourse, AVG(score) as avgscore

FROM student

GROUP BY id

HAVING AVG(score)>=80;

在这里，如果用WHERE代替HAVING就会出错。

 

使用示例************************************************************************************

计算每种物料入库总数大于当前库存的最早一笔入库的时间

select productid,inamount,DATEDIFF(day,operatedate,getdate()) from(
select row_number() over(partition by productid order by operatedate) as rownum,*
from (select p.productid,p.inamount,k.operatedate from
(select productid,isnull(sum(amount),0) as inamount from P_ProductBillSumTab a with(nolock)
where tagid in ('1101','1102','1105','1106') group by productid having isnull(sum(amount),0)>(select sum(storage) as storate from p_productbatchtab b with(nolock)
where productid=a.productid)) p
left join P_ProductBillSumTab k with(nolock) on k.productid=p.productid) as mm) as T
where T.rownum = 1 order by operatedate

****************************************************************************************************

select *,DATEDIFF(day,t.operatedate,getdate()) from (
(select row_number() over(partition by productid order by samount) as rownum,* from(
select aa.*,c.storage from (
select productid,amount,operatedate,
(select SUM(amount) from product_inlist b where b.productid=a.productid and b.id>=a.id) as samount
from product_inlist a)aa
left join product_storage c on c.productid=aa.productid
where samount>c.storage
) k )) as T where T.rownum = 1
order by operatedate desc