mysql - 多列分组依据

Question

我在一个表 (A) 中有一种内容，在另外两个表 (C1,C2) 中有它的类别来自不同表(MC)的category_id。

我想计算特定类别中呈现的内容数量。

实时数据示例:

表A

main_key (unique)    stuff_id (non unique)
   1                    7
   2                    6 
   3                    3

表C1

category_id main_key (it is FK for A table)
   1             1
   1             2
   3             1

表C2

category_id main_key (it is FK for A table)
   2             3
   2             1

表MC

category_id category_name
   1             blablbl
   2               asas
   3             asasa
...

之间的关系:C1 和 A 为多对一C2 和 A 为多对一C1或C2与MC多对一

在当前示例中，我希望看到最终结果为

stuff_qnt        category_id        category_name
2                   1                  blablbl
2                   2                   asas
1                   3                   asasa

如何通过一个查询来实现它？

我的查询是:

SELECT count(A.stuff_id) as stuff_qnt, MC.category_id, MC.category_name 
FROM A
LEFT JOIN C1 using(main_key)
LEFT JOIN C2 using(main_key)
LEFT JOIN MC ON (C1.category_id = MC.category_id AND C2.category_id = MC.category_id)
GROUP BY C1.category_id, C2.category_id

但是它显示了错误的结果，我做错了什么？

Answer 1

SELECT COUNT(*) AS Stuff_Qnt, C.Category_ID, MC.Category_Name
  FROM MC
  JOIN (SELECT C1.Category_ID
          FROM A
          JOIN C1 ON A.Main_Key = C1.Main_Key
        UNION ALL
        SELECT C2.Category_ID
          FROM A
          JOIN C2 ON A.Main_Key = C2.Main_Key
       ) AS C
    ON C.Category_ID = MC.Category_ID
 GROUP BY C.Category_ID, MC.Category_Name
 ORDER BY C.Category_ID, Stuff_Qnt;

您需要将 A 与 C1 连接的类别以及将 A 与 C2 连接的类别，并且您绝对不想要使用 LEFT OUTER JOIN 获得的笛卡尔积，因此您采用列表的 UNION的类别ID，然后聚合并加入MC。

CREATE TABLE A
(
    main_key INTEGER NOT NULL PRIMARY KEY,
    stuff_id INTEGER NOT NULL
);
INSERT INTO A VALUES(1, 7);
INSERT INTO A VALUES(2, 6);
INSERT INTO A VALUES(3, 3);

CREATE TABLE MC
(
    category_id INTEGER NOT NULL PRIMARY KEY,
    category_name VARCHAR(10) NOT NULL
);
INSERT INTO mc VALUES(1, "blablbl");
INSERT INTO mc VALUES(2, "asas");
INSERT INTO mc VALUES(3, "asasa");

CREATE TABLE C1
(
    category_id INTEGER NOT NULL REFERENCES mc,
    main_key INTEGER NOT NULL REFERENCES a
);
INSERT INTO c1 VALUES(1, 1);
INSERT INTO c1 VALUES(1, 2);
INSERT INTO c1 VALUES(3, 1);

CREATE TABLE C2
(
    category_id INTEGER NOT NULL REFERENCES mc,
    main_key INTEGER NOT NULL REFERENCES a
);
INSERT INTO c2 VALUES(2, 3);
INSERT INTO c2 VALUES(2, 1);

SELECT COUNT(*) AS Stuff_Qnt, C.Category_ID, MC.Category_Name
  FROM MC
  JOIN (SELECT C1.Category_ID
          FROM A
          JOIN C1 ON A.Main_Key = C1.Main_Key
        UNION ALL
        SELECT C2.Category_ID
          FROM A
          JOIN C2 ON A.Main_Key = C2.Main_Key
       ) AS C
    ON C.Category_ID = MC.Category_ID
 GROUP BY C.Category_ID, MC.Category_Name
 ORDER BY C.Category_ID, Stuff_Qnt;

输出:

2   1   blablbl
2   2   asas
1   3   asasa

<小时/>

这是一个进一步的测试，在 A 中添加了两行，在 C1 和 C2 中使用了相应的行。有两个查询被测试，我的和 query通过 dkkumargoyal .

CREATE TABLE A(main_key INTEGER NOT NULL PRIMARY KEY, stuff_id INTEGER NOT NULL);
INSERT INTO A VALUES(1, 7);
INSERT INTO A VALUES(2, 6);
INSERT INTO A VALUES(3, 3);
INSERT INTO A VALUES(4, 3);
INSERT INTO A VALUES(5, 3);

CREATE TABLE MC(category_id INTEGER NOT NULL PRIMARY KEY, category_name VARCHAR(10) NOT NULL);
INSERT INTO mc VALUES(1, "blablbl");
INSERT INTO mc VALUES(2, "asas");
INSERT INTO mc VALUES(3, "asasa");

CREATE TABLE C1(category_id INTEGER NOT NULL REFERENCES mc, main_key INTEGER NOT NULL REFERENCES a);
INSERT INTO c1 VALUES(1, 1);
INSERT INTO c1 VALUES(1, 2);
INSERT INTO c1 VALUES(3, 1);
INSERT INTO c1 VALUES(3, 4);
INSERT INTO c1 VALUES(1, 4);
INSERT INTO c1 VALUES(1, 5);

CREATE TABLE C2(category_id INTEGER NOT NULL REFERENCES mc, main_key INTEGER NOT NULL REFERENCES a);

INSERT INTO c2 VALUES(2, 3);
INSERT INTO c2 VALUES(2, 1);
INSERT INTO c2 VALUES(2, 5);

SELECT COUNT(*) AS Stuff_Qnt, C.Category_ID, MC.Category_Name
  FROM MC
  JOIN (SELECT C1.Category_ID
          FROM A
          JOIN C1 ON A.Main_Key = C1.Main_Key
        UNION ALL
        SELECT C2.Category_ID
          FROM A
          JOIN C2 ON A.Main_Key = C2.Main_Key
       ) AS C
    ON C.Category_ID = MC.Category_ID
 GROUP BY C.Category_ID, MC.Category_Name
 ORDER BY C.Category_ID, Stuff_Qnt;

-- Query by dkkumargoyal
SELECT COUNT(DISTINCT A.stuff_id) AS stuff_qnt, MC.category_id, MC.category_name 
FROM A
LEFT JOIN C1 on a.main_key = c1.main_key  -- USING(main_key)
LEFT JOIN C2 on a.main_key = c2.main_key  -- USING(main_key)
LEFT JOIN MC ON (C1.category_id = MC.category_id OR C2.category_id = MC.category_id)
GROUP BY MC.category_id, mc.category_name
ORDER BY MC.Category_id, stuff_qnt;       -- stuff_qnt added for standard compatibility

所做的更改对于代码在测试 DBMS (Informix 11.70.FC6) 上运行是必要的。

结果1:

4   1   blablbl
3   2   asas
2   3   asasa

结果 2:

3   1   blablbl
2   2   asas
2   3   asasa

我认为我的结果是正确的，而另一个则不是，主要是因为当问题规定它不唯一时，替代方案取决于 A.Stuff_ID 是唯一的(并且附加的数据行使其成为唯一的)非唯一)。