我正在计算购买品牌1、品牌2或品牌3的商店数量。但是pne商店计数一次。但在我的查询中，它计算每个品牌的总行数。有人能纠正我的疑问吗？
--- product ---
id pcode pname brand
1 123 Dalda 1
2 124 Habib 1
3 125 Sufi 2
4 126 Toyota 3
---------SALE-----------
id shcode shname pcode pname amount
1 1 A G/S 123 DALDA 1020
2 1 A G/S 124 HABIB 1030
3 2 B G/S 125 SUFI 1040
4 2 B G/S 123 DALDA 1020
5 2 B G/S 126 TOYOTA 1050
6 3 C G/S 123 DALDA 1020
7 4 D G/S 125 SUFI 1040
8 4 D G/S 123 DALDA 1020
9 4 D G/S 124 HABIB 1030
10 4 D G/S 126 TOYOTA 1050
11 5 E G/S 123 DALDA 1020
12 6 F G/S 125 SUFI 1040
13 7 G G/S 126 TOYOTA 1050
MY REQUIRED RESULT
BRAND Shops
1 5
2 3
3 3
我的查询
select p.brand, count(s.shcode) AS shops
FROM product p
INNER JOIN sdetail s on s.pcode = p.pcode
GROUP BY p.brand
SELECT tmp.brand, count(tmp.shcode) AS shops FROM (
SELECT DISTINCT temp.brand, (SELECT DISTINCT temp.shcode) AS shcode FROM (SELECT p.brand, s.shcode
FROM product p
INNER JOIN sdetail s on s.pcode = p.pcode) AS temp
) AS tmp GROUP BY tmp.brand
逻辑：将两个表连接起来，得到不同的商店品牌对，按品牌分组，得到商店数量。
我认为您需要的是count函数中的shcode。所以试试这个：
select p.brand, count(distinct(s.shcode)) AS shops
FROM product p
INNER JOIN sdetail s on s.pcode = p.pcode
GROUP BY p.brand
这是一把小提琴。。