mysql - 表间联接为关系和弦图

我有一个问题。对你来说可能很容易。
我试图建立一个d3js和弦图-类似这样的（http://bl.ocks.org/4062006）：
不过，我正在从mysql数据库中获取数据
我的桌子是这样的：

id gender_taker gender_giver
1     F            M
2     M            M
3     F            M
4     F            F

我希望输出像这样：

gender_giver gender_taker count(*)
M            F            2
M            M            1
F            F            1

这很容易，可以通过以下方法生产：

SELECT gender_giver, gender_taker, COUNT(*) FROM data WHEREclauses GROUP BY gender_taker, gender_giver

但我有另一个问题，我还有两张这样的桌子：
表1：

id entryid gender_taker
1   2       F
2   2       M
3   3       F

表2：

id entryid gender_giver
1   1       M
2   1       F
3   2       M

entryid基本上是第一个表的id，表明表2和表3只是表1的子集
如果你把这三张表组合起来，可能看起来像：

id gender_taker gender_giver
1     F            M,M,F
2     M,F,M        M,M
3     F,F          M
4     F            F

因此，作为和弦图的结果，我希望所有这些表最终都能考虑到如下内容：

gender_giver gender_taker count(*)
M            F            6
M            M            4
F            F            2
F            M            0

请帮帮我。

最佳答案

DROP TABLE IF EXISTS core;
CREATE TABLE core
(entry_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,gender_taker CHAR(1) NOT NULL
,gender_giver CHAR(1) NOT NULL
);

INSERT INTO core VALUES
(1     ,'F','M'),
(2,'M','M'),
(3,'F','M'),
(4,'F','F');

DROP TABLE IF EXISTS table1;
CREATE TABLE table1
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,entryid INT NOT NULL
,gender_taker CHAR(1) NOT NULL
);

INSERT INTO table1 VALUES
(1   ,2       ,'F'),
(2   ,2       ,'M'),
(3   ,3       ,'F');


DROP TABLE IF EXISTS table2;
CREATE TABLE table2
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,entryid INT NOT NULL
,gender_giver CHAR(1) NOT NULL
);

INSERT INTO table2 VALUES

(1   ,1       ,'M'),
(2   ,1       ,'F'),
(3   ,2       ,'M');

SELECT entry_id
     , GROUP_CONCAT(gender_taker) gender_takers
     , GROUP_CONCAT(gender_giver) gender_givers
  FROM
     ( SELECT * FROM core
       UNION
       SELECT entryid,gender_taker,NULL FROM table1
       UNION
       SELECT entryid,NULL,gender_giver FROM table2
     ) x
 GROUP
    BY entry_id;
+----------+---------------+---------------+
| entry_id | gender_takers | gender_givers |
+----------+---------------+---------------+
|        1 | F             | M,M,F         |
|        2 | M,F,M         | M,M           |
|        3 | F,F           | M             |
|        4 | F             | F             |
+----------+---------------+---------------+

SELECT a.gender taker
     , b.gender giver
     , COUNT(*)
  FROM
     (
       SELECT entry_id,'taker' role, gender_taker gender FROM core
       UNION ALL
       SELECT entry_id,'giver', gender_giver FROM core
       UNION ALL
       SELECT entryid,'taker',gender_taker FROM table1
       UNION ALL
       SELECT entryid,'giver',gender_giver FROM table2
     ) a
  JOIN
     (
       SELECT entry_id,'taker' role, gender_taker gender FROM core
       UNION ALL
       SELECT entry_id,'giver', gender_giver FROM core
       UNION ALL
       SELECT entryid,'taker',gender_taker FROM table1
       UNION ALL
       SELECT entryid,'giver',gender_giver FROM table2
     ) b
    ON b.entry_id = a.entry_id
   AND b.role = 'giver'
   AND a.role = 'taker'
 GROUP
    BY taker
     , giver;
+-------+-------+----------+
| taker | giver | COUNT(*) |
+-------+-------+----------+
| F     | F     |        2 |
| F     | M     |        6 |
| M     | M     |        4 |
+-------+-------+----------+

关于mysql - 表间联接为关系和弦图，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/14643016/