mysql - 如何获取另一个表中定义的列值

Question

有人可以帮助我实现预期的输出吗？我还添加了数据库结构

表1

ID  Work Id Question                     Column  Column Type
1   1   What is your name?       field1   String
2   1   is you have mobile number?   field2   boolean
3   2   is you have passport?        field2   boolean
4   2   are you indian?              field4   boolean
5   2   abc?                         field5   Number
6   3   cde?                         field2   boolean

表2

Id  WorkId  field1  field2  field3  field4  field5
1   1   JOHN      1         
2   2         1       0   1
3   3         0         

预期输出

Work Id Question                   Answer
1   What is your name?          JOHN
1   is you have mobile number?  1
2   is you have passport?           1
2   are you indian?                 0
2   abc?                            1
3   cde?                            0

表格结构

CREATE DATABASE /*!32312 IF NOT EXISTS*/`testtest` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `testtest`;

/*Table structure for table `table1` */

DROP TABLE IF EXISTS `table1`;

CREATE TABLE `table1` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `WorkId` int(11) DEFAULT NULL,
  `Question` varchar(100) DEFAULT NULL,
  `ColumnRef` varchar(100) DEFAULT NULL,
  `ColumnType` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

/*Data for the table `table1` */

insert  into `table1`(`Id`,`WorkId`,`Question`,`ColumnRef`,`ColumnType`) values (1,1,'What is your name?','field1','String'),(2,1,'is you have mobile number?','field2','boolean'),(3,2,'is you have passport?','field2','boolean'),(4,2,'are you indian?','field4','boolean'),(5,2,'abc?','field5','Number'),(6,3,'CDE?','field2','boolean');

/*Table structure for table `table2` */

DROP TABLE IF EXISTS `table2`;

CREATE TABLE `table2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `workid` int(11) DEFAULT NULL,
  `field1` varchar(11) DEFAULT NULL,
  `field2` int(11) DEFAULT NULL,
  `field3` int(11) DEFAULT NULL,
  `field4` int(11) DEFAULT NULL,
  `field5` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*Data for the table `table2` */

insert  into `table2`(`id`,`workid`,`field1`,`field2`,`field3`,`field4`,`field5`) values (1,1,'JOHN',1,NULL,NULL,NULL),(2,2,NULL,1,NULL,0,1),(3,3,NULL,1,NULL,NULL,NULL);

Answer 1

希望这对您有帮助

SELECT t1.workid AS 'workid',t1.Question AS 'Question',
COALESCE(`field1`,`field2`,`field3`,`field4`,`field5`) AS 'Answer'
FROM table1 AS t1
JOIN table2 AS t2 ON t1.workid = t2.workid

SQL Fiddle