关系chord图的Inter

Question

我有一个问题。这对你来说可能很容易。

我正在尝试构建一个类似这样的d3js chord图(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

条目id基本上是第一个表的id，这表明Table2和Table3只是table1的子集

如果您将这三个表组合在一起，它可能如下所示：

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

因此，对于chord图，我希望将所有这些表都考虑在内，最终给出如下内容：

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

请帮帮我。

Answer 1

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 |
+-------+-------+----------+