日期:2014-05-18  浏览次数:20537 次

求总余额值与明细的最好一笔记录的余额值是否相等的检验语句
有如下两个表:

人员表: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))b
on a.pid=b.pid and a.pmoney<>b.pmoney

pid         pmoney
----------- ---------------------
2           0.00

(1 行受影响)