数据按要求分组
数据如下:
箱号 品名规格 数量 订单
1 001 BODY UEL 1 150 A2-03-114
1 001 BODY UEL 11/4 100 A2-03-114
1 001 BODY UCL 3/8 196 A2-03-114
1 001 BODY UCL 1 200 A2-03-114
1 002 BONNET UEL 1/2 200 A2-03-114
1 002 BONNET UEL 3/4 250 A2-03-114
1 002 BONNET UEL 1 150 A2-03-114
1 002 BONNET UEL 11/4 144 A2-03-114
1 002 BONNET UEL 11/2 420 A2-03-114
1 002 BONNET UCL 1/2 350 A2-03-114
1 002 BONNET UCL 1 200 A2-03-114
1 002 BONNET UCL 11/4 50 A2-03-114
2 001 BODY UEL 1/2 58 A2-03-114
2 001 BODY UEL 2 300 A2-03-114
2 002 BONNET UEL 2 88 A2-03-114
3 001 BODY UEL 11/2 336 A2-03-114
3 004 BALL UTK 1 4200 A2-03-114
3 004 BALL UTK 11/2 560 A2-03-114
3 004 BALL UTK 2 162 A2-03-114
4 001 BODY UTKM 3/8 2160 A2-03-114
4 001 BODY UTKM 1/2 3744 A2-03-114
4 001 BODY UTKM 3/4 960 A2-03-114
5 052 UNION SCREW PU 1/2 1200 B2-03-114-1
5 052 UNION SCREW PU 3/4 480 B2-03-114-1
5 052 UNION SCREW PUM 3/4 160 B2-03-114-1
5 052 UNION SCREW PU 11/2 36 B2-03-114-1
5 053 UNION SWIVEL PU 3/4 640 B2-03-114-1
5 053 UNION SWIVEL PU 1 1350 B2-03-114-1
5 054 UNION NUT PU 1 1344 B2-03-114-1
5 054 UNION NUT PU 11/2 108 B2-03-114-1
6 001 BODY WJU-L 20 36 B2-02-114A3
6 052 UNION SCREW PU 1 1200 B2-03-114-1
6 053 UNION SWIVEL PU 1/2 2400 B2-03-114-1
6 053 UNION SWIVEL PUM 3/4160 B2-03-114-1
6 053 UNION SWIVEL PUM 1 180 B2-03-114-1
6 054 UNION NUT PU 1/2 1760 B2-03-114-1
6 054 UNION NUT PU 3/4 160 B2-03-114-1
6 054 UNION NUT PUM 3/4 160 B2-03-114-1
7 001 BODY 150UTDZM 1/2 528 C2-03-114E
8 002 CAP 150UTDZM 1 328 C2-03-114E
9 001 BODY 300UTDZM 4 20 C2-03-114X
9 002 CAP 300UTDZM 3 2 C2-02-114Z
9 002 CAP 300UTDZM 4 20 C2-03-114X
10 001 BODY 300UTAZM 1/2 54 C2-03-114C1
10 001 BODY 300UTAZM 3/4 52 C2-03-114C1
10 002 CAP 150UTDZ 3/4 90 C2-03-114E
10 002 CAP 300UTDZM 1 56 C2-02-114K
11 001 BODY 300UTAZM 4 20 C2-03-114A
12 001 BODY 300UTAZM 3 28 C2-03-114A
13 001 BODY 150UTAZM 4 36 C2-03-114A
14 001 BODY 150UTAZM 4 36 C2-03-114A
15 001 BODY 150UTAZM 4 5 C2-03-114A
15 001 BODY 300UTAZM 3 22 C2-03-114A
15 029 INSERT 300UTAZM 3 30 C2-03-114N
16 001 BODY 300UTAZM 2 40 C2-03-114C1
16 001 BODY 300UTAZM 4 10 C2-03-114C1
16 029 INSERT 300UTAZM 4 20 C2-03-114C1
17 001 BODY 150UTBM 1 96 C2-03-114K
17 002 CAP 150UTB 1 144 C2-03-114H
17 002 CAP 150UTBM 1 96 C2-03-114K
18 001 BODY 10UTBM 50 210 C2-03-114H
19 001 BODY 150UTB 4 48 C2-03-114H
20 002 CAP 10UTBM 50 210 C2-03-114H
21 001 BODY 150UTBM 4 20 C2-03-114H
21 002 CAP 10UTBM 80 20 C2-03-114H
21 002 CAP 150UTBM 4 40 C2-03-114H
22 002 CAP 150UTBM 11/2 288 C2-03-114H
23 002 CAP 150UTB 2 210 C2-03-114H
24 001 BODY 10UTBM 80 20 C2-03-114H
24 002 CAP 150UTB 4 60 C2-03-114H
25 001 BODY 150UTAZM 4 4 C2-03-114A
25 001 BODY 150UTAZM 4 32 C2-03-114B


要求分组,每组品名品种不得超过20种,同一箱号不能分开出现在两个组, 请问这个能实现吗?
谢谢!

------解决方案--------------------
select b.*
from (select 箱号 from tab group by 箱号 having count(distinct 品名规格)<20)as a
inner join tab as b on a.箱号=b.箱号
------解决方案--------------------
你提供几条正确的结果看看