我有samples, sample_products,products和.ordersorder_products
樣品 <=> 產品（通過sample_products）
訂購 <=> 產品（通過order_products）。
訂單和樣品之間沒有聯系。
現在我需要找到訂單上最常用的樣品。這尤其是一個問題，因為例如，如果我們有一個包含 2 種產品的包裝 - 產品 A 和產品 B，則訂單需要包含它們，而不僅僅是產品 A 或不僅是產品 B。訂單必須僅包含并且準確這兩種產品被視為樣品。
我嘗試像這樣加入他們，但這會導致我之前提到的問題 - 它不能確保訂單不包含其他產品或 A 和 B 都存在。
SELECT * FROM samples
INNER JOIN sample_products ON sample_products.sample_id = samples.id
INNER JOIN products ON products.id = sample_products.product_id
INNER JOIN order_products ON order_products.product_id = products.id
INNER JOIN orders ON orders.id = order_products.order_id
如果產品 A 和 B 都存在，我如何確保它只將表連接在一起？
uj5u.com熱心網友回復：
如果您想計算完整的樣本集（如果 sample1 由 A、B、C 組成 -> 對 A、B、C 進行計數），您可以使用產品數量進行連接：
SELECT order_sample_count.sample_id,
COUNT(order_sample_count.order_id) as sample_use
FROM
(
SELECT sample_id, count(product_id) as semple_numbers
FROM
(
SELECT DISTINCT sample_id, sample_products.product_id
FROM samples
INNER JOIN sample_products ON sample_products.sample_id = samples.id
) AS sample_distinct
GROUP BY sample_id
) AS semple_count
INNER JOIN
)
SELECT order_id, sample_id, count(product_id) as semple_numbers
FROM
(
SELECT DISTINCT order_products.order_id, sample_products.sample_id, order_products.product_id
FROM order_products
INNER JOIN sample_products ON sample_products.product_id = order_products.product_id
) AS order_sample_distinct
GROUP BY order_id, sample_id
) AS order_sample_count ON semple_count.sample_id = order_sample_count.sample_id
AND semple_count.semple_numbers = order_sample_count.semple_numbers
GROUP BY order_sample_count.sample_id
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/515064.html