如何从另一个表但类型不同的多个连接记录筛选数据库表？

Question

我有一个products表和相应的ratings表，其中包含一个外键product_id、grade(int)和type，这是一个枚举接受值robustness和price_quality_ratio。

分数接受1到10之间的值。例如，如果我想过滤产品，robustness的最低等级是7，price_quality_ratio的最低等级是8，那么查询会是什么样的呢？

Answer 1

你可以参加两次，每次评分一次。inner join排除任何评级标准不合格的产品，

select p.*
from products p
inner join rating r1 
    on r1.product_id = p.product_id
    and r1.type = 'robustness'
    and r1.rating >= 7
inner join rating r2 
    on r2.product_id = p.product_id
    and r2.type = 'price_quality_ratio'
    and r2.rating >= 8

另一个选项是使用do条件聚合。这只需要一个join，然后是一个group by；在having子句中检查评等标准。

select p.product_id, p.product_name
from products p
inner join rating r 
    on r.product_id = p.product_id
    and r.type in ('robustness', 'price_quality_ratio')
group by p.product_id, p.product_name
having 
    min(case when r.type = 'robustness' then r.rating end) >= 7
    and min(case when r.type = 'price_quality_ratio then r.rating end) >= 8

Answer 2

@GMB提出的JOIN也将是我的第一个建议。如果由于必须维护太多的rX.rating而变得过于复杂，您还可以使用嵌套查询：

SELECT *
FROM (
  SELECT p.*, r1.rating as robustness, r2.rating as price_quality_ratio
  FROM products p
  JOIN rating r1 ON (r1.product_id = p.product_id AND r1.type = 'robustness')
  JOIN rating r2 ON (r2.product_id = p.product_id AND r2.type = 'price_quality_ratio')
) AS tmp
WHERE robustness >= 7
  AND price_quality_ratio >= 8
-- ORDER BY (price_quality_ratio DESC, robustness DESC) -- etc