求统计语句。

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   

Answer 1

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

Answer 2

;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
  )

Answer 3

更正;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
  )

Answer 4

----------------------------------------------------------------
-- 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 行受影响)
*/

Answer 5

fredrickhu
的方法不行,结果以下：cCusCode   cInvCode    iTaxUnitPrice    iQuantity
---------------------------------------------------
00001       01 10.00           200.00
00002       01 10.00           200.00
00002       02 10.00           200.00

Answer 6

附上建表语句：
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

Answer 7

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 t4cCusCode cInvCode iTaxUnitPrice iQuantity
-------- -------- ------------- -----------
00001    01       10            250
00002    02       10            500(2 row(s) affected)

Answer 8

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 t4cCusCode cInvCode iTaxUnitPrice iQuantity
-------- -------- ------------- -----------
00001    01       9             250
00002    02       10            500(2 row(s) affected)

Answer 9

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 ;