Cacher is the code snippet organizer for pro developers

--统计出入库2表库存数
create view VStock
as
--查询有出库
select 药品编号=a.yzno,药品名称=a.Yname,单价=a.pr,库存数量=a.库存数量-b.销售数量 from 
(select yzno,Yname,pr,库存数量=sum(cont) from dbo.Ypinput group by yzno,Yname,pr,cont) a,
(select yzno,Yname,pr,销售数量=sum(cont) from dbo.Ypoutput group by yzno,Yname,pr,cont) b 
where a.yzno=b.yzno and a.Yname=b.Yname
union 
--查询无出库
select 药品编号=yzno,药品名称=Yname,单价=pr,库存数量=cont from dbo.Ypinput where
Yname+yzno not in(select Yname+yzno from dbo.Ypoutput)
GO
select * from Vstock where 库存数量>0 

--表中同类型相减
--查询无出库
select 产品名称=pname,单价=prc,库存数量=ct from input where flag=1 and 
pname+convert(varchar(25),prc) not in(select pname+convert(varchar(25),prc) from input where flag=0)
union 
--查询有出库
select 产品名称=a.pname,单价=a.prc,库存数量=a.库存数量-b.库存数量 from 
(select pname,ct,库存数量=sum(ct),prc from dbo.input where flag=1 group by pname,prc,ct) a,
(select pname,ct,库存数量=sum(ct),prc from dbo.input where flag=0 group by pname,prc,ct) b 
where a.prc=b.prc and a.pname=b.pname
--查询起始0点至结束23:59
sql = string.Format("Select 入库单位,产品名称,规格,单位,数量,单价,合计=(数量*单价),时间,备注,操作人 from vinput where 时间 between '{0}'  and '{1}' and  标志=1", dateTimePicker1.Value.ToShortDateString() + " 00:00", dateTimePicker2.Value.ToShortDateString() + " 23:59");

SELECT ROW_NUMBER() OVER(ORDER BY 排序列名 ASC|DESC) AS 自增列, * FROM 表名 --查询或视图添加自增ID