php - 将两个 MySql 合并为一个查询

Question

我有两个 sql 查询，需要将它们合并为一个查询以提高效率，只是不知道该怎么做。基本上我需要查询我的供应商，然后对于每个供应商检查列表表是否有多个基于“供应商 ID”匹配的记录，如果有，那么我需要返回供应商名称和 ID。这是我的两个查询:

查询 1:

SELECT          s.name, s.id 
FROM            Supplier s

查询 2:

SELECT      l.asin, 
            l.id, 
            COUNT(*) c
FROM        Listing l
LEFT JOIN   Product p
 ON         p.id = l.product_id
LEFT JOIN   Supplier s
 ON         p.supplier_id = s.id 
WHERE       (l.matchValidated IS NULL OR l.matchValidated = 0)
AND         s.id = SUPPLIER_ID_GOES_HERE
GROUP BY    l.asin HAVING c > 1);

Answer 1

您已经在第二个查询中加入了供应商，因此您只需将供应商名称和 ID 添加到您的 SELECT 和 GROUP BY 中即可。

SELECT      l.asin, 
            l.id,
            s.name AS supplier_name,
            s.id AS supplier_id, 
            COUNT(*) c
FROM        Listing l
LEFT JOIN   Product p
 ON         p.id = l.product_id
LEFT JOIN   Supplier s
 ON         p.supplier_id = s.id 
WHERE       (l.matchValidated IS NULL OR l.matchValidated = 0)
GROUP BY    l.asin, l.id, s.name, s.id 
HAVING      c > 1;