另开一新贴还是问SQL语句,忘赐教,指点一下

原始数据如下

boxno       colt       sizet       quantity
箱号         色号       尺码         数量
----------------------------
1 41 M 20
2 41 M 20
3 41 M 20
4 41 M 20
5 41 M 20
6 41 M 20
7 41 M 20
8 41 M 20
9 41 L 20
10 41 L 20
11 41 L 20
12 41 L 20
13 41 L 20
14 41 L 19
15 41 L 20
16 41 L 20
17 41 L 20
18 41 LL 20
19 41 LL 20
20 41 LL 20
21 41 LL 20
22 49 M 20
23 49 M 20
24 49 M 20
25 49 M 20
26 49 M 20
27 49 M 20
28 49 M 20
29 49 M 20
30 49 M 20
31 49 M 20
32 49 L 20
33 49 L 20
34 49 L 20
35 49 L 20
36 49 L 20
37 49 L 20
38 49 L 20
39 49 L 20
40 49 L 20
41 49 L 20
42 49 L 20
43 49 LL 20
44 49 LL 20
45 49 LL 20
46 49 LL 20
47 49 LL 20
48 51 M 20
49 51 M 20
50 51 M 20
51 51 M 20
52 51 M 20
53 51 M 20
54 51 M 20
55 51 M 20
56 51 M 20
57 51 L 20
58 51 L 20
59 51 L 20
60 51 L 20
61 51 L 20
62 51 L 20
63 51 L 20
64 51 L 20
65 51 L 20
66 51 L 20
67 51 LL 20
68 51 LL 20
69 51 LL 20
70 51 LL 20
71 51 LL 20
72 70 M 20
73 70 M 20
74 70 M 20
75 70 M 20
76 70 M 20
77 70 M 20
78 70 M 20
79 70 L 20
80 70 L 20
81 70 L 20
82 70 L 20
83 70 L 20
84 70 L 20
85 70 L 20
86 70 L 20
87 70 LL 20
88 70 LL 20
89 70 LL 20
90 41 M 9
90 41 L 4
90 41 LL 7
91 41 LL 7
91 49 M 6
91 49 L 5
91 49 LL 2
92 49 LL 5
92 51 M 11
92 51 L 4
93 70 M 9
93 70 L 8
93 70 LL 3
94 51 L 1
94 51 LL 1
94 70 LL 15
------------------------------------


SQL语句如下：
------------------------------------

select   boxno     =case   when   (min(boxno) <> max(boxno))     then   ltrim(min(boxno))+ '- '+ltrim(max(boxno))   else   ltrim(min(boxno))   end   ,xs=count(1),colt,sizet,quantity   from   col     group   by   colt,sizet,quantity   order   by   boxno

结果如下:

行号是为了方便大家阅读添加的
-------------------------------

                  boxno       xs             colt             sizet       quantity
行号           箱号         箱数           色号           尺码           数量
------------------------------------------------
1 14 1 41 L 19
2 1-8 8 41 M 20
3 18-21 4 41 LL 20
4 22-31 10 49 M 20
5 32-42 11 49 L 20
6 43-47 5 49 LL 20
7 48-56 9 51 M 20
8 57-66 10 51 L 20
9 67-71 5 51 LL 20
10 72-78 7 70 M 20
11 79-86 8 70 L 20
12 87-89 3 70 LL 20
13 90 1 41 M 9
14 90 1 41 L 4
15 90-91 2 41 LL 7
16 91 1 49 L 5
17 91 1 49 M 6
18 91 1 49 LL 2
19 9-17 8 41 L 20
20 92 1 49 LL 5
21 92 1 51 M 11
22 92 1 51 L 4
23 93 1 70 L 8
24 93 1 70 M 9
25 93 1 70 LL 3
26 94 1 70 LL 15
27 94 1 51 LL 1
28 94 1 51