时间戳

首页 > 解决方案 > 未找到数据时打印消息

sql - 未找到数据时打印消息

问题描述

需要查询来获取员工姓名，每个员工使用的总燃料。如果员工不使用燃料，则第二列应包含“未使用燃料”文本。

这些是以下两个表：

Table1: EmployeeID, FirstName

1 Vikas
2 nikita
3 Ashish
4 Nikhil
5 anish

Table2: ID, Fuel

1 10
2 9
3 8
4 6
5 12
6 11
7 10
8 9
9 8
10 10
11 9
12 12
13 7
14 15

其中列table2.ID是 的外键table1.EmployeeID。

这是我写的代码，很可能是错误的。

select ID, FirstName, sum(table2.Fuel) sum_fuel
from table2,table1
where EmployeeID=ID IN (
select ID, coalesce(ID, 'No-fuel used') as ID
from table1 t1
left join table2 t2 on t2.ID = t1.EmployeeID
)
group by fuel
order by ID DESC;

从两张表中可以看出，table1 的 1 到 5 的员工在 table2 中。因此，对于这些员工，我需要显示每个人使用的总燃料。对于 ID 为 6 到 14 的员工在表 1 中不可用，因此应为这些员工打印“未使用燃料”消息。

标签： sqloracle

解决方案

您可以使用left join. 这样，每当表的 Id 值不匹配时，您将获得 null 值，并使用以下函数为列sum(fuel)分配字符串 ：'No fuel used'sum_fuelnvl()

with table1( EmployeeID, FirstName ) as
(
 select 1,'Vikas'  from dual union all 
 select 2,'nikita' from dual union all 
 select 3,'Ashish' from dual union all 
 select 4,'Nikhil' from dual union all 
 select 5,'anish'  from dual union all 
 select 15,'pratteek'  from dual 
), table2( ID, Fuel ) as
(
 select 1, 10  from dual union all
 select 2, 9   from dual union all
 select 3, 8   from dual union all
 select 4, 6   from dual union all
 select 5, 12  from dual union all
 select 6, 11  from dual union all
 select 7, 10  from dual union all
 select 8, 9   from dual union all
 select 9, 8   from dual union all
 select 10, 10 from dual union all
 select 11, 9  from dual union all
 select 12, 12 from dual union all
 select 13, 7  from dual union all
 select 14, 15 from dual       
)
select EmployeeID, FirstName, nvl(to_char(sum(t2.Fuel)),'No fuel used') as sum_fuel 
  from table1 t1
  left join table2 t2 
    on t1.EmployeeID = t2.ID 
 group by EmployeeID, FirstName
 order by EmployeeID desc;

 EMPLOYEEID FIRSTNAME   SUM_FUEL
 ---------- ---------   ------------
 15         pratteek    No fuel used
  5         anish       12
  4         Nikhil      6
  3         Ashish      8
  2         nikita      9
  1         Vikas       10

Demo

推荐阅读