select a.代码,a.类别ID,a.预警值,a.名称,b.数量 , (case when cast(b.数量 numeric(28,2) /a.预警值 <=0.4 then '高' when cast(b.数量 numeric(28,2) /a.预警值 >0.4 and cast(b.数量 numeric(28,2) /a.预警值<70 then '中' when cast(b.数量 numeric(28,2) /a.预警值 >0.7 then '低' end) as 等级 from 零件表 a left join 库存表 b on a.代码=b.代码 and a.类别ID=b.类别ID
谢谢CrazyFro,不过我后来也写出来了,是下面这样的。select a.代码,a.类别ID,a.预警值,a.名称,b.数量 , (case when (b.数量/a.预警值) is NULL then '高' when cast(b.数量/a.预警值 as numeric(28,2) ) <=0.4 then '高' when cast(b.数量/a.预警值 as numeric(28,2) ) >0.4 and cast(b.数量/a.预警值 as numeric(28,2) )<70 then '中' when cast(b.数量/a.预警值 as numeric(28,2) ) >0.7 then '低' end) as 等级 from 零件表 a left join 库存表 b on a.代码=b.代码 and a.类别ID=b.类别ID where (a.预警值>b.数量) or (not exists(select * from 库存表 where a.代码=库存表.代码 and a.类别ID=库存表.类别ID))
(case
when cast(b.数量 numeric(28,2) /a.预警值 <=0.4 then '高'
when cast(b.数量 numeric(28,2) /a.预警值 >0.4 and cast(b.数量 numeric(28,2) /a.预警值<70 then '中'
when cast(b.数量 numeric(28,2) /a.预警值 >0.7 then '低'
end) as 等级
from 零件表 a left join 库存表 b on a.代码=b.代码 and a.类别ID=b.类别ID
(case
when (b.数量/a.预警值) is NULL then '高'
when cast(b.数量/a.预警值 as numeric(28,2) ) <=0.4 then '高'
when cast(b.数量/a.预警值 as numeric(28,2) ) >0.4 and cast(b.数量/a.预警值 as numeric(28,2) )<70 then '中'
when cast(b.数量/a.预警值 as numeric(28,2) ) >0.7 then '低'
end) as 等级
from 零件表 a left join 库存表 b on a.代码=b.代码 and a.类别ID=b.类别ID
where (a.预警值>b.数量) or (not exists(select * from 库存表 where a.代码=库存表.代码 and a.类别ID=库存表.类别ID))