part_no date qty
------------- ------------------------------------------------------ --------------
TM2002004 2004-04-13 50.000
TM2002004 2004-04-14 456.000
TM2002004 2004-04-14 222.000
20041012TEST 2005-04-20 20.000
20041012TEST 2005-05-06 20.000
20041012TEST 2005-05-15 15.000
20041012TEST 2005-05-16 17.000
20041012TEST 2005-06-02 5.500
20041012TEST 2005-06-06 2.000
20041012TEST 2005-06-06 10.500
20041012TEST 2005-07-04 50.000
20041012Test 2006-06-27 12.000
20041012TEST 2006-06-30 1.000
20041012TEST 2006-07-05 50.000
20041012TEST 2006-07-15 25.000分组对就每一个part_no的相对几天(条件,如10天)为一组。
TM2002004 2004-04-13 2004-04-23 728.000
20041012TEST 2005-04-20 2005-04-30 20.000
20041012TEST 2005-04-30 2005-05-10 20.000
20041012TEST 2005-05-10 2005-05-20 32.000
.
怎么处理??
------------- ------------------------------------------------------ --------------
TM2002004 2004-04-13 50.000
TM2002004 2004-04-14 456.000
TM2002004 2004-04-14 222.000
20041012TEST 2005-04-20 20.000
20041012TEST 2005-05-06 20.000
20041012TEST 2005-05-15 15.000
20041012TEST 2005-05-16 17.000
20041012TEST 2005-06-02 5.500
20041012TEST 2005-06-06 2.000
20041012TEST 2005-06-06 10.500
20041012TEST 2005-07-04 50.000
20041012Test 2006-06-27 12.000
20041012TEST 2006-06-30 1.000
20041012TEST 2006-07-05 50.000
20041012TEST 2006-07-15 25.000分组对就每一个part_no的相对几天(条件,如10天)为一组。
TM2002004 2004-04-13 2004-04-23 728.000
20041012TEST 2005-04-20 2005-04-30 20.000
20041012TEST 2005-04-30 2005-05-10 20.000
20041012TEST 2005-05-10 2005-05-20 32.000
.
怎么处理??
MAX(date) AS dateto,SUM(qty) AS qty
FROM table1 a
GROUP BY parn_no,
DATEDIFF(day,SELECT MIN(date) FROM table1 b WHERE a.part_no=b.part_no,a.date)/10
ORDER BY part_no, datefrom
Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.