总是根据某些条件从表2中得到1条记录

Question

我有两张桌子，产品和ProductImages。

产品表有2列: ProductID和Name

ProductImages表有4列: ID、ProductID、ImageName和Primary(bit)。

产品和ProductImages之间的关系是一对多的，所以一个产品可以有很多图像，但是对于每一个产品，只有一个ProductImage是主要的。

我需要编写一个查询，以获得所有产品的主要图像。如果产品没有主图像，则应该获取ProductId的第一条记录。

样本产品表

| 1  | P1 |
| 2  | P2 |
| 3  | P3 |
| 4  | P4 |

样例productImage表

| 1   | 1 | P1-1 | 1
| 2   | 1 | P1-2 | 0
| 3   | 1 | P1-3 | 0
| 4   | 1 | P1-4 | 0
| 5   | 2 | P2-1 | 1
| 6   | 2 | P2-2 | 0
| 7   | 3 | P3-1 | 0
| 8   | 3 | P3-2 | 0
| 9   | 4 | P4-1 | 0
| 10  | 4 | P4-2 | 0

输出表

| 1   | 1 | P1-1 | 1
| 5   | 2 | P2-1 | 1
| 7   | 3 | P3-1 | 0
| 9   | 4 | P4-1 | 0

我希望我能澄清我的问题。请询问是否需要进一步澄清。

Answer 1

您可以简单地使用row_number窗口函数这样做：

select * from Products p
join (select *, row_number()  
                 over(partition by ProductID order by ID) rn from ProductImages)pi 
      on p.ProductID = pi.ProductID and pi.rn = 1

我假设主图像ID将先于非主图像ID。

Answer 2

这是一种“快速和肮脏”，但我的工作：

SELECT  pr.ProductID, pr.Name, prim.ImageName, 1 AS IsPrimary
FROM    @product pr
        INNER JOIN @productimage prim ON pr.ProductID = prim.ProductID
WHERE   prim.[Primary] = 1
UNION ALL
SELECT  pr.ProductID, pr.Name, prim.ImageName, 0 AS IsPrimary
FROM    @product pr
        INNER JOIN
        -- Get any image for this Product MIN, MAX,...what you want
        (
            SELECT  ProductID, MIN(ImageName) AS ImageName
            FROM    @productimage
            WHERE   [Primary] = 0
            GROUP BY ProductID

        ) prim ON pr.ProductID = prim.ProductID
        LEFT JOIN
        --Primary Images:
        (
            SELECT ProductID
            FROM @productimage pri
            WHERE pri.[Primary] = 1
        ) primages ON pr.ProductID = primages.ProductID
WHERE   primages.ProductID IS NULL --there is no primary image

第一个查询针对所有具有主映像的产品，第二个查询针对的是那些没有主映像的产品。

Answer 3

使用较少的联接，这看起来很整洁，并且可以完成这项工作。

select a.ProductId, ProductName, ImageName, b.ID as ImageID, b.[Primary] , b.[Primary] as IsPrimary
into a
from tProduct a 
inner join tProductImages b on a.ProductID = b.ProductID 
where b.[Primary] = 1

;WITH cte AS
(
   SELECT a.ProductId, ProductName, ImageName, b.ID as ImageID, b.[Primary] as IsPrimary ,
         ROW_NUMBER() OVER (PARTITION BY b.ProductId ORDER BY b.ID) AS rn
   from tProduct a 
    inner join tProductImages b on a.ProductID = b.ProductID 
    where b.[Primary] = 0 and a.ProductID not in (select ProductId from a)
)
SELECT ImageID, ProductId, ProductName, ImageName, IsPrimary
FROM cte WHERE rn = 1
union
select  ImageID, ProductId, ProductName, ImageName, IsPrimary
from a

drop table a

供你参考

​

​

编辑：

我只是重复了一遍，直到我意识到不需要联合查询，只有在下面才够了。

;WITH cte AS
(
    SELECT a.ProductId, ProductName, ImageName, b.ID as ImageID, b.[Primary] as IsPrimary ,
         ROW_NUMBER() OVER (PARTITION BY b.ProductId ORDER BY b.[Primary] desc, b.ID) AS rn
    from tProduct a 
    inner join tProductImages b on a.ProductID = b.ProductID 
)
select * from cte where rn = 1