求统计语句。
原数据：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
;WITH CTE AS (SELECT cCusCode,cInvCode,iTaxUnitPrice,SUM(iQuantity) AS iQuantityFROM TABGROUP BY cCusCode,cInvCode,iTaxUnitPrice)SELECT B.cCusCode,B.cInvCode,A.iTaxUnitPrice,B.iQuantityFROM TAB A,CTE BWHERE A.cCusCode = B.cCusCodeAND A.cInvCode = B.cInvCodeAND NOT EXISTS ( SELECT 1 FROM TAB WHERE cCusCode= A.cCusCode AND cInvCode = A.cInvCode AND dDate > A.dDate )AND NOT EXISTS ( SELECT 1 FROM CTE WHERE cCusCode= B.cCusCode AND iQuantity> B.iQuantity )
[解决办法]
------------------------------ 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--------------开始查询--------------------------; with f as(select cCusCode,cInvCode,SUM(iQuantity) as iQuantityfrom tbgroup by cCusCode,cInvCode ) select a.*,b.iTaxUnitPrice from f 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 f where cCusCode=a.cCusCode) ----------------结果----------------------------/* cCusCode cInvCode iQuantity iTaxUnitPrice-------- -------- ----------- -------------00001 01 250 900002 02 500 10(2 行受影响)*/
[解决办法]
sorry, 8楼代码有bug,
create table GDTOPONE(cCusCode varchar(8), cInvCode varchar(2), dDate date, iTaxUnitPrice int, iQuantity int)insert into GDTOPONEselect '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, 200with 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.iQuantityfrom 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.iQuantityfrom t4cCusCode cInvCode iTaxUnitPrice iQuantity-------- -------- ------------- -----------00001 01 9 25000002 02 10 500(2 row(s) affected)