我有一个表shop_inventory和另一个shops。我想计算shop_inventory中DISTINCT zbid的数量和shops中的行数，其中cid=1 AND zbid!=0。我试过了：
SELECT COUNT(a.cid) shops,COUNT(DISTINCT b.zbid) buyers
FROM shops a
JOIN shop_inventory b ON b.cid=a.cid
WHERE a.zbid!=0 AND a.cid=1
然而，这返回了100家店铺，而不是2家，这是正确的答案。我想我不明白JOIN是如何正确工作的。有人能提供此查询的修复程序吗？
许多相关行上的JOIN使您的店铺数量大增。
试试这个解决方案：
SELECT COUNT(DISTINCT a.cid, a.zbid) shops,
COUNT(DISTINCT b.zbid) buyers
FROM shops a
JOIN shop_inventory b ON a.cid = b.cid
WHERE a.cid = 1 AND a.zbid <> 0
如果这能让你得到你想要的商店数量：
SELECT COUNT(1) AS shops
FROM shops a
WHERE a.zbid != 0
AND a.cid = 1
然后，包括来自另一个表的计数的一种方法是使用相关的子查询。（这种方法存在一些性能问题，如果外部查询返回的行数有限，则效果良好。）
SELECT COUNT(1) AS shops
, ( SELECT COUNT(DISTINCT b.zbid)
FROM shop_inventory b
WHERE b.cid = a.cid
) AS buyers
FROM shops a
WHERE a.zbid != 0
AND a.cid = 1
另一种方法是使用子查询（内联视图）的联接。。。
SELECT COUNT(1) AS shops
, c.buyers AS buyers
FROM shops a
JOIN ( SELECT b.cid, COUNT(DISTINCT b.zbid) AS buyers
FROM shop_inventory b
WHERE b.cid = 1
GROUP BY b.cid
) c
WHERE a.zbid != 0
AND a.cid = 1
AND a.cid = c.cid
如果这些没有返回您要查找的结果集，那么我可能误解了规范。