SQL组合键查询

Question

再次引用这个SQL products/productsales

当主键由两列而不是一列组成时，我怎么能做类似的事情呢？

所以 products 有两列作为 PK，productsales 有两列作为 FK。

这是使用 1 列键的解决方案:

SELECT p.[name]
FROM products p
WHERE p.product_id in (SELECT s.product_id
    FROM productsales s
    WHERE s.[date] between @dateStart and @dateEnd
    GROUP BY s.product_id
    HAVING Sum(s.quantity) > @X )

Answer 1

沿着这些路线的东西可能会起作用

SELECT p.[name]
FROM products p
JOIN (SELECT s.key1, s.key2
    FROM productsales s
    WHERE s.[date] between @dateStart and @dateEnd
    GROUP BY s.product_id
    HAVING Sum(s.quantity) > @X ) as a on a.key1 = p.key1 and a.key2 = p.key2