仅当in不同时才在group_concat中获取重复值

Question

我有两张桌子，首先是表产品：

id|category_id
--+-----------
1 | 12345
2 | 12345
3 | 12465

然后是一个表格活动：

id|prod_id|activity_type   |description
--+-------+----------------+-----------
1 | 1     | Initialization | blah
2 | 1     | Finalization   | foo
3 | 2     | Initialization | blah again
4 | 2     | Duplication    | bar
5 | 2     | Finalization   | foobar
6 | 3     | Initialization | blob
7 | 3     | Migration      | A to B
8 | 3     | Migration      | B to C
9 | 3     | Finalization   | fuh

现在，我想为每种类型的活动检索至少有一种活动的产品数量，以及产品类别的列表。这些类别将在此类别的每个产品的列表中重复。现在，我使用以下查询：

SELECT a.activity_type as Activity, COUNT(DISTINCT p.id) as Products,
CONVERT(GROUP_CONCAT(p.category SEPARATOR ',  ') USING utf8) AS Categories
FROM mydb.product p, mydb.activity a
WHERE p.id = a.prod_id
AND a.activity_type <> '' // To not count activities which haven't been correctly initialized
GROUP BY Categories
ORDER BY Products

现在我等待的是：

Activity       | Products | Categories
---------------+----------+--------------------
Initialization | 3        | 12345, 12345, 12465
Finalization   | 3        | 12345, 12345, 12465
Duplication    | 1        | 12345
Migration      | 1        | 12465

但是通过这个查询，我得到了用于迁移的值'12465, 12465'。我可以让列表中出现一个类别，只针对每个不同的产品ids，而不是针对一种类型的每个活动？

Answer 1

首先是activity_type和prod_id的分组，然后是activity_type的另一组：

SELECT 
    a.activity_type AS Activity, 
    COUNT(DISTINCT p.id) AS Products,
    CONVERT(GROUP_CONCAT(p.category_id SEPARATOR ',  ') USING utf8) 
      AS Categories
FROM 
    product AS p
  JOIN 
    ( SELECT activity_type
           , prod_id
      FROM activity 
      WHERE activity_type <> '' 
      GROUP BY activity_type
             , prod_id
    ) AS a
    ON p.id = a.prod_id
GROUP BY 
    activity_type
ORDER BY 
    Products DESC;

在中测试(谢谢@Mr.Radical)

您还可以安全地将上面的COUNT(DISTINCT p.id)替换为COUNT(*)，对于每种活动类型，只有不同的产品is (这在内部组by中得到了注意)。

Answer 2

好吧，我解决了。试试这个：

SELECT a.activity_type, COUNT(DISTINCT( p.id)) AS products, 
CONVERT(GROUP_CONCAT(DISTINCT (p.category_id) SEPARATOR ',  ') USING utf8) AS Categories 
FROM activity as a 
LEFT JOIN Product AS p ON p.id = a.prod_id
WHERE a.activity_type <> ''
GROUP BY a.activity_type
ORDER BY products DESC;

样本数据：

CREATE TABLE Product
    (`id` int, `category_id` int)
;

INSERT INTO Product
    (`id`, `category_id`)
VALUES
    (1, 12345),
    (2, 12345),
    (3, 12465)
;

CREATE TABLE Activity
    (`id` int, `prod_id` int, `activity_type` varchar(14), `description` varchar(10))
;

INSERT INTO Activity
    (`id`, `prod_id`, `activity_type`, `description`)
VALUES
    (1, 1, 'Initialization', 'blah'),
    (2, 1, 'Finalization', 'foo'),
    (3, 2, 'Initialization', 'blah again'),
    (4, 2, 'Duplication', 'bar'),
    (5, 2, 'Finalization', 'foobar'),
    (6, 3, 'Initialization', 'blob'),
    (7, 3, 'Migration', 'A to B'),
    (8, 3, 'Migration', 'B to C'),
    (9, 3, 'Finalization', 'fuh')
;

http://www.sqlfiddle.com/#!2/86dac/36