34,590
社区成员
发帖
与我相关
我的任务
分享
select a.ma_name, a.materiel_code,sum(a.number) as kc_number,safe_stock=(case b.safe_stock when b.safe_stock then b.safe_stock else (select safe_stock=case e.safe_stock when e.safe_stock then e.safe_stock else 0 end from all_materiel e where e.code=a.materiel_code) end) from kc_stock a,kc_materiel b where a.materiel_code=b.materiel_code and a.data_area=b.data_are group by a.materiel_code,a.ma_name,b.safe_stock
select t.* ,
库存 = case when t.number>=t.safe_stock then '安全庫存' else '庫存不夠 ' end
from (
select 'a' as ma_name, 001 as kc_number, 100 as number , 50 as safe_stock
union
select 'b', 002 , 50 , 80
union
select 'c',003 , 200 , 100
) t
ma_name kc_number number safe_stock 库存
------- ----------- ----------- ----------- ---------
a 1 100 50 安全庫存
b 2 50 80 庫存不夠
c 3 200 100 安全庫存
(3 個資料列受到影響)
select
a.ma_name,
a.materiel_code,
sum(a.number) as kc_number,
coalesce(b.safe_stock, e.safe_stock, 0) cal_safe_stock,
case when kc_number > cal_safe_stock then '安全库存' else '库存不够' end kc_status
from kc_stock a,kc_materiel b, all_materiel e
where a.materiel_code=b.materiel_code
and a.data_area=b.data_are
and e.code=a.materiel_code
group by a.materiel_code,a.ma_name,b.safe_stock
declare @tb table(库存 int,出库 int)
insert into @tb select 5,4
insert into @tb select 6,7
insert into @tb select 7,7
select *,case
when 库存>出库 then '库存足够'
when 库存<出库 then '库存不够'
else '库存为0' end as 库存
from @tb