姓名 应交金额 实交金额
琵琶 150 150
琵琶 150 100
中止 130 130
中止 140 140
统计出两个人的收入总和就是实金额总和正常收入笔数,欠款总额欠款笔数
结应该是
收入金额 正常笔数 欠款总额 欠款笔数
琵琶 250 1 50 1
中止 270 2 0 0
解决方案 »
- .NET Framework 4.0 中怎么不能引用 System.Web.Security 啊?
- 日历控件使用问题求助
- 为什么我显示出来的验证码和存到session里的不同步啊?网上查了很久也没解决,求高手解惑。。
- ASP.net可否指定某个URL转到特定页面?
- 怎么合并两个doc文件的内容?
- DataGrid编辑问题,老是不能更新。更改了编辑框中的值更新不了数据库。。。怎么办?
- 怎样设置excel导出后不能修改
- 高手们帮帮忙:关于颜色渐变的问题
- ie登陆网站后台显示验证码失效
- 创建web应用程序出错.
- excel.ActiveWorkbook.SaveAs()的13个参数该怎么弄啊?
- 如何在.net中从隐藏的倥件中取值啊,很急,谢谢.
CREATE PROCEDURE StatBill
@recRegtime datetime,
@serID varchar(4)
AS
SELECT dbo.user_info.id, dbo.user_info.userName, lin.conRealMoney, lin.sumRealMoney,d.sumArrearage, d.countArrearage
FROM dbo.user_info LEFT OUTER JOIN
(select b.empID,sum(b.realMoney)as sumRealMoney,count(b.realMoney) as conRealMoney
from
(select * from finReceipt as a
where a.serID = @serID and (a.recRegtime >@recRegtime) AND (a.recRegtime <dateadd(day,1,@recRegtime)))as b
group by b.empID) as lin on
user_info.id=lin.empID left outer join
(select SUM(a.dueMoney)-SUM(a.realMoney) as sumArrearage ,count(*) as countArrearage ,a.empID FROM dbo.finReceipt as a LEFT OUTER JOIN
dbo.svrInfo b ON a.serID = b.id where a.dueMoney > a.realMoney GROUP BY a.empID ) as d
on user_info.id= d.empID
GO
FROM dbo.user_info LEFT OUTER JOIN
(select b.empID,sum(b.realMoney)as sumRealMoney,count(b.realMoney) as conRealMoney,
sum((case when (realMoney-duemoney)<0 then 1 else 0 end)) as countArrearage,sum(duemoney-realMoney) as sumArrearage
from
(select * from finReceipt as a
where a.serID = '1119' and (a.recRegtime > '2008-02-15') AND (a.recRegtime <dateadd(day,1,'2008-02-15')))as b
group by b.empID) as lin on user_info.id=lin.empID