当前位置: 代码迷 >> Sql Server >> 求总余额值与明细的最好一笔记录的余额值是否相等的检验语句,该怎么处理
  详细解决方案

求总余额值与明细的最好一笔记录的余额值是否相等的检验语句,该怎么处理

热度:67   发布时间:2016-04-27 15:06:23.0
求总余额值与明细的最好一笔记录的余额值是否相等的检验语句
有如下两个表:

人员表:tbl_person

pid pmoney
---------------------------
1 200
2 -6
3 300


费用明细表:tbl_person_list

Lid Pid Lmoney Pmoney LDate
---------------------------------------
1   1   50     50  2008-2-11
2   2   50     50  2008-2-11
3   3   50     50  2008-2-11
4   1   50     100 2008-2-13
5   2   -56     -6  2008-2-13
6   3   100     150  2008-2-13
7   1   100     200  2008-2-14
8   3   150     300  2008-2-14




假定:
tbl_person人员的pmoney初始值为0



现在想写一个余额检验语句,即检查tbl_person每个人的余额值是否与tbl_person_list中对应人员最后一笔记录的余额值(Pmoney)相等,
把不相等的人员信息列出来




------解决方案--------------------
SQL code
create table tbl_person(pid int,pmoney money)insert into tbl_person values(1,200)insert into tbl_person values(2,0)insert into tbl_person values(3,300)create table tbl_person_list(lid int identity(1,1),pid int,lmoney money,pmoney money,ldate datetime)insert into tbl_person_list values(1,50,50,'2008-2-11')insert into tbl_person_list values(2,50,50,'2008-2-11')insert into tbl_person_list values(3,50,50,'2008-2-11')insert into tbl_person_list values(1,50,100,'2008-2-13')insert into tbl_person_list values(2,-56,-6,'2008-2-13')insert into tbl_person_list values(3,100,150,'2008-2-13')insert into tbl_person_list values(1,100,200,'2008-2-14')insert into tbl_person_list values(3,150,300,'2008-2-14')select a.* from tbl_person a inner join (select * from tbl_person_list a where not exists( select 1 from tbl_person_list where pid=a.pid and ldate>a.ldate))bon a.pid=b.pid and a.pmoney<>b.pmoneypid         pmoney----------- ---------------------2           0.00(1 行受影响)
  相关解决方案