如何使用SQL查询从表中根据条件选择特定的行数？

Question

我将照片图像存储在Server数据库中。图像使用一个名为PhotoType的列存储。

PhotoTypes有很多种--其中一些是：

CheckList, Installation, Audit, SignOff ...

现在，我希望从数据库中为CheckList选择2张照片、2张用于安装的照片、1张用于审核的照片和1张用于SignOff的照片(针对每个客户)。

因此，将有6张照片将被选中，虽然数据库可能有许多照片为客户。

如何在SQL查询中做到这一点？

感谢并致以问候

克里希纳

Answer 1

将公共表表达式与row_number()结合使用

;with cte as (
  select *
    , rn = row_number() over (
      partition by Customer, PhotoType
      order by Id)
  from Photo 
  where PhotoType in ('CheckList','Installation','Audit','SignOff')
)
select *
from cte
where rn = 1 
  or (rn = 2 and PhotoType in ('CheckList','Installation');

使用子查询(派生表)代替cte

select *
from (
  select *
    , rn = row_number() over (
      partition by Customer, PhotoType
      order by Id)
  from Photo 
  where PhotoType in ('CheckList','Installation','Audit','SignOff')
  ) as sub
where rn = 1 
  or (rn = 2 and PhotoType in ('CheckList','Installation');

Answer 2

   select top 2 * from temp# where [Type]='CheckList'
   union all
   select top 2 * from temp# where [Type]='Installation'
   union all
   select top 1 * from temp# where [Type]='Audit'
   union all
   select top 1 * from temp# where [Type]='SignOff'