如果两个表中的两个值相同，如何添加新列 [英] how to add new column if two values are same in two tables

问题描述

学生表如下



姓名Rollno主题标记



A 1 ENG 50
B 2 TAM 60





报告表如下



Rollno分数



1 50

2 60



in如果匹配标记列，则上面两个表。然后添加名为result的新列。



从学生s中选择s.name，s.rollno，s.subject，报告r其中s.Rollno = r.rollno和s.marks = r.marks



当我运行上述查询时，输出如下



姓名Rollno主题标记



A 1 ENG 50

B 2 TAM 60





但是我希望输出如下



名字Rollno主题标记结果



A 1 ENG 50通票

B 2 TAM 60通票



来自上述查询如何

Student table as follows

Name Rollno subject marks

A 1 ENG 50
B 2 TAM 60


Report table as follows

Rollno marks

1 50
2 60

in the above two table if the marks column is matched. then add the new column called result.

select s.name,s.rollno,s.subject from student s,Report r where s.Rollno=r.rollno and s.marks=r.marks

when i run the above query, output as follows

Name Rollno Subject Marks

A 1 ENG 50
B 2 TAM 60


But i want the output as follows

Name Rollno Subject Marks Result

A 1 ENG 50 Pass
B 2 TAM 60 Pass

from the above query how

推荐答案

试试这个

try this

select s.name,s.rollno,s.subject,case when s.marks<30 then 'PASS' else 'FAIL' End from student s,Report r where s.Rollno=r.rollno and s.marks=r.marks

假设50以上是'PASS'，否则'FAIL'：

Assuming 50 and above is 'PASS' else 'FAIL':

select name, rollno, subject, marks,
case when
   marks >=50 then 'PASS'
else
   'FAIL'
end
from student s where exists
(
select * from report r
  where r.rollno = s.rollno and r.marks = s.marks
)

这篇关于如果两个表中的两个值相同，如何添加新列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！