再求统计语句
- SQL code
原数据：cCusCode cInvCode dDate iTaxUnitPrice iQuantity---------------------00001 01 2010-02-01 10 20000001 02 2010-02-01 5 10000001 01 2010-02-02 9 5000002 02 2010-02-02 9 30000002 01 2010-02-02 10 20000002 02 2010-02-04 10 200需求(按cCusCode得出销售数量(iQuantity)最多的cInvCode,并取cInvCode的最后一次单价(iTaxUnitPrice))结果如下： cCusCode cInvCode iTaxUnitPrice iQuantity---------------------------------------------------- 00001 01 9 250 00002 02 10 500
- SQL code
注意：环境为：SQL 2000
[解决办法]
- SQL code
------------------------------ 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 allselect '00001','02','2010-02-01',5,100 union allselect '00001','01','2010-02-02',9,50 union allselect '00002','02','2010-02-02',9,300 union allselect '00002','01','2010-02-02',10,200 union allselect '00002','02','2010-02-04',10,200--------------开始查询--------------------------select * into #t from(select cCusCode,cInvCode,SUM(iQuantity) as iQuantityfrom tbgroup 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 900002 02 500 10(2 行受影响)*/
[解决办法]
try this,
- SQL code
-- 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.iQuantityfrom(select t1.cCusCode,max(t1.iQuantity) iQuantityfrom(select t.cCusCode,t.cInvCode, sum(t.iQuantity) iQuantity from GDTOPONE t group by t.cCusCode,t.cInvCode) t1group by t1.cCusCode) t3) t4inner join(select distinct a.cCusCode,a.cInvCode,a.iTaxUnitPrice from GDTOPONE ainner join(select cCusCode,cInvCode,max(dDate) mdDatefrom GDTOPONE group by cCusCode,cInvCode) bon a.cCusCode=b.cCusCode and a.cInvCode=b.cInvCodeand a.dDate=b.mdDate) t5on t4.cCusCode=t5.cCusCode and t4.cInvCode=t5.cInvCodecCusCode cInvCode iTaxUnitPrice iQuantity-------- -------- ------------- -----------00001 01 9 25000002 02 10 500(2 row(s) affected)