mysql - 如何标准化sql查询的结果以及如何将外键引用到两列

Question

我有下表:

人表

 +-----------+-------------+------+-----+---------+-------+
    | Field     | Type        | Null | Key | Default | Extra |
    +-----------+-------------+------+-----+---------+-------+
    | person_id | int(100)    | NO   | PRI | NULL    |       |
    | name      | varchar(50) | NO   |     | NULL    |       |
    +-----------+-------------+------+-----+---------+-------+

    +-----------+---------------+
    | person_id | name          |
    +-----------+---------------+
    |         1 | linon jacob   |
    |         2 | andrew simons |
    |         3 | john random   |
    |         4 | kayne ran     |
    +-----------+---------------+


EDIT:

As per the below comment:

    create table person(person_id int(100) primary key not null, name varchar(50) not null);
    create table questions(QID int(50) primary key not null, questions varchar(100));
    create table custom_questions(CID int(20) not null, questions varchar(50) not null, PID int(20), primary key (CID), foreign key (PID) references person(person_id));
    create table feedback_system(surveyID int(20) not null, recepient varchar(50), questionID int(20) not null, submitter name varchar(50), response varchar(10), primary key (surveyID, questionID));

Answer 1

我相信您需要使用一些 LEFT JOIN，因为对于每个反馈行，您要么在 questions 中有问题，要么在 custom_questions 中有问题。

类似这样的事情:

SELECT IFNULL(q.questions, cq.questions) as 'question',
   f.recepient_name, 
   f.submitter_name,
   f.response
FROM feedback_system f
LEFT JOIN custom_questions cq
    ON cq.CID = f.questionID    
LEFT JOIN questions q
    ON q.QID = f.questionID 
WHERE f.surveyID = 1;