如何根据表A中列的条件从不同的表B中获取列？

suppose i have two tables table A and table B

假设我有两个表A和表B.

Table A

表A.

id name remarks
 4  X    XXX
 6  Y    YYY
 7  Z    ZZZ

Table B

表B.

id Aid    remarks        edit_flag
 1   4     NULL            0
 2   6     YY changes      1
 3   7     Z cahnged       1

so, i would like to retrieve data like below:

所以，我想检索如下数据：

if edit_flag is 1 (that is edited), get remarks column from table B else(edit_flag is 0) get remarks column from table A since it isnt edited

如果edit_flag为1（即编辑），则从表B获取备注列else（edit_flag为0）从表A获取备注列，因为它未被编辑

i am looking at something like this

我在看这样的事情

if(edit_flag == 0) 
    then get remarks from table A 
else get remarks from table B

so my result table should be looking like

所以我的结果表应该看起来像

Row_Counter remarks 
1 XXX 
2 YY changes 
3 Z changed

3 个解决方案

#1

Use CASE:

使用案例：

SELECT aID = a.id, name,
       remarks = CASE b.edit_flag 
                    WHEN 0 THEN a.remarks
                    WHEN 1 THEN b.remarks
                 END
FROM TableA a INNER JOIN TableB b ON a.id = b.Aid

#2

You can join 2 tables and make a conditional query. Such as:

您可以连接2个表并进行条件查询。如：

NOTE: I'm assuming that you're using a database supporting "iif". Otherwise you should use "case" as in the other answers.

注意：我假设您正在使用支持“iif”的数据库。否则你应该像其他答案一样使用“case”。

Select a.id as id_id, iif(b.edit_flag = 0, a.remarks, b.remarks) as remark
  from tableA as a inner join tableB as b on a.id=b.Aid

#3

Use Case Statement

用例陈述

    declare @tblA as table
(
    id int,
    name varchar(50),
    remarks varchar(50)
)


insert into @tblA values(4,'x','xxx');
insert into @tblA values(6,'y','yyy');
insert into @tblA values(7,'z','zzz');

declare @tblB as table
(
    id int,
    Aid int,    
    remarks varchar(50),
    edit_flag int
)


insert into @tblB values(1,4,NULL,0);
insert into @tblB values(2,6,'yy changes',1);
insert into @tblB values(3,7,'z changes',1);

SELECT 
    B.id,
    B.Aid,
    B.edit_flag,    
    CASE WHEN edit_flag=1 THEN B.remarks ELSE a.remarks END as remarks
FROM @tblB B
LEFT JOIN @tblA A ON B.Aid=A.id

#1