mysql - 所需的SQL语法

表格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中没有assigned，则应添加不可用

 **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

最佳答案

使用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;

关于mysql - 所需的SQL语法，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/45047792/