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
我可以得到如下的列表:
a 001 100 50
b 002 50 80
c 003 200 100
我想是不是有如下的写法:
a 001 100 50 安全库存
b 002 50 80 库存不够
c 003 200 100 安全库存
也就是我想比较sum(a.number)与b.safe_stock的数量,如果sum(a.number)>b.safe_stock显示安全库存,反之显示库存不够!请问这个如何来写啊?请朋友们帮助!谢谢!
case when sum(a.number)> b.safe_stock then '安全库存' else '库存不够' end as 库存,
列名,列名 from 表
insert into @tb select 5,4
insert into @tb select 6,7
insert into @tb select 7,7select *,case
when 库存>出库 then '库存足够'
when 库存<出库 then '库存不够'
else '库存为0' end as 库存
from @tb库存 出库 库存
5 4 库存足够
6 7 库存不够
7 7 库存为0
from
(
select ...
) a
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
库存 = 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 個資料列受到影響)