在 SQL (MySQL) 中根据表 1 中的不同 ID 计算表 2 中的不同字段

Question

我有 2 个表：

表 1：

| order_id | shop_id | customer_id | total |  date    |
-------------------------------------------------------
|   9005   |    A    |      1      | 1205  | 20110210 |
|   9006   |    A    |      2      | 8591  | 20110212 |
|   9007   |    A    |      2      | 3472  | 20110216 |
|   9008   |    B    |      1      | 6310  | 20110218 |
-------------------------------------------------------

表 2：

| shop_id | customer_id | reference |
-------------------------------------
|    A    |      1      | Friend    |
|    A    |      2      | Internet  |
|    B    |      1      | Friend    |
|    C    |      1      | Friend    |
-------------------------------------

我想从 table1 中选择不同的值（shop_id、customer_id）（按日期过滤），然后对 table2 中的引用进行计数。

预期结果：

| reference | count |
---------------------
|  Friend   |   2   |
|  Internet |   1   |
----------------------

到目前为止，我使用的查询是：

SELECT reference,COUNT(*) as count FROM table1 JOIN table2 USING(shop_id,customer_id) GROUP BY reference

结果是：

| reference | count |
---------------------
|  Friend   |   2   |
|  Internet |   2   |
---------------------

问题是计数了 2 次：shop_id=A 和 customer_id=2。这就是为什么“互联网”被计算两次。

有人可以帮我找出问题所在吗？如果可能的话，我想在不使用子查询的情况下实现这一点（技术限制）。

谢谢。

SQL 转储：

CREATE TABLE `table1` (
   `order_id` int(11),
   `shop_id` char(1),
   `customer_id` int(11),
   `total` smallint(6),
   `date` date
);

INSERT INTO `table1` (`order_id`, `shop_id`, `customer_id`, `total`, `date`) VALUES 
('9005', 'A', '1', '1205', '2011-02-10'),
('9006', 'A', '2', '8591', '2011-02-12'),
('9007', 'A', '2', '3472', '2011-02-16'),
('9008', 'B', '1', '6310', '2011-02-18');

CREATE TABLE `table2` (
   `customer_id` int(11),
   `shop_id` char(1),
   `reference` enum('Friend','Internet')
);

INSERT INTO `table2` (`customer_id`, `shop_id`, `reference`) VALUES 
('1', 'A', 'Friend'),
('2', 'A', 'Internet'),
('1', 'B', 'Friend'),
('1', 'C', 'Friend');

Answer 1

我认为这可能对你有用 - 至少它返回预期的结果

SELECT reference,COUNT(distinct(concat(shop_id,'_',customer_id))) as count 
FROM table1 JOIN table2 USING(shop_id,customer_id) GROUP BY reference;

'_' 是为了避免混合 store_id 和 customer_id，你可能可以使用不同的哈希函数来生成唯一的商店/客户 ID

Answer 2

这是使用子查询的一种解决方案：

SELECT reference, COUNT(*) as count FROM (SELECT shop_id,customer_id,reference FROM table1 JOIN table2 USING(shop_id,customer_id) GROUP BY shop_id,customer_id) as filtered GROUP BY(reference)

但我宁愿不使用子查询...如果没有这样的替代方案，这将成为正确的答案。