检索三个表之间一对多关系中的一行

Question

我有以下三个表：

----Product----

PRODUCT_ID     DESC     
1              'Pencil'   
2              'Paper'

----PRICE_BY_SUPPLIER----

PRODUCT_ID     SUPPLIER_ID     PRICE  
1              1               10  
1              2               9  
1              3               9.5  
2              1               5

----IMAGES_BY_Product----

PRODUCT_ID     NAME  
1              'pencil.img'  
1              'pen.img'  
1              'pencil_other.img'  
2              'paper.img'

我想要一个查询来提取最低价格、供应商数量持有产品和一张图片（所有供应商中一张图片）。输出查询应如下所示：

----FINAL_QUERY----

PRODUCT_ID     MIN_PRICE     IMAGE          SUPPLIER_COUNT  
1              9             'pencil.img'   3  
2              5             'paper.img'    1

我有这个查询，它返回除图像之外的所有内容。

SELECT f.PRODUCT_ID, f.DESC, x.MIN_PRICE, x.SUPPLIER_COUNT  
  FROM (
        SELECT pp.PRODUCT_ID, 
               MIN(pp.PRICE) AS MIN_PRICE, 
               COUNT(pp.PRODUCT_ID) AS SUPPLIER_COUNT 
          FROM PRICE_BY_SUPPLIER AS pp 
         GROUP 
            BY pp.PRODUCT_ID
       )   
       AS x
       INNER JOIN PRODUCT AS f 
         ON f.PRODUCT_ID = X.PRODUCT_ID

你能帮我完成我的查询吗？

Answer 1

与其参与所有这些可怕的连接，你难道不能尝试一些类似的事情吗？

SELECT PRODUCT_ID,
       (SELECT MIN(PRICE)
             FROM PRICE_BY_SUPPLIER
             WHERE PRICE_BY_SUPPLIER.PRODUCT_ID = PRODUCT.PRODUCT_ID) AS MIN_PRICE,
       (SELECT NAME
             FROM PRODUCT_IMAGES
             WHERE PRODUCT_IMAGES.PRODUCT_ID = PRODUCT.PRODUCT_ID
             LIMIT 1) AS PRODUCT_IMAGES,
       (SELECT COUNT(*)
             FROM PRICE_BY_SUPPLIER
             WHERE PRICE_BY_SUPPLIER.PRODUCT_ID = PRODUCT.PRODUCT_ID) AS SUPPLIER_COUNT
    FROM PRODUCT_ID;

Answer 2

您可以添加另一个连接来获取图像。例如，每个产品 ID 的 max 图像：

SELECT  f.PRODUCT_ID, f.DESC, x.MIN_PRICE, x.SUPPLIER_COUNT, img.Name
FROM    ( 
        select  pp.PRODUCT_ID
        ,       MIN(pp.PRICE) as MIN_PRICE
        ,       COUNT(pp.PRODUCT_ID) as SUPPLIER_COUNT 
        from    PRICE_BY_SUPPLIER as pp 
        group by 
                pp.PRODUCT_ID
        ) AS x 
JOIN    PRODUCT as f 
on      f.PRODUCT_ID = X.PRODUCT_ID
JOIN    (
        select  max(name)
        from    images_by_product
        group by
                product_id
        ) as img
on      img.PRODUCT_ID = X.PRODUCT_ID

如果图像是可选的，请将 join 替换为 outer join。根据您的 DBMS，您还可以获取随机图像。例如，在 SQL Server 中，您可以用 cross apply 替换连接：

...
on      f.PRODUCT_ID = X.PRODUCT_ID
CROSS APPLY
        (
        select  top 1 name
        from    images_by_product
        where   PRODUCT_ID = X.PRODUCT_ID
        order by
                newid()
        ) as img

或者甚至直接在 select 子句中替换：

SELECT  f.PRODUCT_ID, f.DESC, x.MIN_PRICE, x.SUPPLIER_COUNT, img.Name
,       (
        select  top 1 name
        from    images_by_product
        where   PRODUCT_ID = X.PRODUCT_ID
        order by
                newid()
        ) as img
FROM    ( 
        select  pp.PRODUCT_ID
...

Answer 3

试试这个：

SELECT DISTINCT p.product_id, MIN(pbs.price), ibp.name, pbs.supplier_id
FROM product p
INNER JOIN price_by_supplier pbs
ON  p.product_id = pbs.product_id
INNER JOIN images_by_product ibp
ON p.product_id = ibp.product_id
GROUP BY p.product_id

我能够得到你的确切输出（除了供应商计数，因为这对我来说没有意义。如果可以的话，请进一步解释。

PRODUCT_ID     MIN_PRICE     IMAGE          SUPPLIER_ID  
1              9             'pencil.img'   1  
2              5             'paper.img'    1

更新：

这是一种蹩脚的方式得到你想要的但是......它确实有效：

SELECT DISTINCT p.product_id, MIN(pbs.price), ibp.name, COUNT(pbs.supplier_id)
FROM product p
INNER JOIN images_by_product ibp
ON p.product_id = ibp.product_id
INNER JOIN price_by_supplier pbs
ON  p.product_id = pbs.product_id
GROUP BY  pbs.price
LIMIT 2


PRODUCT_ID     MIN_PRICE     IMAGE          SUPPLIER_ID  
2              5             'paper.img'     1  
1              9             'pencil.img'    3