iT邦幫忙

4

趣味SQL,求出前十大客戶各自的前十大商品合計銷售額

求出前十大客戶各自的前十大商品合計銷售額,題目不難,但要求是只用一道SQL指令來完成,程式碼越短越好。
(有的大客戶可能沒有買10項商品,但總金額就擠進前十大了,所以,本題答案不見得是就是100列)

資料如下:

CREATE TABLE IF NOT EXISTS `temp` (
  `custid` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '0' COMMENT '客戶代碼',
  `productid` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '存貨代碼',
  `amount` bigint(12) NOT NULL COMMENT '銷售額'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `temp` (`custid`, `productid`, `amount`) VALUES ('WJ0090', 'EK-15-SSP5', 85860), ('U20032', 'NART-30UUR-IKO', 4800), ('WJ0130', 'Z12', 70), ('X20016', 'SMK-12WUU-E-SK', 45000), ('WJ0131', 'SSR15XW1UU-1120LY', 3000), ('FX0016', 'L-1980ZZ', 720), ('9R0004', 'HSR25AZZ', 301140), ('FT0007', 'SMF-12GUU-SK', 660), ('FT0007', '7204BW', 2430), ('XU0007', 'RB-2508UUCC0', 9280), ('F10002', 'SSLF-1060-EZO', 1472), ('WJ0104', 'LM-30UU', 6450), ('WG0012', 'L-730ZZ-JIB', 160), ('WJ0090', '6001-H-20T1XDDU*MA', 104000), ('WJ0094', 'L-630ZZ', 4230), ('WG0012', 'SS6901ZZ-YCB', 50), ('WJ0009', 'TLA-1210', 372), ('WG0012', 'L-730ZZ', 960), ('FT0007', 'AFF+70G', 29904), ('WJ0091', 'EGH20R260', 660), ('RM0012', 'MBF1602-3.7G0-334LC3', 90000), ('WJ0091', 'FK-20', 6980), ('AF5010', 'DPM-2550', 1770), ('UU0009', '6001ZZ-NSK', 300), ('RJ0006', 'T-1810', 7000), ('WJ0094', 'L-630ZZ', 846), ('WJ0090', '6006-H-20T1XDDU*MA', 38500), ('ZM0006', 'L-1960ZZ', 160), ('5Z0011', 'SB-30', 980), ('WJ0100', 'LM-6', 800), ('WJ0090', '6811LLB', 141000), ('JR0008', 'AFB-LF+70G', 3000), ('FT0007', 'AFF+70G', 29904), ('MT0024', 'MTC-55CRD-22K6-25K8', 1450), ('WJ0091', 'SS6201-2RS', 3360), ('WJ0104', '6304VV-NSK', 400), ('FM0014', 'CF-3MA-JNS', 6900), ('F10001', 'GRS-LGU', 9000), ('XM0007', '6218-NTN', 950), ('WJ0009', 'NTB-1024', 336), ('WJ0009', 'L-1340ZZ', 39), ('BM0002', 'CFN-6RA', 14700), ('WJ0031', 'SRS12WMUU', 2360), ('WG0012', '6815ZZ-FBJ', 1000), ('WJ0090', 'LMF-30MLUU', 23820), ('FT0007', 'SHS45LV2UUFME-3610LTF', 63460), ('WG0012', 'LM-12UU-YCB', 140), ('BM0002', 'BWU25-45', 14000), ('AZ0005', 'HSR25LCSS', 55200), ('VU0021', 'GRS-LGU', 9500), ('CF0022', 'VR3-75H-10Z', 16275), ('WG0012', '1651-211-22-4-1605-201-31-1300L', 33600), ('F10002', 'SVW2120-21Z', 10800), ('U20007', 'SCE200-80X120-2', 1635), ('WJ0090', '2SRS12MUU-120LM', 4630), ('WJ0090', '6002ZZ-NSK', 5200), ('WJ0091', 'VLAST45-06-0100-0-0N', 12300), ('RU0035', 'MTC-65CRD-19-20', 3760), ('U20010', '2-KSH-40L-110-52-HD-HD', 120000), ('NV0002', 'L-1260ZZ', 3600), ('5Z0011', 'Z12', 40), ('FT0007', 'SMSF-30WUU-E', 12080), ('RE0001', '30206-NSK', 1680), ('WG0012', 'L-1360ZZ-JIB', 112), ('FX0011', 'GRS-NS7', 6500), ('FT0006', 'WBK20-11-2', 3500), ('FT0007', '6004-H-20T1XDDU*MA', 222), ('WJ0094', 'L-1650ZZ', 70), ('FT0007', 'LMK-13LUU', 2100), ('U20007', 'SRS15-310LM', 3500), ('RJ0006', 'L-1910ZZ', 4800), ('TR0009', '6804ZZ-NTN', 520), ('WJ0090', '6905-H-20T1XDDU*MA', 30000), ('FT0006', '6914VV', 1350), ('WJ0009', 'L-1350ZZ', 86), ('WJ0090', '1314127', 284000), ('R50025', 'Z18', -17300), ('CR0003', 'SFIR3205-4-635L-DF-C7', 12770), ('FT0007', '1666-113-20', 4485), ('WJ0091', '6003-H-20T1XDDU*MA', 192), ('CF0022', 'NV2045-9Z', 19300), ('WJ0075', 'L-1480ZZ', 360), ('WG0012', 'L-1360ZZ-JIB', 112), ('FG0010', 'LM-8', 680), ('WJ0009', 'L-1560ZZ', 1672), ('MC0007', 'SSR15XVUU', 2700), ('WJ0090', 'AFF+400G', 290000), ('WJ0090', '1314095', -360000), ('WC0009', '7210A5RDULP3', 27600), ('WJ0009', 'L-1040ZZ', 360), ('R20016', 'SR30-3000L', 180900), ('9R0004', 'SHS35R2SS-2040L', 57800), ('5X0011', 'Z12', 40), ('FT0007', 'SS6001ZZ', 2610), ('WJ0090', '1315768', -340000), ('9R0003', 'DK2506S-3RRGT-503LC7', 45820), ('WJ0090', '6904ZZ-NSK', 95), ('U20032', 'SHS30RSSFM', 29200), ('WJ0009', '6902ZZ', 352), ('WJ0091', '0455-203-31-280L', 8415), ('RU0035', 'MFL-32-12-12', 2040), ('JR0008', 'AFB-LF+70G', 3000), ('WJ0221', 'UC215-FYH', 3400), ('WJ0095', 'AFF+70G', 3720), ('WJ0009', 'L-1040ZZ', 90), ('WJ0090', 'AFF+400G', 290000), ('WJ0009', '6900ZZ-EZO', 120), ('VU0004', 'GRS-AS2', 600), ('JA0004', 'GRS-LG2', 14500), ('WJ0091', 'SS6203-2RS', 2628), ('WJ0009', 'VR6-100H-7Z', 4290), ('FT0007', '1-KSH-32WL-60-500', 66000), ('WJ0090', 'AFA+400G', 90000), ('CW0015', 'LWHDG12SL', 8600), ('WG0012', 'LF-740ZZ-JIB', 1152), ('WJ0090', '1651-294-20', 7440), ('FT0006', 'L-1650ZZ', 1200), ('CV0047', 'MTC-40CRD-15-16', 4705), ('SF0002', 'Z18', 6100), ('CW0015', 'GRS-LG2', 16000), ('MA0010', '6810ZZ-NTN', 3900), ('U20007', '2-KSH-40-110-54-HD-HD', 25200), ('JR0008', 'SSA-2216', 1275), ('WG0012', 'CF-6UUA', 870), ('EV0012', 'XGL2-30C-8-14', 2150), ('CF0004', 'GRS-LGU', 22000), ('CM0005', 'SS6804ZZ-FBJ', 5800), ('CM0005', '6205-H-20T1XAZZ*MA', 740), ('UG0028', 'BGXH25FN-N-Z0', 2780), ('WJ0009', 'CRT6-12', 384), ('FM0014', 'HK-1501-A', 49800), ('WJ0091', 'GRS-LG2', 8800), ('WJ0090', '010-T5-0900E-F', 2670), ('WJ0090', 'WGF50100FM-3ZZG3-2900LC7F', 573720), ('VA0003', 'LF-740ZZ-JIB', 200), ('FT0008', 'AFF+70G', 17800), ('WJ0091', '0443-293-01', 2780), ('CF0004', 'RNA-6911UU', 9450), ('CW0015', 'BST17X47-1BP4', 8044), ('BM0002', 'CF-3MA', 19440), ('FT0007', 'LF-850ZZ', 10000), ('WG0012', 'SS6901ZZ-YCB', 300), ('CF0022', 'NV2045-9Z', 11580), ('FT0007', 'AFF+70G', 712), ('WJ0090', '1623-114-20', 49000), ('A-0002', 'GRS-NF2', 2800), ('BM0002', 'AFC+70G', 2870), ('MT0024', 'MHS-35C', 715), ('U20012', 'RB-18025UUC0P5-6', -4750), ('2C0017', 'AFE-CA+70G', 520), ('XU0007', 'RA-5008UUCC0', 76500), ('2X0011', '6702ZZ', 5240), ('FT0007', 'AFF+70G', 29904), ('WJ0065', 'GRS-LG2', 8400), ('MZ0011', 'CHROME-A-1+1/8', 8640), ('XA0008', 'GRS-LR3', 7200), ('VU0021', 'AFE-CA+70G', 5400), ('2C0017', 'AFC+70G', 580), ('UZ0009', 'LM-25MGUU', 3575), ('XU0007', 'RB-2508UUCC0', 62640), ('FT0007', 'AFF+70G', 2136), ('WJ0091', 'SHS20V1SSFE-220LF', 4800), ('GZ0003', 'Z12', 40), ('AG0013', '6214-NTN', 620), ('WJ0091', 'EGH20R1770', 4100), ('WG0012', 'LRT-121516', 45), ('WJ0091', 'SHW14CR2UUM-250LM', 20844), ('VU0021', 'GRS-LGU', 3800), ('9R0004', 'LM-8S', 19950), ('WJ0090', '1315772', -370000), ('VE0018', 'GRS-LG2', 1400), ('WJ0094', 'CFS-3A', 1300), ('WJ0091', 'R20-20A1-FSW-410-520-0.018', 10650), ('5Z0011', 'SSR15XV2UUC1M-160LM', 13200), ('9R0004', 'HSR25BSS', 256500), ('WJ0009', '6204ZZNR', 600), ('WG0012', 'SS6901ZZ-YCB', 400), ('UT0032', 'Z12', 120), ('WJ0090', '1315197', 114590), ('9R0004', 'SHS25C2SS-3440LT', 64236), ('WJ0091', 'Z14', 8600), ('UA0011', '6800ZZ-NSK', 1200), ('XU0007', 'FK-8', 6300), ('JR0008', 'AFC+70G', 520), ('CF0022', 'VR3-50H-7Z', 7455), ('RU0035', 'MTC-55CRD-15-20', 2560), ('EX0016', 'MFC-20-5-10', 450), ('CW0015', 'FA-1210MB-C', 29600), ('XV0020', 'NTB-5070', 1100), ('U20010', 'SS6202ZZ-NSK', 3780), ('FT0008', 'AFF+70G', 18512), ('5Z0011', 'SSL-1450ZZ', 3150), ('BM0002', '6000ZZ-YCB', 200), ('WJ0090', '6004ZZ-NSK', 1260), ('WJ0009', 'TLA-810', 399), ('WJ0104', '6006ZZ-NSK', 720), ('520008', '7000CTYNDBHP4', 1145), ('WX0013', 'CB-2012', 18), ('9R0004', 'SSR35XW3SSFM-3320LHF', 77000), ('WJ0091', '6203DDU-NSK', 330), ('FT0007', '6001-H-20T1XZZ*MA', 4560), ('RC0004', 'SFP25-950L', 3400), ('WJ0090', 'SHS45V4SSHHC0FMS-6870LHFT', 344296), ('U20011', '6212-2RS-KOYO', 3456), ('WJ0090', '1314134', 290000), ('CR0003', 'BGXS20BN-1-L520-N-Z0', 18690), ('EZ0008', 'Z12', 65), ('FT0007', 'AFF+70G', 3580), ('WG0012', '6815ZZ-FBJ', 2000), ('WX0013', 'AW10', 33), ('UR0021', '6901ZZNR', 460), ('WG0012', '1200-IJK', 360), ('FT0006', '6000-H-20T1XZZ*MA', 13900), ('RJ0006', 'T-1810', 7000), ('CA0006', '6802ZZ-NSK', 3650), ('GB0004', 'GRS-PS2', 1480), ('U20032', 'BTK2510V-5.3ZZ-612.5LC7T', 49140), ('RC0052', 'L-1680HH-NMB', 665), ('RE0006', 'CF-6BUU', 3237), ('U20032', 'BWU25-30', 4070), ('CJ0004', 'GRS-PS2', 360), ('MC0023', 'CFH-6UURA', 5200), ('WG0012', '126', 280), ('XC0007', 'AFE-CA+70G', 3300), ('R10009', 'UFL005-ASAHI', 1860), ('VT0004', 'SFP20-250L', 680), ('FT0006', '6910ZZ-NSK', 500), ('WJ0090', '1315767', 348000), ('5C0018', '6904-H-20T1XDDU*MA', 8550), ('WJ0091', '6004-H-20T1XZZ', 18560), ('WC0009', '6204ZZC4-SKF', 3400), ('WJ0009', 'LF-1140ZZ', 510), ('2X0011', '6702ZZ', 7860), ('WJ0090', 'GRS-LG2', 35200), ('U20007', '6804-H-20T1XZZ*MA', 992), ('CW0015', 'LF-740ZZ', 3500), ('WJ0075', 'L-1480ZZ', 360), ('TR0009', 'AFE-CA+70G', 1500), ('FM0014', '6004-H-20T1XDDU*MA', 1180), ('WG0012', '126', 280), ('FU0015', 'GRS-PS2', 1710), ('XQ0004', 'MHC-45C', 935), ('EV0012', 'XGL2-30C-12-14', 2150), ('FT0007', '6908VV-NSK', 522), ('2C0017', 'AFE-CA+70G', 520), ('U20007', 'SR30M1WUU', 94500), ('FG0008', 'GRS-LR3', 1790), ('CW0015', 'LF-630ZZ-JIB', 180), ('FT0007', 'AFF+70G', 35600), ('WJ0091', 'Z13', 13000), ('C20013', 'BGXH15BN-1-L900-N-Z0-30-30', 1900), ('9R0003', '6905-H-20T1XZZ*MA', 26300), ('9R0003', 'BNT1605-2.6ZZ-319LT', 7400), ('WJ0009', 'LF-630ZZ', 600), ('CA0020', 'L-1030ZZ(623)', 1200), ('WJ0090', '1315770', -360000), ('RJ0006', 'L-1910ZZ', 4800), ('WJ0090', '6000ZZ-NSK', 1440), ('JR0008', 'AFC+70G', 2080), ('VU0041', 'SS6004ZZ-KOYO', 960), ('BM0004', 'GRS-NSL', 8000), ('WJ0091', 'Z13', 3510), ('WJ0009', 'ML12C2R350H', 3665), ('EV0012', '6800ZZ-NSK', 6750), ('FT0007', '6207ZZ-NSK', 1380), ('FU0015', 'AFC+70G', 2280), ('CV0023', 'GRS-LG2', 4350), ('WJ0009', 'L-1060', 180), ('FG0007', 'SSR20XW1UU-220L', 4900), ('WJ0009', '6800ZZ-NTN', 162), ('WJ0009', '6900ZZ-EZO', 120), ('WJ0091', '6811LLB', 18800), ('WJ0095', 'AFF+70G', 3720), ('WJ0091', '1315558', 60000), ('FA0007', 'SHS45LCSS', 14800), ('WJ0090', 'BNK1004-2.5RRG2-230LC7Y', 12660), ('WJ0090', 'GRS-LG2', 35200), ('GG0004', 'AFB-LF+70G', 915), ('WX0013', '51210-NACHI', 405), ('WJ0009', 'L-1060', 720), ('FT0006', '7204ATYNDFMP5', 1785), ('WJ0091', 'ST-16B', 740), ('WJ0090', 'SVR45R3SSHHC0FMS-5925LPTF', 2798600), ('WJ0091', 'AFF+400G', 2900), ('WJ0009', 'LF-1480ZZ', 6720), ('RJ0006', '6000ZZ-IBK', 2350), ('9R0004', 'HSR25BSS', 11400), ('WG0012', 'L-1360ZZ-JIB', 224), ('WJ0009', '6201ZZNR', 400), ('VU0041', '6002-KOYO', 516), ('WJ0094', 'L-630ZZ', 846), ('WJ0090', 'BK-25', 7500), ('FG0008', 'MTC-55CRD', 1390), ('520009', 'SSR15XW1UUM-110LYM', 8800), ('WJ0009', 'LF-1480ZZ', 140), ('CJ0004', 'RIF-518ZZ', 850), ('U20007', '6920VV-NTN', 3800), ('WJ0091', '608-H-DDMC3*MA', 2100), ('WJ0091', 'SHW14CR2UUM-200LM', 22000), ('2C0017', 'Z12', 65), ('CM0005', 'SS6303ZZ', 13350), ('FU0015', 'AFA+70G', 8550), ('CV0047', 'MTC-55CRD-12-24', 2590), ('CW0015', 'LCF-16BLUU', 5580), ('VE0020', 'HGP', 3000), ('WJ0094', 'CFS-4VA-JNS', 1480), ('U20007', 'SCE200-80X120-2', 4700), ('RC0004', 'SFP25-950L', 3400), ('WJ0090', 'BLK3636FM-7.2QZZZ-1788LF', 51500), ('RU0035', 'MTC-55CRD-15-15', 8960), ('CA0006', 'SMR106ZZMC3*MAZ', 260), ('WJ0009', 'CRT6-12', 192), ('X20013', 'AFF+70G', 5700), ('CF0032', 'BARRIERTA-IS/V-75G', 4658), ('WJ0009', 'L-940ZZ', 210), ('FT0008', 'AFF+70G', 35600), ('WJ0009', '7004ADB', 2318), ('WJ0091', 'FK-20', 13960), ('WJ0094', 'L-630ZZ', 846), ('WG0012', 'Z1', 4), ('WG0012', '51100-YCB', 210), ('WJ0094', '6800-H-20ZZ*MA', 3160), ('EX0002', 'MG70', 2750), ('FT0007', 'SFC-030DA2-14B-14B', 1360), ('JR0008', 'GRS-LG2', 2800), ('CA0020', 'L-1360ZZ(686)', 960), ('WJ0009', '6801ZZ', 252), ('MT0024', 'SSR15XW1SS-730LY', 10200), ('WJ0009', 'LF-1760ZZ', 280), ('RE0001', '6307ZZ-NSK', 1500), ('2F0006', 'AFC+70G', 1100), ('WJ0090', '6203-H-20T1XDD*MA', 14400), ('9R0004', 'HSR25A6ZZE-2320LTE', 64820), ('WJ0090', 'HSR25R2SSFMS-340LM', 26600), ('WJ0094', 'L-630ZZ', 846), ('250007', '6001ZZ-NSK', 5000), ('250007', '6000ZZ-NSK', 5200), ('XR0020', 'AFA+70G', 810), ('WJ0069', 'BRD35A0', 12400), ('WJ0090', 'AFF+400G', 145000), ('9R0004', 'BLK2020-3.6ZZ-1000L', 10680), ('WJ0069', 'POS-12', 1225), ('U20008', 'SHS30VSS', 2835), ('FT0007', '6816ZZ-NSK', 2580), ('FT0006', 'AFF+70G', 1780), ('U20007', 'NAS20CLNPCZ1AA', 4080), ('UA0011', '6000ZZ-NSK', 6800), ('TR0010', 'AG0524A0', 240), ('WJ0090', '6902-H-20T1XDD1*MA', 42000), ('WJ0090', '6001-H-20T1XDDU*MA', 52000), ('WG0012', 'SHS25C4SSC1-1300LSP-2', 201600), ('WJ0009', '6802ZZ', 82), ('U20007', 'BLK2525F-7.2ZZ-402LTFG-JIK-4', 4725), ('FT0007', 'LM-50UU', 6000), ('WJ0094', 'LM-8UU', 740), ('CF0022', 'LF-1360ZZ', 660), ('CA0006', '6802ZZ-NSK', 7300), ('CR0003', 'BGXS20BS-1-L226-N-Z0', 3400), ('U20007', '33007-NSK', 3720), ('1F0002', '7901CTYNSULP4', 84000), ('WJ0090', '1623-114-20', 58800), ('UZ0009', 'PSS1505N1D0261', 26300), ('FG0008', 'AFF+70G', 5800), ('WJ0009', 'NTB-1226', 336), ('CF0022', 'VR3-50H-7Z', 2485), ('BM0002', 'MG70-P', 585), ('WG0012', '1200-IJK', 360), ('U20007', '33007-NSK', 1240), ('XC0007', 'SKR2602A-0060-P0-00A0', 209880), ('WG0012', 'L-730ZZ', 160), ('WJ0091', 'SS6800-2RS', 14200), ('FU0015', 'AFE-CA+70G', 24360), ('VT0004', 'Z12', 40), ('WJ0091', 'SS6203-2RS', 2190), ('FT0007', '6004-H-20T1XDDU*MA', 11100), ('FT0008', '6004-H-20T1XDDU*MA', 22200), ('WJ0094', 'L-630ZZ', 846), ('WJ0090', '6002-H-20T1XZZ*MA', 1700), ('WG0012', 'MHC-27C-8-8', 1160), ('WJ0090', 'SGL35HTE2-1080/W2LB', 81400), ('ZM0006', 'L-1040ZZ', 960), ('2F0006', 'AFC+400G', 6300), ('WG0012', 'LF-740ZZ', 96), ('CM0005', '6205-H-20T1XAZZ*MA', 2960), ('FT0008', '7204BW', 3645), ('R10009', 'HSR35RSS', 18800), ('WJ0090', '6203ZZ-NTN', 60), ('CV0047', 'MTC-30CRD-06-14', 1176), ('WJ0091', '1347007', 84480), ('CF0022', '6903ZZ', 620), ('WJ0009', 'L-730ZZ', 2352), ('FT0007', 'AFF+70G', 7120), ('JR0008', '6206ZZ-NSK', 280), ('FT0006', '6001-H-20T1XZZ*MA', 2336), ('R20016', 'SHS30LRSS', 13500), ('WJ0090', 'AFA+400G', 108000), ('WG0012', 'CF-6UUA', 580), ('WJ0090', 'SHS30R2UUFME-760LF', 37056), ('9R0003', 'SHS25CSS', 18800), ('XC0007', 'SKR2602A-0060-P0-00A0', 34980), ('WG0012', 'L-1960ZZ', 276), ('WG0012', 'L-1360ZZ-JIB', 336), ('920004', 'Z12', 65), ('WG0012', 'LF-740ZZ-JIB', 864), ('WG0012', '126', 280), ('5C0047', 'R-BRF1208', 4000), ('CV0004', 'AFF+70G', 8400), ('WJ0009', '6800ZZ-NTN', 486), ('WJ0104', '6005ZZ-NSK', 1460), ('FT0007', '6804ZZ-NSK', 9000), ('QD0001', 'C-8', 800), ('WJ0100', 'POM-6000-PA-GS', 1440), ('CW0015', 'LM-12UU', 1630), ('FG0009', 'GRS-LR3', 716), ('WJ0090', '6908ZZ-NTN', 720), ('WJ0009', 'L-1060ZZ', 180), ('U20007', '020-MA5-0723E-FY', 15540), ('U20011', 'RBI-20D-HIJ', 2445), ('FM0014', '2214M-FAG', 3100), ('MT0024', 'MTC-40CRD', 4500), ('9R0003', '6905-H-20T1XZZ*MA', 26300), ('U20010', 'SSR20XWUU', 8100), ('RU0033', '6902ZZ-NTN', 480), ('XU0007', 'L-2280ZZ', 104), ('FT0007', 'WBK06-11', -2730), ('WJ0090', 'AFA+400G', 144000), ('FT0007', 'AFF+70G', 4272), ('U20032', 'BTK2510V-5.3ZZ-679LC7T', 50760), ('WJ0091', 'RNFCL5050A6S-2272LT', 60000), ('U20007', '6804-H-20T1XZZ*MA', 4725), ('CW0015', 'AFE-CA+70G', 3100), ('WJ0091', 'HSR8R1UUM-75LM', 1924), ('MW0015', 'MG80', 800), ('R20016', 'SHS35CSS', 12000), ('WJ0104', '6003ZZ-NSK', 600), ('WG0012', 'L-730ZZ-JIB', 160), ('WJ0009', 'L-840ZZ', 168), ('FT0007', 'AFF+70G', 712), ('WJ0163', 'L-1960ZZ', 400), ('U20007', '1SRS7MUU-55LM', 7500), ('U20007', '6920VV-NTN', 3800), ('T50004', 'Z12', 70), ('CW0012', 'LM-50GA', 6600), ('CF0022', 'LF-1360ZZ', 2640), ('BN0002', 'AFE-CA+70G', 3360), ('CM0005', '6205ZZ-NSK', 172), ('RX0022', 'MR12MNV0N', 1040), ('WG0012', 'L-1960ZZ-JIB', 92), ('UA0011', 'SHS35LVSS', 15456), ('XU0007', 'L-2280ZZ', 1040), ('MV0021', 'L-740ZZ', 2112), ('CA0006', 'AFC+70G', 71250), ('FT0007', 'AFF+70G', 2136), ('CJ0004', 'CF-3A', 1275), ('WJ0009', 'L-1680ZZ', 80), ('WJ0094', '6800-H-20ZZ*MA', 3160), ('FT0007', 'AFF+70G', 10680), ('RE0001', 'AFE-CA+70G', 17100), ('WJ0091', 'R20-20A1-FSW-410-520-0.018', 10650), ('VU0021', 'GRS-LGU', 5700), ('FU0029', 'Z12', 40), ('FT0007', 'AFF+70G', 8900), ('VZ0012', 'Z12', 65), ('VR0023', 'AFB-LF+70G', 2300), ('JR0008', 'LMK-10UU', 4160), ('EV0012', 'SSL-950ZZ', 750), ('CM0005', 'L-2280ZZ', 320), ('RX0013', 'LMC-20UU', 450), ('WG0012', 'SSL-1980ZZ-YCB', 1296), ('WJ0178', 'GRS-LG2', 6000), ('WG0012', 'L-1360ZZ-JIB', 112), ('JR0008', 'CB-0606-12T', 840), ('WJ0091', 'SHS45VSSHHC0FMS', 41600), ('WJ0091', 'ST-16B', 1480), ('WG0012', 'LF-1980ZZ-JIB', 220), ('ZT0001', '2FBW50110XRUU-800L', 11960), ('FT0007', 'RA-12008CUUC0', 29000), ('FT0007', 'MDW-40C-12X17', 3820), ('FT0007', 'AFF+70G', 35600), ('WJ0090', '6202-H-20T1XZZ', 170), ('FT0007', '1666-113-22', 2990), ('WJ0009', 'L-940ZZ-JIB', 168), ('XU0007', 'RA-12008UUC0', 87395), ('FT0007', 'AFF+70G', 71200), ('JR0008', 'NTB-1024', 450), ('AU0005', 'CFT-20M', 9520), ('Z20004', 'SSR20XWSS', -4680), ('MA0010', 'PSS1520N1D1179', 64125), ('WJ0018', '6201ZZ-NSK', 260), ('BM0002', 'AFC+70G', 2050), ('RJ0006', 'T-1810', 7000), ('CR0003', 'BGXH25FN-2-L1186-N-Z0', 17704), ('FT0008', '6802ZZ-NSK', 567), ('FT0006', 'W0800MA-1PY-C3Z1', 27990), ('WG0012', 'L-1360ZZ-JIB', 112), ('XU0007', 'L-2280ZZ', 1950), ('WX0011', 'Z11', 625), ('UR0022', 'Z18', 2100), ('5Z0011', 'Z11', 955), ('WJ0090', '1623-114-20', 49000), ('CA0020', 'L-1470ZZ', 450), ('CR0003', 'SFIR3205-4-819L-DF-C7', 5175), ('WJ0090', 'SHS20CSSFM', 14720), ('CM0005', 'CRM-6902-PT-ZR2P', 11904), ('WJ0091', 'SS6203-2RS', 6570), ('FT0007', 'SHS45LV2UUFME-3610LTF', 190380), ('EX0002', 'AFC+70G', 2800), ('VE0020', 'NIPPLE-A-M6F', 80), ('WJ0009', 'CRT6-12', 384), ('FT0007', 'GSR35T5UUFE-7600LHRTF', 167680), ('FT0007', 'AKS-25W', 21300), ('WG0012', 'AN04', 184), ('FT0007', 'SW-70-I', 19200), ('XM0009', 'NTB-3552', 220), ('ER0006', 'HGP-NZ3', 2100), ('WJ0075', 'MG70-P', 1750), ('WJ0009', 'NTB-1528', 96), ('WJ0091', 'SHW14CR2UUM-250LM', 10422), ('EV0012', 'SS6302ZZ', 1560), ('X20016', 'SMK-10WUU-E-SK', 9000), ('WG0012', 'L-1360ZZ-JIB', 224), ('M10002', 'Z12', 65), ('WJ0090', '6203-H-20T1XDD*MA', 14400), ('WJ0090', 'SHW14CR2UUM-210LM', 53400), ('WG0012', 'LF-740ZZ-JIB', 1152), ('RT0028', 'BGCS20BS-1-L900-N-Z0', 3800), ('FG0009', 'GRS-LR3', 1790), ('WJ0091', 'SGL35TF2-3000-W2LB', 93800), ('CF0004', 'GRS-LGU', 11000), ('RE0001', '6000ZZ-KOYO', 1100), ('WJ0009', '6901ZZ', 120), ('CW0012', 'LM-50GA', 6600), ('WJ0091', 'PSL-G-60', 2900), ('FT0007', 'NV2075-19Z', 18000), ('RJ0006', 'L-1910ZZ', 4800), ('WJ0091', 'RB-4010UUCC0', 45876), ('FT0008', 'AFF+70G', 3560), ('FM0014', 'HK-2001-A', 33600), ('FT0007', 'NKX25T2Z', 2460), ('9R0004', 'SHS30CSS', 22900), ('WG0012', 'LMF-6LUU', 4140), ('WJ0090', '6900-H-20T1XDD1-MA', 1300), ('GU0037', 'MHC-57C-24*25', 4200), ('EV0012', '7304A', 2680), ('5Z0011', 'L-1060ZZ', 336), ('XM0009', 'CF-8MUURA', 3760), ('FG0008', 'GRS-LR3', 1074), ('FT0008', 'AFF+70G', 3916), ('CA0006', '6802ZZ-NSK', 3650), ('WJ0104', 'UCFL205-ASAHI', 8700), ('F10002', 'SRS12MUU', 4320), ('TZ0011', 'HGP', 2590), ('ZM0006', '608ZZ-EZO', 1020), ('WJ0075', '6904-2NSE-NACHI', 2240), ('JR0008', 'F6900VV-YCB', 200), ('CR0003', 'BGXH25FN-2-L1186-N-Z0', 17704), ('WG0012', '51100-YCB', 210), ('FG0008', '6202ZZC3-NTN', 2550), ('WJ0222', 'AFC+70G', 580), ('WJ0091', '6004-H-20T1XZZ', 4640), ('WG0012', 'SS6901ZZ-YCB', 800), ('WJ0090', '6006-H-20T1XDDU*MA', 19250), ('V20015', 'GRS-LGU', 4100), ('FG0008', 'GRS-LR3', 358), ('WJ0018', '6308ZZ-NSK', 700), ('WG0012', 'CF-3UUA', 426), ('WJ0114', 'CF-8UUA', 2880), ('WJ0094', 'L-630ZZ', 1692), ('CA0006', '6802ZZ-NSK', 3650), ('CA0006', 'SMR106ZZMC3*MAZ', 130), ('CT0014', 'SFP10-350L', 4340), ('WJ0090', 'DIK2005F6WWG0-240C5F', 22350), ('U20007', 'GSR25TUU', 48600), ('VG0002', '7001A', 2550), ('FT0006', 'AFB-LF+70G', 300), ('VT0004', 'CB-1615-24T', 180), ('5X0011', 'CF-4BUU', 2300), ('WJ0009', 'TLA-2526', 95), ('CC0020', 'AFE-CA+70G', 5600), ('WJ0090', 'L100-400G', 19500), ('2C0017', 'AFF+70G', 580), ('WJ0090', '7908UCG/GNP4', 105000), ('FT0006', 'AFB-LF+70G', 300), ('WJ0009', 'A-2520', 390), ('U20007', 'NRS45LRX6UUFME-7800LTFE', 276000), ('FT0007', 'FK-30', 14600), ('CA0006', '6802ZZ-NSK', 3650), ('RM0027', 'DPM-2550', 3975), ('MA0010', 'MR7WNSSV0N', 7280), ('BM0002', '6001-2RS-NTN', 2250), ('WJ0009', 'L-310', 2400), ('VU0041', 'PSP2505N3AB0499B', 40820), ('ER0005', 'CF-4A', 1275), ('WG0012', 'SS6801ZZ-YCB', 750), ('WJ0091', 'GRS-LG2', 17600), ('MT0024', 'MTC-30CRD-10K4-10K4', 750), ('MT0024', 'SHS20V2SS-1530L', 31560), ('WJ0090', 'SHS20C2ZZFMJ-460FJII', 42600), ('FT0007', 'AFF+70G', 2848), ('U20032', '62/22DDU', 1530), ('WG0012', 'SFP6-130L', 128), ('JA0004', 'GRS-LGU', 4632), ('FT0006', 'AFB-LF+70G', 900), ('BM0002', 'AFC+70G', 1230), ('U20012', 'RB-18025UUC0P5-6', -4665), ('WJ0090', 'BK-25', 7500), ('VV0010', 'HK-0810', 90), ('UA0011', 'FK-6', 3088), ('CT0007', 'HSR35LCSS', 53000), ('5Z0011', '6911ZZ-KOYO', 1788), ('FT0007', '30TAC62CSUHPN7C', 45432), ('WJ0090', '2R050-50A1-1FSKT-3655-3925-0.018H', -295000), ('CV0047', 'MTC-65CRD-25-35', 1890), ('XC0007', 'SKR2602A-0060-P0-00A0', 34980), ('RE0001', '30205-NSK', 1140), ('XA0008', 'GRS-LR3', 7200), ('WJ0100', 'TLA-810', 900), ('FT0007', 'NH451200BND2KCZ1AL', 92480), ('1F0002', 'NHS-3T', 1040), ('UC0018', 'AFF+70G', 1920), ('XA0008', '6901ZZ-NSK', 3000), ('MT0024', 'SSR20XW2SS-3335LT', 24339), ('WJ0090', 'WGF50100FM-3ZZG3-3300LC7TF', 308228), ('WJ0009', 'LF-1060ZZ', 488), ('FT0007', '6904-H-20T1XDDU*MA', 2128), ('WJ0094', 'L-1350ZZW05', 440), ('TR0009', '6807ZZ-NTN', 2000), ('UW0004', 'HSR12R1UUM-230LM', 5070), ('WJ0091', 'VRT-1025AM', 88000), ('RE0001', '6205VV-NSK', 2000), ('U20032', 'FK-10', 3250), ('BM0002', 'NAH25BNZ', 5660), ('CM0005', '6004-H-20T1XDDU*MA', 5300), ('WJ0100', 'L-1470ZZ', 180), ('WJ0009', 'L-1060', 360), ('MG0018', '6203-2NSE', 1200), ('RX0022', 'MR12MNV0N', 1040), ('FT0007', 'SS6001ZZ', 8700), ('FT0007', 'NS251480ALD2PCZ1AL', 46960), ('FT0006', 'LF-1060ZZ-NMB', 480), ('VU0021', 'GRS-LGU', 3800), ('WJ0100', 'SS6002ZZ', 1140), ('WJ0090', '1623-114-20', 49000), ('WJ0009', 'L-1560ZZ', 76), ('R20016', 'SHS30-840L', 4040), ('A-0002', 'AFE-CA+70G', 10800), ('CJ0004', 'AFC+70G', 10000), ('XU0007', 'RA-7008UUCC0', 3900), ('WJ0090', 'BLK3636-7.2ZZ', 30400), ('RE0001', '6004VV-NSK', 1020), ('X20013', 'LM-8S', 6700), ('WG0012', '6815ZZ-FBJ', 2000), ('5X0011', 'SS6002ZZ-FBJ', 4620), ('CF0022', '7005CTYNDBLP5', 7500), ('FT0007', 'AFF+70G', 8900), ('WG0012', 'L-1960ZZ', 552), ('WJ0091', '6203-H-20T1XZZ*MA', 2450), ('WJ0114', '0455-504-31-75L', 1230), ('U20032', 'PP-STK-CARRIAGE昇降軸包膠導輪組', 16580), ('FT0006', 'AFF+70G', 10740), ('FT0006', 'AFF+70G', 35600), ('WG0012', '5200-IJK', 560), ('CM0005', 'SS6804ZZ-FBJ', 131760), ('WG0012', 'L-1960ZZ', 184), ('WJ0091', 'HSR25R2SSFMS-340LFM', 26600), ('WJ0108', 'BSR1530SL', 2600), ('FT0007', '6828VV', 4950), ('U20007', 'TU65', 2500), ('VE0020', 'GRS-LG2', 3200), ('WJ0009', 'LF-1260ZZ', 300), ('WG0012', 'L-1360ZZ-JIB', 1344), ('WJ0090', 'L-1350ZZ', 230), ('WJ0091', 'SHS25VSS', 9072), ('9R0004', 'SBN3210V-7WWG0-1230LC5-J2K', 48000), ('WG0012', 'LF-740ZZ-JIB', 1536), ('UA0011', '6904ZZ-NSK', 5880), ('2D0002', 'CF-5UURA', 3700), ('WJ0009', 'RBI-6D', 441), ('VE0020', 'NIPPLE-B-MT6X1', 60), ('FT0006', '7204BW', 2430), ('WJ0091', '6207-H-20T1XZZ*MA', 658), ('FT0007', 'RB-7013UUC0', 38000), ('MV0021', 'LF-1790ZZ', 8432), ('2F0006', 'AFC+400G', 6300), ('WJ0104', 'H2308', 332), ('WJ0091', '1532-2-2016-1531-2-2900-92L', 41600), ('WJ0090', 'SSR20XTB2SS-300L', 7924), ('9R0003', 'SSR25XW6QZDDME-1900LYM', 158400), ('CR0003', 'BGXS20BN-1-L466-N-Z0', 1300), ('ZM0006', 'L-1480ZZ', 520), ('U20008', 'SSR35XW2UUFM-4910LTF', 104000), ('CV0023', 'GRS-LG2', 4350), ('FT0007', '6908ZZ-NSK', 1800), ('520008', '608VV-NSK', 30), ('WJ0091', 'HSR8R2UUM-75LM', 3310), ('WJ0100', 'L-1680ZZ', 920), ('WJ0090', 'SHS30V1SSFM-680LF', 20700), ('WJ0090', 'RSR15M1WVUUS-94LS', 27320), ('F10002', 'PSS2040N1D0658', 38000), ('XU0007', 'RB-2508UUCC0', 11600), ('WJ0009', '6206ZZ-NSK', 1380), ('FT0007', '1-KSH-32WL-60-500', 132000), ('FU0005', 'AFB-LF+70G', 4600), ('FT0001', 'Z12', 40), ('UR0022', 'Z18', -3300), ('BM0002', 'CF-4WBUUR', 6240), ('WJ0090', 'GSR30TKK', 11600), ('AR0012', '洗淨機軸承', 40937), ('XM0007', '6304ZZ-NSK', 2700), ('MC0007', 'Z12', 70), ('WJ0130', 'LMC-20LUU', 1560), ('9R0004', 'HSR25M1B', 122600), ('MT0024', 'SSR15XW1SS-450LY', 8180), ('FT0007', 'RA-5008CUUC0', 17200), ('WJ0090', 'BIF1605FM-5RRG0-586LC7F', 23450), ('CA0006', '6802ZZ-NSK', 3650), ('CA0006', '6802ZZ-NSK', 3650), ('FT0006', '6800ZZ-NSK', 1960), ('U20010', 'HSR15RSS', 1835), ('JX0002', '6206ZZ-FBJ', 520), ('CJ0004', 'MG70-U', 520), ('WJ0009', 'L-830ZZ', 100), ('CM0005', '6005-H-20T1XZZ*MA', 992), ('WJ0009', 'L-1050ZZ', 160), ('FT0007', 'AFF+70G', 7120), ('U20007', '6804ZZ-NSK', 3720), ('WG0012', 'RB-35020UUCC0P4', 156000), ('U20007', 'HSR25LR2QZSSM-1060LHM', 88000), ('ER0006', 'GRS-PS2', 2100), ('C50004', 'GRS-LR3', 1680), ('WJ0091', 'AFF+400G', 8700), ('FG0008', '6900VV-NSK', 210), ('FT0007', '1-KSH-32WL-60-500', 66000), ('SY0002', 'ADVU-16-20-P-A', 4900), ('JV0011', 'GRS-PS2', 380), ('WJ0131', 'SHS15V1SS-940L', 8100), ('WJ0094', 'L-1650ZZ', 175), ('WJ0090', 'HSR25R3SSFME-4540LMTF-AP-CF', 180000), ('5C0018', 'MG70', 2300), ('WJ0090', 'BIF1810EFM-3RRG0-1123LC5FM', 198500), ('MT0024', 'MTC-40CRD-10K4-14K5', 1125), ('WJ0009', '6808ZZNR', 850), ('WJ0009', '6900ZZ-NSK', 120), ('WJ0009', 'GS-1528', 152), ('EV0012', 'L-1360ZZ', 1200), ('UW0022', 'GRS-PS2', 340), ('R20016', 'FK-10-OAMG', 78000), ('WG0012', 'LF-1980ZZ', 220), ('EX0002', 'VR1-60H-16Z', 11850), ('FG0010', 'GRS-AS2', -620), ('WJ0094', 'CFS-04FV', 4550), ('EV0012', '6802ZZ', 1500), ('WJ0100', 'NAS15CLZ', 7400), ('XV0020', 'NTB-3047', 850), ('XU0007', 'MDK1002FM-3RRG2+125LC7F', 47600), ('CM0005', '6004-H-20T1XDDU*MA+NR', 4656), ('AG0013', '6218-KOYO', 1580), ('WJ0091', 'LMH-10LUU', 2040), ('WJ0091', 'AFE-CA+400G', 2650), ('XU0007', 'RA-7008UUC0', 2600), ('FG0008', 'GRS-LR3', 716), ('MT0024', 'MHC-40C-15K5X-16K5', 925), ('CW0015', 'LM-8S', 1644), ('WJ0095', 'LF-1980ZZ', 100), ('R10009', 'POS-12', 7350), ('WJ0090', '6802ZZ-NSK', 1800), ('WG0012', 'L-1360ZZ-JIB', 112), ('WJ0009', '6801ZZ', 126), ('CF0022', 'NV2045-9Z', 27020), ('WJ0018', '6204ZZ-NSK', 190), ('CR0003', 'BGXS20BN-1-L466-N-Z0', 3900), ('FT0008', 'AFF+70G', 17800), ('FT0008', '7003AW', 1050), ('R20016', 'CF-20-1B', 3500), ('WJ0090', 'SS6909-2RS', 7760), ('FT0007', '6004-H-20T1XDDU*MA', 6660), ('CF0022', 'VR3-75H-10Z', 35805), ('9R0003', 'SHS30R1SS-1960LP', 19920), ('WG0012', 'MHC-27C-8-8', 1740), ('FA0019', 'LM-10GAUU', 4400), ('5Z0011', 'Z12', 40), ('WJ0104', 'Z18', 2070), ('CM0005', '608-H-20T1XZZMC3ZMA', 750), ('WJ0100', 'NAS15ALZ', 8360), ('VU0021', 'AFC+70G', 11000), ('RA0009', 'LWES25C1S1', 10500), ('TM0006', 'HSR35CSS', 13320), ('WJ0090', '1532-4-2006-1531-4-2900-252L', 19040), ('WJ0009', 'VR4-80H-7Z', 14180), ('R20016', 'FK-08-OAMG', 36300), ('WJ0221', 'UC215-NTN', 2900), ('RE0001', 'R6ZZ-NSK', 1700), ('R20016', 'GSR25-1600L', -79000), ('VU0004', 'GRS-AS2', 600), ('5Z0011', 'BTK1404VFM-3.6ZZ-202LTF-H1K', 15500), ('WJ0090', '2R050-50A1-1FSKT-4930-5200-0.018H', 330000), ('250007', '6000ZZ-NSK', 5200), ('MT0024', 'WTM-05C', 1950), ('WJ0090', 'AFF+400G', 580000), ('U20007', 'HSR20M1A', 29600), ('WG0012', '51100-YCB', 210), ('WJ0114', '6001ZZ-NACHI', 640), ('VA0003', 'L-740ZZ-JIB', 185), ('WJ0091', 'AFF+400G', 8700), ('R20016', 'BTK2005V-2.6ZZ-1000LC7', 14148), ('UC0018', 'AFF+400G', 3350), ('UC0018', 'VR4-80H-7Z', 13520), ('R20016', 'GSR25-1500LR', 106600), ('WJ0090', '1653-294-20', 6000), ('WJ0009', 'NTB-2542', 335), ('WJ0091', 'EGR20R1135C', 5320), ('FT0007', 'SEBS5BYUU1-47/W2', 12300), ('5E0002', 'GSR35TUU', 36000), ('BM0002', 'MFB-20', 1305), ('FG0008', 'GRS-LR3', 358), ('VU0041', '6004-KOYO', 342), ('CU0009', 'GRS-LR3', 5760), ('WJ0100', '6002ZZ-NSK', 2600), ('WJ0091', 'SHS20V1SSFE-220LF', 4800), ('WJ0099', '6202ZZ-KOYO', 530), ('MT0024', 'MTC-40CRD-10K4-14K5', 1125), ('U20008', 'EGH20CAZ0C', 2700), ('MT0024', 'MTC-55CRD-22K6-25K8', 1450), ('WJ0100', '6000ZZ-NSK', 4700), ('WJ0009', 'LF-1560ZZ-JIB', 232), ('WG0012', 'LF-1980ZZ', 220), ('CR0003', 'SFIR3205-4-819L-DF-C7', 10350), ('R20016', 'GSR25TUU', 57480), ('U20032', 'BLK2020F-7.2ZZ-1850LC7F', 27000), ('FG0010', 'LM-5', 1560), ('WJ0108', 'MGN12H1R90Z0PM', 5660), ('ZM0006', 'L-1480ZZ', 520), ('WJ0114', '6801ZZ-NSK', 1170), ('WG0012', 'SSL-1980ZZ-YCB', 486), ('WJ0090', 'SHS20CSSFM', 14720), ('WJ0009', 'CRT-1680', 176), ('2;0001', 'HSR25A2SS-660L', 12900), ('ZM0006', 'L-1260ZZ', 220), ('VC0010', '2SRS15MUU-310LM', 9000), ('VJ0002', '7202BW', 4940), ('WJ0100', '6004-KOYO', 220), ('WJ0090', '6202-H-20T1XDDU*MA', 55500), ('5Z0011', '40J-SER-48-LINK', 870), ('WJ0091', 'AFF+400G', 2900), ('CA0020', 'L-1260ZZ', 500), ('WJ0090', '6815VV-NSK', 4000), ('WJ0009', 'L-1280ZZ', 188), ('RE0001', '6004VV-NSK', 2125), ('AX0013', 'Z12', 65), ('AR0007', 'NAH45ANNPCZ1AA', 27600), ('2F0006', 'AFC+400G', 6300), ('XM0009', 'SFP6-190L', 3700), ('JR0008', 'CB-0606-12T', 840), ('JR0008', '6900ZZ-YCB', 250), ('NV0002', 'LF-1360ZZ', 534), ('A10001', 'Z11', 560), ('WJ0090', '1315655', 280000), ('U20007', '020-MA5-0723E-FY', 5180), ('WG0012', '126', 280), ('5C0018', 'AG1.5-20R1J10', 5985), ('WG0012', 'CF-3UUA', 1278), ('WJ0090', 'BLK3636FM-7.2QZZZ-1662LTF', 452275), ('FT0007', 'AFF+70G', 2492), ('WJ0009', 'NBJ01-8-72-H5', 3600), ('WJ0090', '1314117', 300000), ('XA0008', 'AFC+70G', 2750), ('XR0020', 'AFA+70G', 2700), ('WJ0009', 'LF-1150ZZ', 840), ('GG0004', 'AFA+70G', 2900), ('WJ0009', 'NTB-1024', 336), ('U20007', '1-KSH-24L-42-40', 4000), ('U20010', '6804ZZ-NSK', 4650), ('U20008', 'HR2042UUM', 51300), ('520006', '7007AW', 2765), ('WJ0009', 'LF-1680ZZ', 75), ('WJ0009', 'L-1060ZZ', 540), ('A-0002', 'Z12', 65), ('EV0012', 'XGL2-30C-8-14', 2150), ('CM0005', '6218LLU', 7200), ('FT0007', '6900-H-20T1XDD1-MA', 1250), ('XU0007', 'RA-5008UUCC0', 48960), ('CR0003', 'BGXH25FN-3-L1366-N-Z0-23-23', 11800), ('MT0024', '6006ZZ-YCB', 160), ('DS0003', 'SFP6-500L', 7275), ('WG0012', 'L-1360ZZ-JIB', 672), ('2X0008', 'GRS-PS2', 760), ('CV0032', 'VR2-45H-8Z', 2900), ('JR0008', 'CF-5UUA', 1176), ('WJ0009', 'L-1560ZZ', 152), ('5C0018', 'HGP-NZ3', 1050), ('WJ0104', '6012ZZ-KOYO', 6500), ('WJ0009', 'LF-1910ZZ', 616), ('WG0012', '7202-NACHI', 1600), ('CF0022', 'NV2045-9Z', 30880), ('9R0004', '6901-H-20T1XZZ*MA', 3100), ('RR0001', 'MHC-40C-8*10', 1650), ('FT0007', '6908ZZ-NSK', 1500), ('WJ0090', '6003-H-20T1XDDU*MA', 49200), ('5Z0011', 'BNF2010AFM-1.5RRG1-360LC7F', 12420), ('T50004', 'LM-40UU-HST', 1360), ('A-0002', 'AFE-CA+70G', 15680), ('BM0002', 'AFF+70G', 3920), ('WG0012', 'LM-6LUU', 1640), ('ZU0005', 'CHROME-2.5M/M', 25), ('WG0012', '1200-IJK', 360), ('U20007', '020-MA5-0723E-FY', 5180), ('WJ0091', 'AFF+400G', 2900), ('ER0005', '6904ZZ-NSK', 570), ('WJ0090', '6001-H-20T1XDDU*MA', 52000), ('U20008', 'VRU-2080', 38000), ('WJ0090', 'SSR20XWUU', 4126), ('WJ0009', 'L-1910ZZ', 54), ('FU0029', '6900-H-20T1XDD1-MA', 1600), ('WJ0009', 'L-1960ZZ', 180), ('WJ0009', 'LF-1350ZZ', 240), ('FT0006', 'PM2.5-20', 40200), ('CF0022', 'LF-1360ZZ', 2310), ('CW0015', 'LF-1450ZZ', 60), ('U20007', 'CF-10-1BUU-IKO', 4680), ('WJ0009', 'AS-1024', 120), ('1B0003', 'TLA-1010', 630), ('FT0007', 'AFF+70G', 14320), ('XC0004', 'MFCS-20C-6.35-8', 950), ('WJ0090', '2R050-50A1-1FSKT-4630-4900-0.018H', -310000), ('U20007', 'CF-12-1UUAB', 3950), ('UY0004', 'HCR15A2UU+60/150R-3T', 50350), ('U20008', 'SHS30C2SSC0F-550LF', 116000), ('WJ0100', 'POM-6900-PA-GS', 2850), ('RC0056', 'Z18', -3600), ('WJ0091', '6004-H-20T1XZZ', 3712), ('RE0001', 'Z12', 40), ('CW0015', 'PSS2040N1D0505', 26230), ('ER0006', 'GRS-PS2', 2100), ('RE0001', '6205VV-NSK', 1200), ('CR0003', 'BGXH25FN-2-L886-N-Z0', 6724), ('CR0003', 'BGXS20BS-1-L286-N-Z0-23-23', 1880), ('WJ0104', '6309ZZC3-NSK', 5500), ('U20007', 'TU65', 2500), ('FT0007', 'MG70', 4400), ('U20032', '1-KSH-24WL-42-200', 10245), ('FT0006', 'AFB-LF+70G', 2250), ('5C0018', 'AFF+70G', 4500), ('2D0002', 'CFS-03VA', 13600), ('WJ0009', 'LF-1150ZZ', 1120), ('WJ0091', 'EGH20SAZAH', 2260), ('FM0014', '7002AW', 1040), ('WJ0091', 'SHS20C2SS-3200LF', 80200), ('WG0012', 'L-1360ZZ-JIB', 448), ('WJ0104', '6214P5-NSK', 11200), ('CA0020', 'L-1360ZZ(686)', 900), ('FT0008', 'AFF+70G', 5340), ('FU0029', 'L-1560ZZ', 240), ('FT0007', 'AFF+70G', 10680), ('9D0001', 'GRS-PS2', 7600), ('AT0006', 'GRS-PS2', 1400), ('VE0004', '6000ZZ-NSK', 65), ('WJ0091', '0443-293-01', 1390), ('ZM0006', '6904ZZ-NSK', 440), ('CW0015', 'LM-12UU', 1630), ('BM0002', 'CF-03FBUU', 13000), ('FT0007', '6003ZZ-NSK', 1000), ('WJ0009', 'L-1350ZZ', 688), ('CR0003', 'BGXS20BS-1-L466-N-Z0', 1040), ('UU0009', '6000ZZ-NSK', 570), ('WJ0009', 'NTB-1024', 168), ('WJ0104', 'Z18', -8360), ('MT0024', 'SSR15XW1SS-640LY', 9380), ('DA0002', 'GRS-LR3', 7200), ('WG0012', 'LM-6LUU', 1640), ('WJ0090', 'LRR45MNS6CV0P-8550L-22.5-22.5-II-J-RRB', -314000), ('WG0012', 'L-1360ZZ-JIB', 112), ('VR0023', 'MG70', 5600), ('RC0004', 'SFP25-950L', 3400), ('U20010', 'HSR15M1R', 36000), ('WJ0009', 'L-1910ZZ', 432), ('WJ0100', 'BK-4615A', 945), ('UC0018', 'BARRIERTA-IS/V-75G', 29370), ('U20007', '33007-NSK', 3720), ('FT0007', '6904-H-20T1XDDU*MA', 106400), ('WG0012', 'L-730ZZ-JIB', 160), ('RR0035', 'SRS9WMUU', 2320), ('UC0018', 'LM-20UU-YCB', 500), ('UV0009', 'SSL-850ZZ', 3250), ('WJ0094', 'L-1350ZZW05', 440), ('9R0004', '6204-H-20T1XADDU*MA', 7350), ('WJ0091', 'MUCP205-ASAHI', 30520), ('JR0008', '6204ZZ-NSK', 160), ('WG0012', 'L-1960ZZ-JIB', 92), ('MV0021', 'LF-1060ZZ-NMB', 6300), ('FT0008', '7204BW', 2916), ('FT0007', '6904-H-20T1XDDU*MA', 2650), ('WJ0104', '6205ZZ-NSK', 800), ('WJ0090', 'Z11', 6000), ('U20007', 'BLK2525F-7.2ZZ-402LTFG-JIK-4', 4110), ('WJ0131', 'HSR12R1UUC1M-430LM', 3600), ('BM0002', '2SRS12MUU-142LM', 16332), ('FT0008', 'AFF+70G', 3560), ('VX0013', 'AFB-LF+70G', 1200), ('JR0008', 'SSA-2216', 1275), ('CM0005', 'SS6004ZZ-KSK', 15300);

我自己的答案是

WITH temp2 AS (
	WITH temp1 AS (
		SELECT
			temp.custid,
			temp.productid,
			SUM( temp.amount ) AS amount 
		FROM
			temp,
			(
			SELECT
				@custids := (
				SELECT
					group_concat( custid ) 
				FROM
					( SELECT temp.custid FROM temp GROUP BY temp.custid ORDER BY ( Sum( temp.amount )) DESC LIMIT 10 ) a 
				)) b,
			(
			SELECT
			@productids := ( SELECT GROUP_CONCAT( DISTINCT temp.productid ) FROM temp WHERE find_in_set( temp.custid, @custids ) )) d 
		WHERE
			FIND_IN_SET( temp.custid, @custids ) 
			AND FIND_IN_SET( temp.productid, @productids ) 
		GROUP BY
			temp.custid,
			temp.productid 
		) SELECT
		custid,
		productid,
		amount,
		(
		DENSE_RANK() OVER ( PARTITION BY custid ORDER BY amount DESC )) AS `Rank` 
	FROM
		temp1 
	) SELECT
	*,
	( SELECT sum( temp3.amount ) FROM temp2 AS temp3 WHERE temp3.custid = temp2.custid ) AS CustTotal 
FROM
	temp2 
WHERE
	`Rank` < 11 
ORDER BY
	5 DESC,
	`Rank`

800多個字元,我覺得實在有點冗長,不知如何改進?
有興趣的先進玩一下吧。

石頭 iT邦高手 1 級 ‧ 2021-07-28 23:15:53 檢舉
請問一下 你qeury跟你的描述好像對不太上耶?
石頭 iT邦高手 1 級 ‧ 2021-07-28 23:16:48 檢舉
因為我用你的查詢只找出 6間公司

下面解法我是依照你的描述 寫出的答案
ckp6250 iT邦好手 1 級 ‧ 2021-07-29 06:16:07 檢舉
我再測試了一回,是10間公司沒錯呀。
會不會是 MySql 版本問題?
我用的是mariadb 10.3
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中
4
rogeryao
iT邦大師 1 級 ‧ 2021-07-29 09:03:37
最佳解答
SELECT *
FROM (
SELECT *,Dense_Rank() OVER (ORDER BY SUBTOTAL DESC,custid) AS NO2
FROM (
SELECT custid,productid,amount,
SUM(amount) OVER (PARTITION BY custid) AS SUBTOTAL,
ROW_NUMBER() OVER (PARTITION BY custid ORDER BY amount DESC) AS NO
FROM (
SELECT custid,productid,SUM(amount) AS amount
FROM temp
GROUP BY custid,productid
) AS K
) AS Q
WHERE Q.NO<=10) AS S
WHERE S.NO2<=10

Demo

看更多先前的回應...收起先前的回應...

原來MySQL也支援這樣的函數~

ckp6250 iT邦好手 1 級 ‧ 2021-07-29 10:22:58 檢舉

真是精妙啊,佩服之至。

SUM(amount) OVER (PARTITION BY custid) AS SUBTOTAL,

我沒想到還可以這樣用。

rogeryao iT邦大師 1 級 ‧ 2021-07-29 10:25:06 檢舉
dscwferp iT邦高手 1 級 ‧ 2021-07-29 10:29:04 檢舉

good

0
石頭
iT邦高手 1 級 ‧ 2021-07-28 23:17:19

可以使用subquery + window function來完成

SELECT t1.custid,
      t1.productid,
      t1.amount
FROM (
  SELECT *,ROW_NUMBER() OVER(PARTITION BY custid ORDER BY amount desc) rn
  FROM temp
) t1
INNER JOIN (
  SELECT custid,
       ROW_NUMBER() OVER(ORDER BY total desc) rn
  FROM (
   SELECT custid,SUM(amount) total
   FROM temp
   GROUP BY custid
  ) t2
) t2 ON t1.custid =t2.custid 
WHERE t2.rn <= 10 AND t1.rn <= 10

update version

WITH cte as (
 SELECT custid,SUM(amount) amount,productid
 FROM temp
 GROUP BY custid,productid
)
SELECT t1.custid,
      t1.productid,
      t1.amount
FROM (
  SELECT *,dense_rank() OVER(PARTITION BY custid ORDER BY amount desc) rn
  FROM cte
) t1
INNER JOIN (
  SELECT custid,
       dense_rank() OVER(ORDER BY total desc) rn
  FROM (
   SELECT custid,SUM(amount) total
   FROM cte
   GROUP BY custid
  ) t2
) t2 ON t1.custid =t2.custid  
WHERE t2.rn <= 10 AND t1.rn <= 10

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=23c6b125671de592c6caafa6c504bccc

看更多先前的回應...收起先前的回應...
丹尼爾 iT邦研究生 2 級 ‧ 2021-07-29 00:44:22 檢舉

應該用 RANK() 求出排名吧~

ckp6250 iT邦好手 1 級 ‧ 2021-07-29 06:13:33 檢舉

感恩,
您的答案很精巧,正是我想要的展現模式,只是我想不出來。
但,答案好像不對哦,

WJ0090 的 AFF+400G,金額應該是 1,305,000
而不是 580,000,
請驗算一下。

石頭 iT邦高手 1 級 ‧ 2021-07-29 09:05:20 檢舉

丹尼爾 RANK()和ROW_NUMBER 理論上會一樣 如果是要同名次RN一樣要用dense_rank

石頭 iT邦高手 1 級 ‧ 2021-07-29 09:23:08 檢舉

ckp6250 我改好答案了 你在試試看

ckp6250 iT邦好手 1 級 ‧ 2021-07-29 10:24:07 檢舉

答案對了,感恩。

2
一級屠豬士
iT邦大師 1 級 ‧ 2021-07-29 07:53:57
with t1 as (
select custid, sum(amount) sumaum
  from it0729
 group by custid
), t2 as (
select custid
     , dense_rank() over(order by sumaum desc) rnk1
  from t1
), t3 as (
select i.custid
     , i.productid
     , i.amount
  from t2
     , it0729 i
 where rnk1 < 11
   and t2.custid = i.custid
 group by i.custid, i.productid, i.amount
), t4 as (
select custid
     , productid
     , amount
     , dense_rank() over(partition by custid order by amount desc) rnk2
  from t3
)
select custid
     , array_agg(row(productid, amount) order by amount desc)
  from t4
 where rnk2 < 11
 group by custid;



-[ RECORD 1 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
custid    | 9R0003
array_agg | {"(SSR25XW6QZDDME-1900LYM,158400)","(DK2506S-3RRGT-503LC7,45820)","(6905-H-20T1XZZ*MA,26300)","(SHS30R1SS-1960LP,19920)","(SHS25CSS,18800)","(BNT1605-2.6ZZ-319LT,7400)"}
-[ RECORD 2 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
custid    | 9R0004
array_agg | {"(HSR25AZZ,301140)","(HSR25BSS,256500)","(HSR25M1B,122600)","(SSR35XW3SSFM-3320LHF,77000)","(HSR25A6ZZE-2320LTE,64820)","(SHS25C2SS-3440LT,64236)","(SHS35R2SS-2040L,57800)","(SBN3210V-7WWG0-1230LC5-J2K,48000)","(SHS30CSS,22900)","(LM-8S,19950)"}
-[ RECORD 3 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
custid    | FT0007
array_agg | {"(SHS45LV2UUFME-3610LTF,190380)","(GSR35T5UUFE-7600LHRTF,167680)","(1-KSH-32WL-60-500,132000)","(6904-H-20T1XDDU*MA,106400)","(NH451200BND2KCZ1AL,92480)","(AFF+70G,71200)","(1-KSH-32WL-60-500,66000)","(SHS45LV2UUFME-3610LTF,63460)","(NS251480ALD2PCZ1AL,46960)","(30TAC62CSUHPN7C,45432)"}
-[ RECORD 4 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
custid    | R20016
array_agg | {"(SR30-3000L,180900)","(GSR25-1500LR,106600)","(FK-10-OAMG,78000)","(GSR25TUU,57480)","(FK-08-OAMG,36300)","(BTK2005V-2.6ZZ-1000LC7,14148)","(SHS30LRSS,13500)","(SHS35CSS,12000)","(SHS30-840L,4040)","(CF-20-1B,3500)"}
-[ RECORD 5 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
custid    | U20007
array_agg | {"(NRS45LRX6UUFME-7800LTFE,276000)","(SR30M1WUU,94500)","(HSR25LR2QZSSM-1060LHM,88000)","(GSR25TUU,48600)","(HSR20M1A,29600)","(2-KSH-40-110-54-HD-HD,25200)","(020-MA5-0723E-FY,15540)","(1SRS7MUU-55LM,7500)","(020-MA5-0723E-FY,5180)","(BLK2525F-7.2ZZ-402LTFG-JIK-4,4725)","(6804-H-20T1XZZ*MA,4725)"}
-[ RECORD 6 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
custid    | U20008
array_agg | {"(SHS30C2SSC0F-550LF,116000)","(SSR35XW2UUFM-4910LTF,104000)","(HR2042UUM,51300)","(VRU-2080,38000)","(SHS30VSS,2835)","(EGH20CAZ0C,2700)"}
-[ RECORD 7 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
custid    | WG0012
array_agg | {"(SHS25C4SSC1-1300LSP-2,201600)","(RB-35020UUCC0P4,156000)","(1651-211-22-4-1605-201-31-1300L,33600)","(LMF-6LUU,4140)","(6815ZZ-FBJ,2000)","(MHC-27C-8-8,1740)","(LM-6LUU,1640)","(7202-NACHI,1600)","(LF-740ZZ-JIB,1536)","(L-1360ZZ-JIB,1344)"}
-[ RECORD 8 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
custid    | WJ0090
array_agg | {"(SVR45R3SSHHC0FMS-5925LPTF,2798600)","(AFF+400G,580000)","(WGF50100FM-3ZZG3-2900LC7F,573720)","(BLK3636FM-7.2QZZZ-1662LTF,452275)","(1315767,348000)","(SHS45V4SSHHC0FMS-6870LHFT,344296)","(2R050-50A1-1FSKT-4930-5200-0.018H,330000)","(WGF50100FM-3ZZG3-3300LC7TF,308228)","(1314117,300000)","(1314134,290000)","(AFF+400G,290000)"}
-[ RECORD 9 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
custid    | WJ0091
array_agg | {"(SGL35TF2-3000-W2LB,93800)","(VRT-1025AM,88000)","(1347007,84480)","(SHS20C2SS-3200LF,80200)","(RNFCL5050A6S-2272LT,60000)","(1315558,60000)","(RB-4010UUCC0,45876)","(1532-2-2016-1531-2-2900-92L,41600)","(SHS45VSSHHC0FMS,41600)","(MUCP205-ASAHI,30520)","(HSR25R2SSFMS-340LFM,26600)","(SHW14CR2UUM-200LM,22000)"}
-[ RECORD 10 ]----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
custid    | XU0007
array_agg | {"(RA-12008UUC0,87395)","(RA-5008UUCC0,76500)","(RB-2508UUCC0,62640)","(RA-5008UUCC0,48960)","(MDK1002FM-3RRG2+125LC7F,47600)","(RB-2508UUCC0,11
600)","(RB-2508UUCC0,9280)","(FK-8,6300)","(RA-7008UUCC0,3900)","(RA-7008UUC0,2600)"}
 
看更多先前的回應...收起先前的回應...
ckp6250 iT邦好手 1 級 ‧ 2021-07-29 08:44:50 檢舉

感恩,
但,金額欄呢?

您的程式碼,比我短多了,但還是很繁雜呀,
能不能再短些?

資料裡交易價格有負數,也有相同客戶相同產品多次交易不同價格,
先保守一點,多層的處理囉,長一點沒關係啦.
金額也放上去囉.

ckp6250 iT邦好手 1 級 ‧ 2021-07-29 10:05:26 檢舉

您的答案不對,WH0090 的 AFF+400G 不是 580000,是1,305,000

又,我就是想要短的程式碼才上來求教的呀,

您殺豬時,一刀斃命不是比十刀斃命要來得帥嗎?

看來是輸入資料時, 我要改用 decimal 才可以, 大概有溢位了.
反正樓下 rogeryao 已經有很漂亮的解了.我這個就不急著改了.

合計銷售額 原來是這個, 看錯了.... 我是算成單獨的.
那就保留成另一種算法吧.不改了.

nicolesmith
iT邦見習生 ‧ 2021-08-24 13:23:40
【**此則訊息已被站方移除**】

我要發表回答

立即登入回答