SqlServer 获取分组的第一条记录

测试数据

CREATE TABLE SPJG(
  SP varchar(100),
  JJ varchar(100),
  JG numeric(5,2)
) 

declare @ID int,@PID int,@JJ varchar(20)
set @ID =1 
while(@ID<6)
begin
	set @Pid=1
	while (@PID<5)
		begin
			if @PID=1 set @JJ='春季'
			if @PID=2 set @JJ='夏季'
			if @PID=3 set @JJ='秋季'
			if @PID=4 set @JJ='冬季'
			insert into SPJG values('SP'+cast(@ID as varchar),@JJ,50+round(50*rand(),0))
			set @PID=@PID+1
		end
	set @ID=@ID+1
end

获取每一商品最高价的时候

这里用group by 不能处理，用Partition By

select * from (select sp,JJ,JG, ROW_NUMBER() over(partition by sp order by  jg desc) as rn  from SPJG )
  as t where t.rn=1