select rownum rn,a.*,case when vp_selldate is null then '出库' else '在库' end as bz from VW_VEHICLEPROPERTY a left join vehiclecurpos b on a.vp_mobile=b.vc_mobile where 1=1 and VP_UNIT in (429,528,468,1329)我想在求 出库 和 在库的个数 请SQL问怎么写?
调试欢乐多
谢谢大家帮忙
用一句实现 最后能加2个字段 直接使用
谢谢大家帮忙
用一句实现 最好能加2个字段 直接使用
select 逐個字段(除了bz,數量),case bz when '出库' then 數量 end as '出库',case bz when '在库' then 數量 end as '在库' From
(select rownum rn,a.*, 數量,case when vp_selldate is null then '出库' else '在库' end as bz
from VW_VEHICLEPROPERTY a left join vehiclecurpos b on a.vp_mobile=b.vc_mobile where 1=1 and VP_UNIT in (429,528,468,1329)) B
select id --(自己加分组字段)
出库 = sum(case when vp_selldate is null then 1 else 0 end),
入库 = sum(case when vp_selldate is not null then 1 else 0 end)
from VW_VEHICLEPROPERTY a left join vehiclecurpos b
on a.vp_mobile=b.vc_mobile where 1=1 and VP_UNIT in (429,528,468,1329)
group by id--oracle
select id --(自己加分组字段)
sum(case when vp_selldate is null then 1 else 0 end) 出库,
sum(case when vp_selldate is not null then 1 else 0 end) 入库
from VW_VEHICLEPROPERTY a left join vehiclecurpos b
on a.vp_mobile=b.vc_mobile where 1=1 and VP_UNIT in (429,528,468,1329)
group by id
select rownum rn,t.* from
(
select a.*
,case when vp_selldate is null then 1 end as 出库
,case when vp_selldate is not null then 1 end as '在库'
from VW_VEHICLEPROPERTY a left join vehiclecurpos b on a.vp_mobile=b.vc_mobile where 1=1 and VP_UNIT in (429,528,468,1329)
group by a.* --(根据你的分组需求列出分组项)
) t
(
select a.*
,sum(case when vp_selldate is null then 1 end) as 出库
,sum(case when vp_selldate is not null then 1 end) as '在库'
from VW_VEHICLEPROPERTY a left join vehiclecurpos b on a.vp_mobile=b.vc_mobile where 1=1 and VP_UNIT in (429,528,468,1329)
group by a.* --(根据你的分组需求列出分组项)
) t