一个纠结了许久的有关问题，让小弟我头晕的Group By。%￥#&×&@～

一个纠结了许久的问题，让我头晕的Group By。。%￥#&×&@～～
客户名称，日期，产品，单价，备注
CusDate CaptionPriceMemo
A2012-02-26K118A1
A2012-04-15K219A2
B2012-01-13K118A3
B2012-02-27K118A4
B2012-05-06K118A5
B2012-05-07K118.5A6
B2012-01-13K220A7
B2012-05-16K219.5A8
B2012-05-30K219.5A9
B2012-08-15K219.5A10
C2012-01-17K325A11
C2012-03-12K325A12
C2012-04-17K325A13
C2012-05-18K326A14
C2012-02-15K118A15
C2012-07-09K219A16
C2012-05-13K522A17
C2012-03-04K410A18
C2012-05-13K411A19
C2012-05-14K411A20

----上面一组数据，要输出为：

客户名称，日期，产品，单价，备注
CusDate CaptionPriceMemo
A2012-02-26K118A1
A2012-04-15K219A2
B2012-05-07K118.5A6
B2012-08-15K219.5A10
C2012-05-18K326A14
C2012-02-15K118A15
C2012-07-09K219A16
C2012-05-13K522A17
C2012-05-14K411A20

-----------------凌乱的分割线---------------
实际上是这个意思：
我想列出客户、产品和最近一次价格的数据，类似于价格表，要过滤掉无用的数据，只保留最新数据。
可是问题在于如果将Price字段进行GroupBy，则无法去掉无用数据。
如果不进行groupBy则需要用聚合函数，Price字段一操作聚合函数就不是我想要的数据了。

不知道有没有办法不用聚合函数，或者其他什么办法保留原数据，并且还能GroupBy其他字段数据？



[解决办法]
试试是不是你想要的：

SQL code

WITH test (Cus,    [Date],     Caption,    Price    ,Memo) AS  ( SELECT 'A'    ,'2012-02-26'    ,'K1',    18    ,'A1'  union all  SELECT 'A',    '2012-04-15',    'K2',    19    ,'A2'  union all  SELECT 'B',    '2012-01-13',    'K1',    18    ,'A3'  union all  SELECT 'B'    ,'2012-02-27',    'K1',    18    ,'A4'  union all  SELECT 'B'    ,'2012-05-06',    'K1',    18    ,'A5'  union all  SELECT 'B'    ,'2012-05-07',    'K1',    18.5    ,'A6'  union all  SELECT 'B',    '2012-01-13',    'K2',    20    ,'A7'  union all  SELECT 'B',    '2012-05-16',    'K2',    19.5    ,'A8'  union all  SELECT 'B',    '2012-05-30',    'K2',    19.5    ,'A9'  union all  SELECT 'B',    '2012-08-15',    'K2',    19.5    ,'A10'  union all  SELECT 'C',    '2012-01-17',    'K3',25    ,'A11'  union all  SELECT 'C',    '2012-03-12',    'K3',    25    ,'A12'  union all  SELECT 'C',    '2012-04-17',    'K3',    25    ,'A13'  union all  SELECT 'C',    '2012-05-18',    'K3',    26    ,'A14'  union all  SELECT 'C',    '2012-02-15',    'K1',    18    ,'A15'  union all  SELECT 'C',    '2012-07-09',    'K2',    19    ,'A16'  union all  SELECT 'C',    '2012-05-13',    'K5',    22    ,'A17'  union all  SELECT 'C',    '2012-03-04',    'K4',    10    ,'A18'  union all  SELECT 'C',    '2012-05-13',    'K4',    11    ,'A19'   union all  SELECT 'C',    '2012-05-14',    'K4',    11    ,'A20' ) SELECT * FROM  test a WHERE EXISTS (SELECT 1 FROM ( SELECT cus,MAX([date])[date] ,Caption FROM test  GROUP BY cus,Caption) b WHERE a.cus=b.cus AND a.[date]=b.[date] AND a.Caption=b.Caption)  /* Cus  Date       Caption Price                                   Memo ---- ---------- ------- --------------------------------------- ---- A    2012-02-26 K1      18.0                                    A1 A    2012-04-15 K2      19.0                                    A2 B    2012-05-07 K1      18.5                                    A6 B    2012-08-15 K2      19.5                                    A10 C    2012-05-18 K3      26.0                                    A14 C    2012-02-15 K1      18.0                                    A15 C    2012-07-09 K2      19.0                                    A16 C    2012-05-13 K5      22.0                                    A17 C    2012-05-14 K4      11.0                                    A20  (9 行受影响)  */
[解决办法]
SQL code
 SELECT *  FROM test AS t1 WHERE NOT EXISTS(SELECT 1 FROM Test AS t2 WHERE t1.cus = t2.cus AND t1.caption = t2.caption AND t1.date < t2.date)