Z
zgjob
Unregistered / Unconfirmed
GUEST, unregistred user!
存储过程(从几万条记录中选择出匹配项约有几条.运行起来感觉速度较慢.)
CREATE PROCEDURE
[dbo].[yp]
@xmmc varchar(50)='%' AS
SELECT distinct a.id, a.xmmc, a.xmrj, a.gg, c.单位 AS dw,a.sjjg AS sjjg, c.上次产地 as cd,
round(SUM(c.实际数量),4) AS sl,
c.kid,c.kxz FROM dbo.库存表 c Inner Join dbo.药品目录 a
On c.药品ID = a.id and c.kxz=1 and c.kid=1
where
Convert(varchar(10),a.id)=@xmmc
or a.xmmc like '%'+@xmmc+'%' or a.xmrj like '%'+@xmmc+'%' or
a.tymc like '%'+@xmmc+'%' or a.tyjm like '%'+@xmmc+'%'
GROUP BY a.id, a.xmmc, a.xmrj, a.gg,c.单位,a.sjjg,
c.kid,c.kxz,c.上次产地 HAVING (SUM(c.实际数量) > 0)
GO
CREATE PROCEDURE
[dbo].[yp]
@xmmc varchar(50)='%' AS
SELECT distinct a.id, a.xmmc, a.xmrj, a.gg, c.单位 AS dw,a.sjjg AS sjjg, c.上次产地 as cd,
round(SUM(c.实际数量),4) AS sl,
c.kid,c.kxz FROM dbo.库存表 c Inner Join dbo.药品目录 a
On c.药品ID = a.id and c.kxz=1 and c.kid=1
where
Convert(varchar(10),a.id)=@xmmc
or a.xmmc like '%'+@xmmc+'%' or a.xmrj like '%'+@xmmc+'%' or
a.tymc like '%'+@xmmc+'%' or a.tyjm like '%'+@xmmc+'%'
GROUP BY a.id, a.xmmc, a.xmrj, a.gg,c.单位,a.sjjg,
c.kid,c.kxz,c.上次产地 HAVING (SUM(c.实际数量) > 0)
GO