必需的 SQL 语法

Question

表 1

**asset_tag         assigned_to     serial              model_number**            
AST-L1516-0127             NULL     NDD513738D          HP 240                  
AST-D1112-0205                1     ddafadf             HP 240                  
AST-D1213-0202             NULL     L52ha13             Lenovo thinkcenter M72e 
AST-D1516-0203                2     FGBH622             Dell Optiplex 3020      
AST-L1516-0077             NULL     3835LY32            Dell LATITUDE 3450      
AST-L1415-0002             NULL     CNFGH95LZJ          HP 240G3                

和表2

id   username 
1     pavan
2     kalyan

根据上述两个表获取下表所需的命令， 表1中的assigned_to = 表2中的ID，如果分配在表2中不可用，则应添加不可用

**asset_tag         assigned_to     serial              model_number                username** 
AST-L1516-0127             NULL     NDD513738D          HP 240                     Not Available
AST-D1112-0205                1     ddafadf             HP 240                     pavan
AST-D1213-0202             NULL     L52ha13             Lenovo thinkcenter M72e    Not Available
AST-D1516-0203                2     FGBH622             Dell Optiplex 3020         kalyan
AST-L1516-0077             NULL     3835LY32            Dell LATITUDE 3450         Not Available
AST-L1415-0002             NULL     CNFGH95LZJ          HP 240G3                   Not Available

Answer 1

使用 coalesce(( 函数

select a.asset_tag, a.assigned_to,a.serial,a.model_number,coalesce(b.username,'Not Available') as username
from table1 a left join table2 b on a.assigned_to = b.id;