数据按要求分组
数据如下:
箱号品名规格 数量订单
1001 BODY UEL 1 150A2-03-114
1001 BODY UEL 11/4100A2-03-114
1001 BODY UCL 3/8196A2-03-114
1001 BODY UCL 1 200A2-03-114
1002 BONNET UEL 1/2200A2-03-114
1002 BONNET UEL 3/4250A2-03-114
1002 BONNET UEL 1150A2-03-114
1002 BONNET UEL 11/4144A2-03-114
1002 BONNET UEL 11/2420A2-03-114
1002 BONNET UCL 1/2350A2-03-114
1002 BONNET UCL 1200A2-03-114
1002 BONNET UCL 11/450A2-03-114
2001 BODY UEL 1/258A2-03-114
2001 BODY UEL 2 300A2-03-114
2002 BONNET UEL 288A2-03-114
3001 BODY UEL 11/2336A2-03-114
3004 BALL UTK 1 4200A2-03-114
3004 BALL UTK 11/2560A2-03-114
3004 BALL UTK 2 162A2-03-114
4001 BODY UTKM 3/82160A2-03-114
4001 BODY UTKM 1/23744A2-03-114
4001 BODY UTKM 3/4960A2-03-114
5052 UNION SCREW PU 1/21200B2-03-114-1
5052 UNION SCREW PU 3/4480B2-03-114-1
5052 UNION SCREW PUM 3/4160B2-03-114-1
5052 UNION SCREW PU 11/236B2-03-114-1
5053 UNION SWIVEL PU 3/4640B2-03-114-1
5053 UNION SWIVEL PU 11350B2-03-114-1
5054 UNION NUT PU 11344B2-03-114-1
5054 UNION NUT PU 11/2108B2-03-114-1
6001 BODY WJU-L 2036B2-02-114A3
6052 UNION SCREW PU 11200B2-03-114-1
6053 UNION SWIVEL PU 1/22400B2-03-114-1
6053 UNION SWIVEL PUM 3/4160B2-03-114-1
6053 UNION SWIVEL PUM 1180B2-03-114-1
6054 UNION NUT PU 1/21760B2-03-114-1
6054 UNION NUT PU 3/4160B2-03-114-1
6054 UNION NUT PUM 3/4160B2-03-114-1
7001 BODY 150UTDZM 1/2528C2-03-114E
8002 CAP 150UTDZM 1328C2-03-114E
9001 BODY 300UTDZM 420C2-03-114X
9002 CAP 300UTDZM 32C2-02-114Z
9002 CAP 300UTDZM 420C2-03-114X
10001 BODY 300UTAZM 1/254C2-03-114C1
10001 BODY 300UTAZM 3/452C2-03-114C1
10002 CAP 150UTDZ 3/490C2-03-114E
10002 CAP 300UTDZM 156C2-02-114K
11001 BODY 300UTAZM 420C2-03-114A
12001 BODY 300UTAZM 328C2-03-114A
13001 BODY 150UTAZM 436C2-03-114A
14001 BODY 150UTAZM 436C2-03-114A
15001 BODY 150UTAZM 45C2-03-114A
15001 BODY 300UTAZM 322C2-03-114A
15029 INSERT 300UTAZM 330C2-03-114N
16001 BODY 300UTAZM 240C2-03-114C1
16001 BODY 300UTAZM 410C2-03-114C1
16029 INSERT 300UTAZM 420C2-03-114C1
17001 BODY 150UTBM 196C2-03-114K
17002 CAP 150UTB 1144C2-03-114H
17002 CAP 150UTBM 196C2-03-114K
18001 BODY 10UTBM 50210C2-03-114H
19001 BODY 150UTB 448C2-03-114H
20002 CAP 10UTBM 50210C2-03-114H
21001 BODY 150UTBM 420C2-03-114H
21002 CAP 10UTBM 8020C2-03-114H
21002 CAP 150UTBM 440C2-03-114H
22002 CAP 150UTBM 11/2288C2-03-114H
23002 CAP 150UTB 2210C2-03-114H
24001 BODY 10UTBM 8020C2-03-114H
24002 CAP 150UTB 460C2-03-114H
25001 BODY 150UTAZM 44C2-03-114A
25001 BODY 150UTAZM 432C2-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.箱号
[解决办法]
你提供几条正确的结果看看
