另开一新贴还是问SQL语句,忘赐教,指点一下
原始数据如下
boxno colt sizet quantity
箱号 色号 尺码 数量
----------------------------
141M20
241M20
341M20
441M20
541M20
641M20
741M20
841M20
941L20
1041L20
1141L20
1241L20
1341L20
1441L19
1541L20
1641L20
1741L20
1841LL20
1941LL20
2041LL20
2141LL20
2249M20
2349M20
2449M20
2549M20
2649M20
2749M20
2849M20
2949M20
3049M20
3149M20
3249L20
3349L20
3449L20
3549L20
3649L20
3749L20
3849L20
3949L20
4049L20
4149L20
4249L20
4349LL20
4449LL20
4549LL20
4649LL20
4749LL20
4851M20
4951M20
5051M20
5151M20
5251M20
5351M20
5451M20
5551M20
5651M20
5751L20
5851L20
5951L20
6051L20
6151L20
6251L20
6351L20
6451L20
6551L20
6651L20
6751LL20
6851LL20
6951LL20
7051LL20
7151LL20
7270M20
7370M20
7470M20
7570M20
7670M20
7770M20
7870M20
7970L20
8070L20
8170L20
8270L20
8370L20
8470L20
8570L20
8670L20
8770LL20
8870LL20
8970LL20
9041M9
9041L4
9041LL7
9141LL7
9149M6
9149L5
9149LL2
9249LL5
9251M11
9251L4
9370M9
9370L8
9370LL3
9451L1
9451LL1
9470LL15
------------------------------------
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
行号 箱号 箱数 色号 尺码 数量
------------------------------------------------
114141L19
21-8841M20
318-21441LL20
422-311049M20
532-421149L20
643-47549LL20
748-56951M20
857-661051L20
967-71551LL20
1072-78770M20
1179-86870L20
1287-89370LL20
1390141M9
1490141L4
1590-91241LL7
1691149L5
1791149M6
1891149LL2
199-17841L20
2092149LL5
2192151M11
2292151L4
2393170L8
2493170M9
2593170LL3
2694170LL15
2794151LL1
2894151L1
错误点：14号箱出现了两次 在第一行和第19行，
应得出的结果是：
行号 箱号 箱数 色号 尺码 数量
------------------------------------------------
....
114141L19
... 9-13 5 41 L 20
... 15-17 2 41 L 20
...
请哪位高手改一下SQL语句谢谢了
[解决办法]
楼主你的语句写的不对，如果只有有这么两条记录
箱号 色号 尺码 数量
----------------------------
10041M20
20041M20
用你的语句查出来就是
行号 箱号 箱数 色号 尺码 数量
------------------------------------------------
1100-200241M20
明显不是你的本意啊
[解决办法]
/*
Limpire：OK
*/
--原始数据：@A
declare @A table(boxno int,colt int,sizet varchar(2),quantity int)
insert @A
select 1,41, 'M ',20 union all
select 2,41, 'M ',20 union all
select 3,41, 'M ',20 union all
select 4,41, 'M ',20 union all
select 5,41, 'M ',20 union all
select 6,41, 'M ',20 union all
select 7,41, 'M ',20 union all
select 8,41, 'M ',20 union all
select 9,41, 'L ',20 union all
select 10,41, 'L ',20 union all
select 11,41, 'L ',20 union all
select 12,41, 'L ',20 union all
select 13,41, 'L ',20 union all
select 14,41, 'L ',19 union all
select 15,41, 'L ',20 union all
select 16,41, 'L ',20 union all
select 17,41, 'L ',20 union all
select 18,41, 'LL ',20 union all
select 19,41, 'LL ',20 union all
select 20,41, 'LL ',20 union all
select 21,41, 'LL ',20 union all
select 22,49, 'M ',20 union all
select 23,49, 'M ',20 union all
select 24,49, 'M ',20 union all
select 25,49, 'M ',20 union all
select 26,49, 'M ',20 union all
select 27,49, 'M ',20 union all
select 28,49, 'M ',20 union all
select 29,49, 'M ',20 union all
select 30,49, 'M ',20 union all
select 31,49, 'M ',20 union all
select 32,49, 'L ',20 union all
select 33,49, 'L ',20 union all
select 34,49, 'L ',20 union all
select 35,49, 'L ',20 union all
select 36,49, 'L ',20 union all
select 37,49, 'L ',20 union all
select 38,49, 'L ',20 union all
select 39,49, 'L ',20 union all
select 40,49, 'L ',20 union all
select 41,49, 'L ',20 union all
select 42,49, 'L ',20 union all
select 43,49, 'LL ',20 union all
select 44,49, 'LL ',20 union all
select 45,49, 'LL ',20 union all
select 46,49, 'LL ',20 union all
select 47,49, 'LL ',20 union all
select 48,51, 'M ',20 union all
select 49,51, 'M ',20 union all
select 50,51, 'M ',20 union all
select 51,51, 'M ',20 union all
select 52,51, 'M ',20 union all
select 53,51, 'M ',20 union all
select 54,51, 'M ',20 union all
select 55,51, 'M ',20 union all
select 56,51, 'M ',20 union all
select 57,51, 'L ',20 union all
select 58,51, 'L ',20 union all
select 59,51, 'L ',20 union all
select 60,51, 'L ',20 union all
select 61,51, 'L ',20 union all
select 62,51, 'L ',20 union all
select 63,51, 'L ',20 union all
select 64,51, 'L ',20 union all
select 65,51, 'L ',20 union all
select 66,51, 'L ',20 union all
select 67,51, 'LL ',20 union all
select 68,51, 'LL ',20 union all
select 69,51, 'LL ',20 union all
select 70,51, 'LL ',20 union all
select 71,51, 'LL ',20 union all
select 72,70, 'M ',20 union all
select 73,70, 'M ',20 union all
select 74,70, 'M ',20 union all
select 75,70, 'M ',20 union all
select 76,70, 'M ',20 union all
select 77,70, 'M ',20 union all
select 78,70, 'M ',20 union all
select 79,70, 'L ',20 union all
select 80,70, 'L ',20 union all
select 81,70, 'L ',20 union all
select 82,70, 'L ',20 union all
select 83,70, 'L ',20 union all
select 84,70, 'L ',20 union all
select 85,70, 'L ',20 union all
select 86,70, 'L ',20 union all
select 87,70, 'LL ',20 union all
select 88,70, 'LL ',20 union all
select 89,70, 'LL ',20 union all
select 90,41, 'M ',9 union all
select 90,41, 'L ',4 union all
select 90,41, 'LL ',7 union all
select 91,41, 'LL ',7 union all
select 91,49, 'M ',6 union all
select 91,49, 'L ',5 union all
select 91,49, 'LL ',2 union all
select 92,49, 'LL ',5 union all
select 92,51, 'M ',11 union all
select 92,51, 'L ',4 union all
select 93,70, 'M ',9 union all
select 93,70, 'L ',8 union all
select 93,70, 'LL ',3 union all
select 94,51, 'L ',1 union all
select 94,51, 'LL ',1 union all
select 94,70, 'LL ',15
/*
Limpire：必须通过中间表生成一个groupno，直接在原始表加一列也行。
数据特性和报表要求决定，很难通过join方式或其它方式获得groupno。
有的一箱有几条记录，现有的数据主要问题在90箱和91箱，出现交叉。
通过SQL来将就它不是不可以，但下一批数据可能又不适用了。
*/
declare @TEMP table(groupno int,boxno int,colt int,sizet varchar(2),quantity int)
insert @TEMP select null,* from @A
declare @groupno int,@boxno int,@colt int,@sizet varchar(2),@quantity int
--先生成每箱只有一条记录的groupno
select @groupno=1
update @TEMP set groupno=@groupno,@groupno=case when colt <> @colt or sizet <> @sizet or quantity <> @quantity then @groupno+1 else @groupno end,@colt=colt,@sizet=sizet,@quantity=quantity from @TEMP where boxno in (select boxno from @TEMP group by boxno having(count(*))=1)
--再生成每箱有多条记录的groupno
select @groupno=@groupno+1,@colt=null,@sizet=null,@quantity=null
update @TEMP set groupno=@groupno,@groupno=case when @boxno <> boxno or colt <> @colt or sizet <> @sizet or quantity <> @quantity then @groupno+1 else @groupno end,@boxno=boxno,@colt=colt,@sizet=sizet,@quantity=quantity from @TEMP where groupno is null
--最终结果
select boxno=case when min(boxno)=max(boxno) then cast(min(boxno) as varchar) else cast(min(boxno) as varchar)+ '- '+cast(max(boxno) as varchar) end,箱数=count(*),colt,sizet,quantity from @TEMP group by groupno,colt,sizet,quantity
/*
boxno箱数coltsizetquantity
1-8841M20
9-13541L20
14141L19
15-17341L20
18-21441LL20
22-311049M20
32-421149L20
43-47549LL20
48-56951M20
57-661051L20
67-71551LL20
72-78770M20
79-86870L20
87-89370LL20
90141M9
90141L4
90141LL7
91141LL7
91149M6
91149L5
91149LL2
92149LL5
92151M11
92151L4
93170M9
93170L8
93170LL3
94151L1
94151LL1
94170LL15
*/
