一条求和语句!!!

[liyong00512]

一个表中有药品ID(ypid)、生产批号(scph)、结余数量(jcsl)、发生时间(fssj)
如下表：
ksid ypid scph fssj jcsl
0601 00000001 10000001 2003-07-01 08:20:56:5 800.0000
0601 00000001 10000001 2003-07-01 17:11:25:733 -1.0000
0601 00000001 10000001 2003-07-03 17:53:35:53 210.0000
0601 00000001 10000003 2003-07-01 18:39:16:606 59.0000
0601 00000001 10000003 2003-07-01 18:39:37:356 58.0000
0601 00000001 10000003 2003-07-01 18:53:04:95 57.0000
0601 00000001 10000003 2003-07-11 08:08:06:793 0
0601 00000001 10000004 2003-07-09 15:44:29:326 400.0000
0601 00000001 10000004 2003-07-11 08:08:06:793 380.0000
0601 00000001 10000004 2003-08-04 09:17:29:763 330.0000
0601 00000001 10000004 2003-08-11 09:14:30:466 310.0000
现要求如下：
找出每一个药品每一个批号的最后一次结余数，再求各结余数的总和.如上表最后的结果是：520

[saucer]

try


select ksid, ypid, sum(jcsl) from yourtable t1
where fssj = (select max(fssj) from yourtable t2 where t1.ksid=t2.ksid and t1.ypid=t2.ypid and t1.scph = t2.scph)
group by ksid, ypid

[pengdali]

select sum(jcsl) 结果 from 表名 t1 where fssj = (select max(fssj) from 表名 where ksid=t1.ksid and ypid=t1.ypid and scph = t1.scph)

[fengyun6]

比如你的表名叫:药品
select sum(a.jcsl) from (select top 1 fssj from 药品 group by ypid,scph)b

[fengyun6]

再发一遍:
select sum(a.jcsl) from (select top1 fssj from 药品 group by ypid,scph order by fssj desc)b

或者:
select sum(a.jcsl) 总和 from (select max(fssj) from 药品 group by ypid,scph)b

[fengyun6]

我又发错了,应该是:把b改为a就行了
select sum(a.jcsl) from (select top1 fssj from 药品 group by ypid,scph order by fssj desc)a

或者:
select sum(a.jcsl) 总和 from (select max(fssj) from 药品 group by ypid,scph)a

[nboys]

select sum(jcsl) 结果 from tableName a
where fssj = (select max(fssj) from tableName
where ksid=a.ksid and ypid=a.ypid and scph = a.scph)

[fengyun6]

我发的绝对对,我给你试验过了

[fengyun6]

还是大力和思归说的对啊