再求统计语句

Question

原数据：
cCusCode   cInvCode      dDate    iTaxUnitPrice    iQuantity
---------------------------------------------------------
00001        01      2010-02-01      10             200
00001        02      2010-02-01       5             100
00001        01      2010-02-02       9             50
00002        02      2010-02-02       9             300
00002        01      2010-02-02       10            200
00002        02      2010-02-04       10            200需求(按cCusCode得出销售数量(iQuantity)最多的cInvCode,并取cInvCode的最后一次单价(iTaxUnitPrice))
结果如下：
 cCusCode   cInvCode    iTaxUnitPrice    iQuantity
----------------------------------------------------
  00001      01             9             250
  00002      02             10            500   注意：环境为：SQL 2000 

Answer 1

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2011-12-06 08:56:47
-- Version:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) 
--    Apr 22 2011 11:57:00 
--    Copyright (c) Microsoft Corporation
--    Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([cCusCode] varchar(5),[cInvCode] varchar(2),[dDate] datetime,[iTaxUnitPrice] int,[iQuantity] int)
insert [tb]
select '00001','01','2010-02-01',10,200 union all
select '00001','02','2010-02-01',5,100 union all
select '00001','01','2010-02-02',9,50 union all
select '00002','02','2010-02-02',9,300 union all
select '00002','01','2010-02-02',10,200 union all
select '00002','02','2010-02-04',10,200
--------------开始查询--------------------------
select * into #t from
(
select 
   cCusCode,cInvCode,SUM(iQuantity) as iQuantity
from
   tb
group by
   cCusCode,cInvCode
)t
 
 select
  a.*,b.iTaxUnitPrice 
 from
  #t a join tb b 
 on
  a.cCusCode=b.cCusCode and a.cInvCode=b.cInvCode
 where
   b.iTaxUnitPrice=(select top 1 iTaxUnitPrice from tb  where cCusCode=b.cCusCode order by dDate desc)
 and
   a.iQuantity=(select MAX(iQuantity) from #t where cCusCode=a.cCusCode) 
----------------结果----------------------------
/* cCusCode cInvCode iQuantity   iTaxUnitPrice
-------- -------- ----------- -------------
00001    01       250         9
00002    02       500         10(2 行受影响)
*/

Answer 2

cte本来就是子查询，搂主自己改下不就可以了

Answer 3

create table GDTOPONE
(cCusCode varchar(8), cInvCode varchar(2), 
dDate date, iTaxUnitPrice int, iQuantity int)insert into GDTOPONE
select '00001', '01', '2010-02-01', 10, 200 union all
select '00001', '02', '2010-02-01', 5, 100 union all
select '00001', '01', '2010-02-02', 9, 50 union all
select '00002', '02', '2010-02-02', 9, 300 union all
select '00002', '01', '2010-02-02', 10, 200 union all
select '00002', '02', '2010-02-04', 10, 200-- SQL2000的写法
select t4.cCusCode,t4.cInvCode,
t5.iTaxUnitPrice,
t4.iQuantity 
from 
(select t3.cCusCode,
(select top 1 cInvCode from 
 (select t.cCusCode,t.cInvCode,
  sum(t.iQuantity) iQuantity
  from GDTOPONE t
  group by t.cCusCode,t.cInvCode) t1 
  where t1.cCusCode=t3.cCusCode and t1.iQuantity=t3.iQuantity) cInvCode,
t3.iQuantity
from
(select t1.cCusCode,max(t1.iQuantity) iQuantity
from
(select t.cCusCode,t.cInvCode,
  sum(t.iQuantity) iQuantity
  from GDTOPONE t
  group by t.cCusCode,t.cInvCode) t1
group by t1.cCusCode) t3) t4
inner join
(select a.cCusCode,a.cInvCode,a.iTaxUnitPrice 
from GDTOPONE a
inner join
(select cCusCode,cInvCode,max(dDate) mdDate
from GDTOPONE 
group by cCusCode,cInvCode) b
on a.cCusCode=b.cCusCode and a.cInvCode=b.cInvCode
and a.dDate=b.mdDate) t5
on t4.cCusCode=t5.cCusCode and t4.cInvCode=t5.cInvCodecCusCode cInvCode iTaxUnitPrice iQuantity
-------- -------- ------------- -----------
00001    01       9             250
00002    02       10            500(2 row(s) affected)
-- SQL2008的写法
with t1 as
(select t.cCusCode,t.cInvCode,
  sum(t.iQuantity) iQuantity
  from GDTOPONE t
  group by t.cCusCode,t.cInvCode
),
t2 as
(select row_number() over(partition by cInvCode,cCusCode order by dDate) rn,
 cCusCode,cInvCode,iTaxUnitPrice 
 from GDTOPONE
),
t3 as
(select t1.cCusCode,max(t1.iQuantity) iQuantity
 from t1
 group by t1.cCusCode  
),
t4 as
(select t3.cCusCode,
(select top 1 cInvCode from t1 where t1.cCusCode=t3.cCusCode and t1.iQuantity=t3.iQuantity) cInvCode,
t3.iQuantity
from t3
)
select t4.cCusCode,t4.cInvCode,
(select iTaxUnitPrice from t2 where t2.cInvCode=t4.cInvCode 
and t2.rn=(select max(rn) rn from t2 where cInvCode=t4.cInvCode)) iTaxUnitPrice,
t4.iQuantity
from t4cCusCode cInvCode iTaxUnitPrice iQuantity
-------- -------- ------------- -----------
00001    01       9             250
00002    02       10            500(2 row(s) affected)

Answer 4

try this,-- SQL2000的写法
select t4.cCusCode,t4.cInvCode,
t5.iTaxUnitPrice,
t4.iQuantity 
from 
(select t3.cCusCode,
(select top 1 cInvCode from 
 (select t.cCusCode,t.cInvCode,
  sum(t.iQuantity) iQuantity
  from GDTOPONE t
  group by t.cCusCode,t.cInvCode) t1 
  where t1.cCusCode=t3.cCusCode and t1.iQuantity=t3.iQuantity) cInvCode,
t3.iQuantity
from
(select t1.cCusCode,max(t1.iQuantity) iQuantity
from
(select t.cCusCode,t.cInvCode,
  sum(t.iQuantity) iQuantity
  from GDTOPONE t
  group by t.cCusCode,t.cInvCode) t1
group by t1.cCusCode) t3) t4
inner join
(select distinct a.cCusCode,a.cInvCode,a.iTaxUnitPrice 
from GDTOPONE a
inner join
(select cCusCode,cInvCode,max(dDate) mdDate
from GDTOPONE 
group by cCusCode,cInvCode) b
on a.cCusCode=b.cCusCode and a.cInvCode=b.cInvCode
and a.dDate=b.mdDate) t5
on t4.cCusCode=t5.cCusCode and t4.cInvCode=t5.cInvCodecCusCode cInvCode iTaxUnitPrice iQuantity
-------- -------- ------------- -----------
00001    01       9             250
00002    02       10            500(2 row(s) affected)