SqlServer中 Partition By 的使用

电玩女神 2022-05-20 03:15 563阅读 0赞

**1:数据库表结构：**

（产品图片表）

select * from product_imgs

![70][]

**2:应用场景：**

可见表中每一个产品(pro\_id)可能对应多个图片(img)，我想要按照添加时间的先后顺序，获得其中每一个产品的第一张图片

**3:尝试使用 group by **

select top 1 pro_id,img,create_time from product_imgs group by pro_id,img,create_time order by create_time

![70 1][]

发现无法满足每一个产品的图片

**4:使用Partition by **

select * from (select pro_id,img,create_time, ROW_NUMBER() over(partition by pro_id order by  create_time) as row_sort  from product_imgs )
      as t where t.row_sort=1

![70 2][]

可以满足需求，所以Partition by可以理解为 **对多行数据分组后排序取每个产品的第一行数据**

**--20180712补充**

最近又发现  cross apply 在联表的时候可以代替 partition by ,查询的效果是一样的

select a.id,a.title,t.* from Product as a
    cross apply 
    (select top 1 b.pro_id,b.img  from product_imgs as b where b.pro_id=a.id)
    as t

![70 3][]

**--20190311补充**

**1.应用场景：查询学生最近一次各分类的体测成绩的分数**

DECLARE @beginTime DATETIME = '2018-02-25 00:00:00'; -- datetime
    DECLARE @endTime DATETIME = '2019-07-13 00:00:00'; -- datetime
    DECLARE @schoolId VARCHAR(100) = '3301080020'; -- datetime
    DECLARE @classId VARCHAR(100) = '33010800202018001'; -- datetime
    DECLARE @groupId VARCHAR(100) = '761'; -- datetime
     --1.Cross APPLY + PARTITION by
     SELECT a.student_id,c.type,c.score,c.NewCol FROM dbo.Student  AS a
     CROSS APPLY
     (
        SELECT b.student_id,b.type,b.score,
    	ROW_NUMBER() OVER(PARTITION BY b.type ORDER BY b.intime desc) AS NewCol
    	FROM dbo.StudentTest AS b
    	WHERE b.intime BETWEEN @beginTime AND @endTime
    	AND type IN (1,2,3,6,7,8) AND a.student_id=b.student_id
    	--GROUP BY b.student_id,b.type,b.score,b.intime
     )
     AS c
     WHERE a.class_id=@classId 
     AND c.NewCol=1
     
    
     --2.Left Join + PARTITION by
    DECLARE @beginTime DATETIME = '2018-02-25 00:00:00'; -- datetime
    DECLARE @endTime DATETIME = '2019-07-13 00:00:00'; -- datetime
    DECLARE @schoolId VARCHAR(100) = '3301080020'; -- datetime
    DECLARE @classId VARCHAR(100) = '33010800202018001'; -- datetime
    DECLARE @groupId VARCHAR(100) = '761'; -- datetime
    SELECT a.student_id,c.type,c.score,c.NewCol FROM dbo.Student  AS a
    LEFT JOIN 
     (
        SELECT 
    	b.student_id,b.type,b.score,
    	ROW_NUMBER() OVER(PARTITION BY b.student_id,b.type ORDER BY b.intime desc) AS NewCol
    	FROM dbo.StudentTest AS b
    	WHERE b.intime BETWEEN @beginTime AND @endTime
    	AND type IN (1,2,3,6,7,8)
     )
     AS c ON a.student_id=c.student_id
     WHERE a.class_id=@classId
     AND c.NewCol=1

**2.两者区别：**

**CrossApply 由于在内指定了联表字段，故Partition By不用加StudentId分组，而Left Join 在外指定联表字段，故Partition By不用加StudentId分组**

**注：inner join 和 cross apply的不同，点击https://blog.csdn.net/liuchang19950703/article/details/81015853**

[70]: /images/20220520/9b73d39246c54fab87f97d08d3eefd33.png
[70 1]: /images/20220520/0f74f66d01fc411b870e23b7446b5247.png
[70 2]: /images/20220520/5ef149fd799a4db284364353aaa6c603.png
[70 3]: /images/20220520/1f9d11e294b5458dacb8b64c9e48e968.png