select *,
(县年初实有数量+县本年增加数量-县本年减少数量) as '县年末实有数量',
(县年初实有金额+县本年增加金额-县本年减少金额) as '县年末实有金额',
'' as '序号' from
(select tbg.fgid,tbg.ID,tbg.GName as '装备项目',tbg.Prickle as '计量单位',tbg.Re1 as '功能描述',tbg.GNo,
sum(ISNULL(tba1.Num,0)) as '县年初实有数量',
sum(ISNULL(tba1.Num,0)*ISNULL(tba1.Price,0)) as '县年初实有金额',
sum(ISNULL(tba2.Num,0)) as '县本年增加数量',
sum(ISNULL(tba2.Num,0)*ISNULL(tba2.Price,0)) as '县本年增加金额',
sum(ISNULL(tba3.Num,0)) as '县本年减少数量',
sum(ISNULL(tba3.Num,0)*ISNULL(tba3.Price,0)) as '县本年减少金额'
from TB_Group tbg
/*县年初实*/
left join TB_Asset tba1 ON (tba1.AssetTypeID=tbg.ID or (tba1.AssetTypeID1=tbg.ID and tba1.AssetTypeID1<>465 )) and tba1.AssetStatus=0 and tba1.units in (select ID from TB_OrgInfo where OrgNo like @Orgno+'%') and tba1.InputDate<=@StartTime and tba1.FundsFrom in (select ID from @tbTable) and tba1.DeptID in (select ID from TB_Department tbd where tbd.DptNo like @DeptNo+'%')
/*增加*/
left join TB_Asset tba2 ON (tba2.AssetTypeID=tbg.ID or (tba2.AssetTypeID1=tbg.ID and tba2.AssetTypeID1<>465 )) and tba2.AssetStatus in(0,7) and tba2.units in (select ID from TB_OrgInfo where OrgNo like @Orgno+'%') and tba2.InputDate>=@StartTime and tba2.InputDate<=@EndTime and tba2.FundsFrom in (select ID from @tbTable) and tba2.DeptID in (select ID from TB_Department tbd where tbd.DptNo like @DeptNo+'%')
/*减少*/
left join TB_Asset tba3 ON (tba3.AssetTypeID=tbg.ID or (tba3.AssetTypeID1=tbg.ID and tba3.AssetTypeID1<>465 )) and tba3.AssetStatus=7 and tba3.units in (select ID from TB_OrgInfo where OrgNo like @Orgno+'%') and tba3.InputDate>=@StartTime and tba3.InputDate<=@EndTime and tba3.FundsFrom in (select ID from @tbTable) and tba3.DeptID in (select ID from TB_Department tbd where tbd.DptNo like @DeptNo+'%')
where tbg.IsDelete=0 and GNo like ISNULL(@gno,'')+'%' and GNo<>'1#' and GNo<>'2#' and GNO not like '2#61#%' and tbg.ID<>465
group by tbg.fgid,tbg.ID,tbg.GName,tbg.Prickle,tbg.Re1,tbg.GNo) as c,f_Sort('0',0) b WHERE c.ID=b.ID ORDER BY b.sort如果left join on 里面没有or的话,查询出来的数据是没问题的,但是有or的时候就会出现问题,不知道要怎么解决
(县年初实有数量+县本年增加数量-县本年减少数量) as '县年末实有数量',
(县年初实有金额+县本年增加金额-县本年减少金额) as '县年末实有金额',
'' as '序号' from
(select tbg.fgid,tbg.ID,tbg.GName as '装备项目',tbg.Prickle as '计量单位',tbg.Re1 as '功能描述',tbg.GNo,
sum(ISNULL(tba1.Num,0)) as '县年初实有数量',
sum(ISNULL(tba1.Num,0)*ISNULL(tba1.Price,0)) as '县年初实有金额',
sum(ISNULL(tba2.Num,0)) as '县本年增加数量',
sum(ISNULL(tba2.Num,0)*ISNULL(tba2.Price,0)) as '县本年增加金额',
sum(ISNULL(tba3.Num,0)) as '县本年减少数量',
sum(ISNULL(tba3.Num,0)*ISNULL(tba3.Price,0)) as '县本年减少金额'
from TB_Group tbg
/*县年初实*/
left join TB_Asset tba1 ON (tba1.AssetTypeID=tbg.ID or (tba1.AssetTypeID1=tbg.ID and tba1.AssetTypeID1<>465 )) and tba1.AssetStatus=0 and tba1.units in (select ID from TB_OrgInfo where OrgNo like @Orgno+'%') and tba1.InputDate<=@StartTime and tba1.FundsFrom in (select ID from @tbTable) and tba1.DeptID in (select ID from TB_Department tbd where tbd.DptNo like @DeptNo+'%')
/*增加*/
left join TB_Asset tba2 ON (tba2.AssetTypeID=tbg.ID or (tba2.AssetTypeID1=tbg.ID and tba2.AssetTypeID1<>465 )) and tba2.AssetStatus in(0,7) and tba2.units in (select ID from TB_OrgInfo where OrgNo like @Orgno+'%') and tba2.InputDate>=@StartTime and tba2.InputDate<=@EndTime and tba2.FundsFrom in (select ID from @tbTable) and tba2.DeptID in (select ID from TB_Department tbd where tbd.DptNo like @DeptNo+'%')
/*减少*/
left join TB_Asset tba3 ON (tba3.AssetTypeID=tbg.ID or (tba3.AssetTypeID1=tbg.ID and tba3.AssetTypeID1<>465 )) and tba3.AssetStatus=7 and tba3.units in (select ID from TB_OrgInfo where OrgNo like @Orgno+'%') and tba3.InputDate>=@StartTime and tba3.InputDate<=@EndTime and tba3.FundsFrom in (select ID from @tbTable) and tba3.DeptID in (select ID from TB_Department tbd where tbd.DptNo like @DeptNo+'%')
where tbg.IsDelete=0 and GNo like ISNULL(@gno,'')+'%' and GNo<>'1#' and GNo<>'2#' and GNO not like '2#61#%' and tbg.ID<>465
group by tbg.fgid,tbg.ID,tbg.GName,tbg.Prickle,tbg.Re1,tbg.GNo) as c,f_Sort('0',0) b WHERE c.ID=b.ID ORDER BY b.sort如果left join on 里面没有or的话,查询出来的数据是没问题的,但是有or的时候就会出现问题,不知道要怎么解决
解决方案 »
- 帮忙修改一个SQL错误
- 求一个子查询返回多个列的效率语句
- 子查询与联接表是否有这样一个区别
- 这个查询怎么做?
- 显示某条件下的重复记录的SQL语句
- sql统计查询--------------------------------------------------
- 如何写查询
- 为什么会出现这种情况 [OLE/DB provider returned message: [Microsoft][ODBC Visual FoxPro Driver]File 'fpjemx.dbf' does not exist.]
- 请哪位给我提供一下Log Explorer这个工具,我在网上下载的不能用,过期了.有了就结帖.
- 求教一条数据更新语句?
- 又是同一个SQL执行速度/效率大不同的问题
- sql查询分析器 链接了远程服务器的数据库 如何备份到我本地电脑
跟tba1.AssetTypeID=tbg.ID有什么区别?
一个是AssetTypeID,一个是AssetTypeID1