一个查询问题

Question

select a.CKBH , a.WPBH, sum(QMSL) from kc a where RQ = (select max(RQ) from kc where CKBH = a.CKBH and WPBH = a.WPBH)
group by a.CKBH , a.WPBH

Answer 1

create table tb(CKBH varchar(10),XSDDH varchar(10),WPBH varchar(10),RQ datetime,QMSL int)
insert into tb values('CK1','XS001','WP001',             '2007-12-5',         100) 
insert into tb values('CK2','XS001','WP001',             '2007-12-5',         100) 
insert into tb values('CK1','XS002','WP002',             '2007-12-5',         100) 
insert into tb values('CK2','XS002','WP002',             '2007-12-5',         100) 
insert into tb values('CK1','XS003','WP001',             '2007-12-2',         30 )
insert into tb values('CK2','XS003','WP001',             '2007-12-2',         40 )
insert into tb values('CK1','XS004','WP002',             '2007-12-2',         50 )
insert into tb values('CK2','XS004','WP002',             '2007-12-2',         60 )
insert into tb values('CK1','XS001','WP001',             '2007-12-3',         10 )
insert into tb values('CK2','XS001','WP001',             '2007-12-3',         10 )
insert into tb values('CK1','XS002','WP002',             '2007-12-3',         10 )            
insert into tb values('CK2','XS002','WP002',             '2007-12-3',         10 )
insert into tb values('CK1','XS003','WP001',             '2007-12-1',         20 )
insert into tb values('CK2','XS003','WP001',             '2007-12-1',         20 )
insert into tb values('CK1','XS004','WP002',             '2007-12-1',         20 )
insert into tb values('CK2','XS004','WP002',             '2007-12-1',         20 )
goselect a.CKBH , a.WPBH, QMSL = sum(QMSL) from tb a where RQ = (select max(RQ) from tb where CKBH = a.CKBH and WPBH = a.WPBH) group by a.CKBH , a.WPBH order by a.WPBH,a.CKBHdrop table tb/*
CKBH       WPBH       QMSL        
---------- ---------- ----------- 
CK1        WP001      100
CK2        WP001      100
CK1        WP002      100
CK2        WP002      100（所影响的行数为 4 行）
*/

Answer 2

select CKBH,WPBH,sum(QMSL)
from table a
where not exists(select 1 from table where a.CKBH =CKBH and a.WPBH =WPBH and a.RQ<RQ)
group by CKBH,WPBH

Answer 3

--怎么和你的结果有点出入,是我理解错了?
create table tb(CKBH varchar(10),XSDDH varchar(10),WPBH varchar(10),RQ datetime,QMSL int)
insert into tb values('CK1','XS001','WP001',             '2007-12-5',         100) 
insert into tb values('CK2','XS001','WP001',             '2007-12-5',         100) 
insert into tb values('CK1','XS002','WP002',             '2007-12-5',         100) 
insert into tb values('CK2','XS002','WP002',             '2007-12-5',         100) 
insert into tb values('CK1','XS003','WP001',             '2007-12-2',         30 )
insert into tb values('CK2','XS003','WP001',             '2007-12-2',         40 )
insert into tb values('CK1','XS004','WP002',             '2007-12-2',         50 )
insert into tb values('CK2','XS004','WP002',             '2007-12-2',         60 )
insert into tb values('CK1','XS001','WP001',             '2007-12-3',         10 )
insert into tb values('CK2','XS001','WP001',             '2007-12-3',         10 )
insert into tb values('CK1','XS002','WP002',             '2007-12-3',         10 )            
insert into tb values('CK2','XS002','WP002',             '2007-12-3',         10 )
insert into tb values('CK1','XS003','WP001',             '2007-12-1',         20 )
insert into tb values('CK2','XS003','WP001',             '2007-12-1',         20 )
insert into tb values('CK1','XS004','WP002',             '2007-12-1',         20 )
insert into tb values('CK2','XS004','WP002',             '2007-12-1',         20 )
goselect a.CKBH , a.WPBH, QMSL = sum(a.QMSL) from tb a where RQ = (select max(RQ) from tb where CKBH = a.CKBH and WPBH = a.WPBH) group by a.CKBH , a.WPBH order by a.CKBH , a.WPBHdrop table tb/*
CKBH       WPBH       QMSL        
---------- ---------- ----------- 
CK1        WP001      100
CK1        WP002      100
CK2        WP001      100
CK2        WP002      100（所影响的行数为 4 行）
*/

Answer 4

--经过测试，发现我和石头的结果一样。应该是楼主错了。
create table tb(CKBH varchar(10),XSDDH varchar(10),WPBH varchar(10),RQ datetime,QMSL int)
insert into tb values('CK1','XS001','WP001',             '2007-12-5',         100) 
insert into tb values('CK2','XS001','WP001',             '2007-12-5',         100) 
insert into tb values('CK1','XS002','WP002',             '2007-12-5',         100) 
insert into tb values('CK2','XS002','WP002',             '2007-12-5',         100) 
insert into tb values('CK1','XS003','WP001',             '2007-12-2',         30 )
insert into tb values('CK2','XS003','WP001',             '2007-12-2',         40 )
insert into tb values('CK1','XS004','WP002',             '2007-12-2',         50 )
insert into tb values('CK2','XS004','WP002',             '2007-12-2',         60 )
insert into tb values('CK1','XS001','WP001',             '2007-12-3',         10 )
insert into tb values('CK2','XS001','WP001',             '2007-12-3',         10 )
insert into tb values('CK1','XS002','WP002',             '2007-12-3',         10 )            
insert into tb values('CK2','XS002','WP002',             '2007-12-3',         10 )
insert into tb values('CK1','XS003','WP001',             '2007-12-1',         20 )
insert into tb values('CK2','XS003','WP001',             '2007-12-1',         20 )
insert into tb values('CK1','XS004','WP002',             '2007-12-1',         20 )
insert into tb values('CK2','XS004','WP002',             '2007-12-1',         20 )
go--我的。
select a.CKBH , a.WPBH, QMSL = sum(a.QMSL) from tb a where RQ = (select max(RQ) from tb where CKBH = a.CKBH and WPBH = a.WPBH) group by a.CKBH , a.WPBH order by a.CKBH , a.WPBH
/*
CKBH       WPBH       QMSL        
---------- ---------- ----------- 
CK1        WP001      100
CK1        WP002      100
CK2        WP001      100
CK2        WP002      100（所影响的行数为 4 行）
*/--石头的。
select CKBH,WPBH,qmsl = sum(QMSL)
from tb a
where not exists(select 1 from tb where a.CKBH =CKBH and a.WPBH =WPBH and a.RQ<RQ)
group by CKBH,WPBH
/*
CKBH       WPBH       qmsl        
---------- ---------- ----------- 
CK1        WP001      100
CK2        WP001      100
CK1        WP002      100
CK2        WP002      100（所影响的行数为 4 行）
*/drop table tb

Answer 5

要统计:最晚日期某个仓库,某个物品的各销售订单(xsddh)期末数量和 谢谢,各位,好快...........

Answer 6

CK1                                           WP001                     130 你说这条结果咋计算来的?或是哪几条记录相加?

Answer 7

CK1                         XS001                       WP001             2007-12-5         100 
CK1                         XS003                       WP001             2007-12-2         30 CK1                         XS002                       WP002             2007-12-5         100 
CK1                         XS004                       WP002             2007-12-2         50 CK2                         XS001                       WP001             2007-12-5         100 
CK2                         XS003                       WP001             2007-12-2         40 CK2                         XS002                       WP002             2007-12-5         100 
CK2                         XS004                       WP002             2007-12-2         60 仓库编号CKBH                         物品编号WPBH           期末数量QMSL 
CK1                                           WP001                     130 
CK1                                           WP002                     150   CK2                                           WP001                     140           
CK2                                           WP002                     160 

Answer 8

要统计:最晚日期某个仓库,某个物品的各销售订单(xsddh)期末数量和   谢谢,各位,好快...........---
上面的答案就能达到

Answer 9

如楼主所说:
'CK1','XS003','WP001',             '2007-12-1',         20 
这条为啥不加?

Answer 10

create table tb(CKBH varchar(10),XSDDH varchar(10),WPBH varchar(10),RQ datetime,QMSL int)
insert into tb values('CK1','XS001','WP001',             '2007-12-5',         100) 
insert into tb values('CK2','XS001','WP001',             '2007-12-5',         100) 
insert into tb values('CK1','XS002','WP002',             '2007-12-5',         100) 
insert into tb values('CK2','XS002','WP002',             '2007-12-5',         100) 
insert into tb values('CK1','XS003','WP001',             '2007-12-2',         30 )
insert into tb values('CK2','XS003','WP001',             '2007-12-2',         40 )
insert into tb values('CK1','XS004','WP002',             '2007-12-2',         50 )
insert into tb values('CK2','XS004','WP002',             '2007-12-2',         60 )
insert into tb values('CK1','XS001','WP001',             '2007-12-3',         10 )
insert into tb values('CK2','XS001','WP001',             '2007-12-3',         10 )
insert into tb values('CK1','XS002','WP002',             '2007-12-3',         10 )            
insert into tb values('CK2','XS002','WP002',             '2007-12-3',         10 )
insert into tb values('CK1','XS003','WP001',             '2007-12-1',         20 )
insert into tb values('CK2','XS003','WP001',             '2007-12-1',         20 )
insert into tb values('CK1','XS004','WP002',             '2007-12-1',         20 )
insert into tb values('CK2','XS004','WP002',             '2007-12-1',         20 )
go
--石头的。
select CKBH,WPBH,qmsl = sum(QMSL)
from tb a
where not exists(select 1 from tb where XSDDH = a.XSDDH and a.CKBH =CKBH and a.WPBH =WPBH and a.RQ<RQ)
group by CKBH,WPBH
/*
CKBH       WPBH       qmsl        
---------- ---------- ----------- 
CK1        WP001      130
CK2        WP001      140
CK1        WP002      150
CK2        WP002      160
（所影响的行数为 4 行）
*/drop table tb

Answer 11

use test
go
declare @T table (CKBH varchar(10),XSDDH varchar(10),WPBH varchar(10),RQ datetime,QMSL int)
insert into @T values('CK1','XS001','WP001',             '2007-12-5',         100) 
insert into @T values('CK2','XS001','WP001',             '2007-12-5',         100) 
insert into @T values('CK1','XS002','WP002',             '2007-12-5',         100) 
insert into @T values('CK2','XS002','WP002',             '2007-12-5',         100) 
insert into @T values('CK1','XS003','WP001',             '2007-12-2',         30 )
insert into @T values('CK2','XS003','WP001',             '2007-12-2',         40 )
insert into @T values('CK1','XS004','WP002',             '2007-12-2',         50 )
insert into @T values('CK2','XS004','WP002',             '2007-12-2',         60 )
insert into @T values('CK1','XS001','WP001',             '2007-12-3',         10 )
insert into @T values('CK2','XS001','WP001',             '2007-12-3',         10 )
insert into @T values('CK1','XS002','WP002',             '2007-12-3',         10 )            
insert into @T values('CK2','XS002','WP002',             '2007-12-3',         10 )
insert into @T values('CK1','XS003','WP001',             '2007-12-1',         20 )
insert into @T values('CK2','XS003','WP001',             '2007-12-1',         20 )
insert into @T values('CK1','XS004','WP002',             '2007-12-1',         20 )
insert into @T values('CK2','XS004','WP002',             '2007-12-1',         20 )select
CKBH,WPBH,sum([num]) as Qty
from 
(select 
CKBH,WPBH,[num]=isnull((select sum(QMSL) from @T where checksum(CKBH,WPBH,XSDDH)=checksum(t.CKBH,t.WPBH,t.XSDDH) and RQ>t.RQ),0)
from 
@T T
)TT
group by CKBH,WPBH
order by CKBH所影响的行数为 1 行）
（所影响的行数为 1 行）
（所影响的行数为 1 行）CKBH       WPBH       Qty         
---------- ---------- ----------- 
CK1        WP001      130
CK1        WP002      150
CK2        WP001      140
CK2        WP002      160（所影响的行数为 4 行）