SQL Server GROUP BY 和 FIRST

Question

我有很多画廊里的物品。我有很多照片。图片在画廊里。

我想创建一个视图，在其中列出画廊以及画廊中第一张图片的一些属性。

类似于：

ALTER VIEW [foto].[gallery_with_picture]    
AS 
select
        main.*
        ,FIRST(pics.[picture_id])
        ,FIRST(pics.[picture_width])
        ,FIRST(pics.[picture_height])
        ,FIRST(pics.[URLPart] as picture_url_part)
        ,FIRST(pics.[Extension] as picture_extension)
    from
        [v_gallery] main
        left join [v_picture_recursive] pics on main.[foto_reference_picture_group_modeling_object/obj_id] = pics.woc_root
    group by
        main.*
    order by
        pics.[picture_id]

当然这不起作用，因为 SQL Server 没有可用的 FIRST 聚合函数。或者确实如此？

或者我应该使用 top1 进行内部选择，因为没有更简单的解决方案？

Answer 1

这是一种方法。

ALTER VIEW [foto].[gallery_with_picture] 
AS
SELECT main.*, /*But best to list columns explicitly*/
       pics.[picture_id],
       pics.[picture_width],
       pics.[picture_height],
       pics.[URLPart]   AS picture_url_part,
       pics.[Extension] AS picture_extension
FROM   [v_gallery] main
       OUTER APPLY
       (SELECT TOP 1 *
        FROM   [v_picture_recursive] pics
        WHERE  main.[foto_reference_picture_group_modeling_object/obj_id] =
               pics.woc_root
        ORDER  BY pics.[picture_id]) pics  

或者另一个

ALTER VIEW [foto].[gallery_with_picture] 
AS
 WITH pics AS ( 
 SELECT *, 
        ROW_NUMBER() OVER (PARTITION BY woc_root 
                               ORDER BY [picture_id]) RN 
 FROM [v_picture_recursive])
SELECT main.*,
       pics.[picture_id],
       pics.[picture_width],
       pics.[picture_height],
       pics.[URLPart]   AS picture_url_part,
       pics.[Extension] AS picture_extension
FROM   [v_gallery] main
       LEFT JOIN pics
         ON main.[foto_reference_picture_group_modeling_object/obj_id] =
            pics.woc_root
            AND RN = 1