Mysql Query用于在多个条件和列上从一个表插入另一个表

I have a table named as test table in which I have total of 8 columns as such (Roll_no, Student_name, Company1, Pass_fail1, Company2, Pass_fail2, Company3, Pass_fail3). I have to insert the data from test table to another table (say Interview table). Columns of Interview table are (Roll_no, Student_name, Company1, Company2, Company3);

我有一个名为test table的表,其中我总共有8列(Roll_no,Student_name,Company1,Pass_fail1,Company2,Pass_fail2,Company3,Pass_fail3)。我必须将测试表中的数据插入另一个表(比如采访表)。访谈表的列是(Roll_no,Student_name,Company1,Company2,Company3);

The condition for insertion is:
If a student has passed the test of Company1 (i.e pass_fail1=1) then he is eligible for interview of Company1,and same conditions for remaining two companies. So, Company1 will be inserted in interview table only if pass_fail1=1.

插入的条件是:如果学生通过了公司1的考试(即pass_fail1 = 1),那么他有资格接受公司1的面试,其余两家公司的面试条件相同。因此,只有当pass_fail1 = 1时,才会将Company1插入到面试表中。

if pass_fail=0 then do not enter the name of the company.

如果pass_fail = 0则不输入公司名称。

1 个解决方案

#1

Try this:

INSERT INTO interviews (Rollno,Student_name,Company1,Company2,Company3)
SELECT Rollno, Student_name, 
      (CASE WHEN Pass_fail1 = 1 THEN Company1 ELSE '' END) Company1, 
      (CASE WHEN Pass_fail2 = 1 THEN Company2 ELSE '' END) Company2, 
      (CASE WHEN Pass_fail3 = 1 THEN Company3 ELSE '' END) Company3 
FROM test