求统计语句。

[GDTOPONE]

原数据：
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   

[JS]

select *
from
(
select b.cCusCode,b.cInvCode,b.iQuantity
from
(
select distinct cCusCode,cInvCode,
sum(iQuantity)over(partition by cCusCode,cInvCode) iQuantity
from atd.test_1206_1
group by cCusCode,cInvCode,iQuantity
) a,
(
select distinct cCusCode,cInvCode,
sum(iQuantity)over(partition by cCusCode,cInvCode) iQuantity
from atd.test_1206_1
group by cCusCode,cInvCode,iQuantity
)b
where a.cCusCode=b.cCusCode and b.iQuantity>a.iQuantity
)a,
( select cCusCode,cInvCode,dDate,iTaxUnitPrice
from
(
select b.cCusCode,b.cInvCode,b.iTaxUnitPrice,
case when b.count=1 then b.dDate
when b.count>1 and b.dDate>a.dDate then b.dDate
else null end dDate
from atd.test_1206_1 a,
(select cCusCode,cInvCode,dDate,iTaxUnitPrice,count(*)over(partition by cCusCode,cInvCode)count from atd.test_1206_1)b
where a.cCusCode=b.cCusCode and a.cInvCode=b.cInvCode
)a
where a.dDate is not null
)b
where a.cInvCode=b.cInvCode and a.cCusCode=b.cCusCode ;

[JS]

写的是取得全部的 你再重新写你想要字段就行了 有点烦 但是逻辑很简单 就是两张表都要去唯一的记录进行关联

[JS]

你可以把每个字句拿出来看看查的是什么 肯定是有更简洁的办法 但是没时间去想了

[--小F--]

select
  cCusCode,cInvCode,iTaxUnitPrice,sum(iQuantity) as iQuantity   
from
  tb t
where
  iTaxUnitPrice=(select max(iTaxUnitPrice) from tb where cCusCode=t.cCusCode)
group by
  cCusCode,cInvCode,iTaxUnitPrice

[昵称被占用了]

;WITH CTE AS (
SELECT cCusCode,cInvCode,iTaxUnitPrice,SUM(iQuantity) AS iQuantity
FROM TAB
GROUP BY cCusCode,cInvCode,iTaxUnitPrice
)
SELECT B.cCusCode,B.cInvCode,A.iTaxUnitPrice,B.iQuantity
FROM TAB A,CTE B
WHERE A.cCusCode = B.cCusCode
AND A.cInvCode = B.cInvCode
AND 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
  )

[昵称被占用了]

更正

;WITH CTE AS (
SELECT cCusCode,cInvCode,SUM(iQuantity) AS iQuantity
FROM TAB
GROUP BY cCusCode,cInvCode
)
SELECT B.cCusCode,B.cInvCode,A.iTaxUnitPrice,B.iQuantity
FROM TAB A,CTE B
WHERE A.cCusCode = B.cCusCode
AND A.cInvCode = B.cInvCode
AND 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
  )

[--小F--]

----------------------------------------------------------------
-- 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
--------------开始查询--------------------------
; with f as
(
select 
   cCusCode,cInvCode,SUM(iQuantity) as iQuantity
from
   tb
group 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         9
00002    02       500         10

(2 行受影响)
*/

[Elvis_chen]

一个问题有多个答案，就看楼主选择哪个了，每一个都有它们使用的地方。

[GDTOPONE]

fredrickhu
的方法不行,结果以下：

cCusCode   cInvCode    iTaxUnitPrice    iQuantity
---------------------------------------------------
00001	      01	10.00	          200.00
00002	      01	10.00	          200.00
00002	      02	10.00	          200.00

[GDTOPONE]

附上建表语句：
CREATE TABLE
tb
(
cCusCode varchar(10),   
cInvCode varchar(10),,
dDate  datetime,  
iTaxUnitPrice numeric(10,2),   
iQuantity numeric(10,2)

)

INSERT INTO tb (cCusCode,
cInvCode,
dDate,
iTaxUnitPrice,
iQuantity)
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

[唐诗三百首]

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


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 order by dDate) rn,
 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 t4

cCusCode cInvCode iTaxUnitPrice iQuantity
-------- -------- ------------- -----------
00001    01       10            250
00002    02       10            500

(2 row(s) affected)

[--小F--]

[Quote=引用 6 楼 gdtopone 的回复:]
fredrickhu
的方法不行,结果以下：

SQL code


cCusCode cInvCode iTaxUnitPrice iQuantity
---------------------------------------------------
00001 01 10.00 200.00
00002 ……
[/Quote]

4楼修正了 呵呵 1楼错的

[唐诗三百首]

sorry, 8楼代码有bug,

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


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 t4

cCusCode cInvCode iTaxUnitPrice iQuantity
-------- -------- ------------- -----------
00001    01       9             250
00002    02       10            500

(2 row(s) affected)