不知道视图是不是可以传参进去然后在查询,
比如说我现在有一个视图,但他查询出来的结果不是我想要的, 可不可以从视图外面传入一个条件 到视图里面,然后再根据这个查询条件 得到我所要的答案呢?CREATE VIEW View_ItemCount as
SELECT a.ID,a.Pid,b.AppDate,
isnull((select sum(OKNum) from tblItemApp WHERE ItemID=a.ID and year(AppDate)=year(getDate()) and month(AppDate)=month(getDate())),0) as MonthNum,
isnull((select sum(OKFunds) from tblItemApp WHERE ItemID=a.ID and year(b.AppDate)=year(getDate()) and month(AppDate)=month(getDate())),0) as MonthFunds,
isnull((select sum(OKNum) from tblItemApp WHERE ItemID=a.ID and month(b.AppDate)=month(getDate())),0) as YearNum,
isnull((select sum(OKFunds) from tblItemApp WHERE ItemID=a.ID and month(b.AppDate)=month(getDate())),0) as YearFunds
FROM tblItem a
left join tblItemApp b on a.ID = b.ItemID
---------------------------------------------------------------------------------------------------------
下面的查询要用到上面的视图View_ItemCount 想把getDate()改成一个参数,从程序中传入值 不知道该怎么做?
---------------------------------------------------------------------------------------------------------
SELECT a.ID,a.Pid,a.Name as ItemName,
isnull((select sum(MonthNum) from View_ItemCount WHERE Pid=a.ID ),0) as MonthNum,
isnull((select sum(MonthFunds) from View_ItemCount WHERE Pid=a.ID),0) as MonthFunds,
isnull((select sum(YearNum) from View_ItemCount WHERE Pid=a.ID ),0) as YearNum,
isnull((select sum(YearFunds) from View_ItemCount WHERE Pid=a.ID)),0) as YearFunds
FROM tblItem a
where a.Pid is null
比如说我现在有一个视图,但他查询出来的结果不是我想要的, 可不可以从视图外面传入一个条件 到视图里面,然后再根据这个查询条件 得到我所要的答案呢?CREATE VIEW View_ItemCount as
SELECT a.ID,a.Pid,b.AppDate,
isnull((select sum(OKNum) from tblItemApp WHERE ItemID=a.ID and year(AppDate)=year(getDate()) and month(AppDate)=month(getDate())),0) as MonthNum,
isnull((select sum(OKFunds) from tblItemApp WHERE ItemID=a.ID and year(b.AppDate)=year(getDate()) and month(AppDate)=month(getDate())),0) as MonthFunds,
isnull((select sum(OKNum) from tblItemApp WHERE ItemID=a.ID and month(b.AppDate)=month(getDate())),0) as YearNum,
isnull((select sum(OKFunds) from tblItemApp WHERE ItemID=a.ID and month(b.AppDate)=month(getDate())),0) as YearFunds
FROM tblItem a
left join tblItemApp b on a.ID = b.ItemID
---------------------------------------------------------------------------------------------------------
下面的查询要用到上面的视图View_ItemCount 想把getDate()改成一个参数,从程序中传入值 不知道该怎么做?
---------------------------------------------------------------------------------------------------------
SELECT a.ID,a.Pid,a.Name as ItemName,
isnull((select sum(MonthNum) from View_ItemCount WHERE Pid=a.ID ),0) as MonthNum,
isnull((select sum(MonthFunds) from View_ItemCount WHERE Pid=a.ID),0) as MonthFunds,
isnull((select sum(YearNum) from View_ItemCount WHERE Pid=a.ID ),0) as YearNum,
isnull((select sum(YearFunds) from View_ItemCount WHERE Pid=a.ID)),0) as YearFunds
FROM tblItem a
where a.Pid is null
在利用视图查询时,可以使用参数,如上面的@ID
这个方法行不通,我已经试过了
因为b.AppDate这个值,在Pid为空时是没有值的