如何获取在另一个表中定义的列值 [英] How to get column value which define in another table
本文介绍了如何获取在另一个表中定义的列值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
有人帮我追踪预期的输出吗?我还添加了数据库结构
Any one help me for following expected output? I have also added database structure
TABLE1
TABLE1
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
TABLE2
TABLE2
Id WorkId field1 field2 field3 field4 field5
1 1 JOHN 1
2 2 1 0 1
3 3 0
预期产量
Expected Output
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
表结构
Table Structure
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);
推荐答案
希望这对您有帮助
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
这篇关于如何获取在另一个表中定义的列值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文