MYSQL 计算 2 个表中的相关行

Question

我有3张 table

products table
productid   productname
---------   -----------
 1          product 1
 2          product 2
 5          product 3
 10         product 4
 11         product 5
 12         product 6

accounts_products table
id  productid   accountid
--  ---------   ---------
1   1           accountid 1
2   10          accountid 2
3   2           accountid3

leads_products table
id  productid    leadid
--  ---------    ---------
1   1            leadid 1
2   5            leadid 2
3   2            leadid 3

我正在尝试计算基于相同 productid 的 leads_products 和 accounts_products 表中的产品总数。

Expected result

Product ID    Product Name    Total
-----------   ------------    --------
1             product 1         2
2             product 2         2
5             product 3         1
10            product 4         1

到目前为止我已经尝试过了

SELECT p.productid as 'Product ID', 
       p.productname as 'Product Name', 
       COUNT(*) as 'Total' FROM products p
INNER JOIN leads_products l ON (l.productid=p.productid)
INNER JOIN accounts_products a ON (a.productid=p.productid)
GROUP BY p.productname,p.productid

以上查询计数并显示比预期更高的数字。

我希望这是有道理的。

Answer 1

试试这个:

SELECT p.productid as 'Product ID', 
       p.productname as 'Product Name', 
       (SELECT COUNT(*)
        FROM leads_products AS l
        WHERE l.productid = p.productid) + 
       (SELECT COUNT(*)
        FROM accounts_products AS a
        WHERE a.productid=p.productid) AS 'Total'
FROM products AS p

http://www.sqlfiddle.com/#!2/f8472/5

使用 JOIN 的替代方法(更好的性能):

SELECT p.productid as 'Product ID', 
       p.productname as 'Product Name', 
       IFNULL(l.count, 0) + IFNULL(a.count, 0) as 'Total'
FROM products AS p
LEFT JOIN (
    SELECT productid, COUNT(*) AS count
    FROM leads_products
    GROUP BY productid
) AS l
ON l.productid = p.productid
LEFT JOIN (
    SELECT productid, COUNT(*) AS count
    FROM accounts_products
    GROUP BY productid
) AS a
ON a.productid = p.productid

http://www.sqlfiddle.com/#!2/f8472/33