分组最大N

发表于 分类于

分组最大N

1
2
3
4
5
6
7
SELECT
	MAX(item_id) AS item_id
FROM
	mall_item mi
GROUP BY
	mi.item_barcode,
	mi.item_store

优化为 join

1
2
3
4
5
6
7
8
9
10
11
SELECT
	i1.*
FROM
	mall_item i1
LEFT OUTER JOIN mall_item i2 ON (
	i1.item_barcode = i2.item_barcode
	AND i1.item_store = i2.item_store
	AND i1.item_id < i2.item_id
)
WHERE
	i2.item_barcode IS NULL;

起因 数据库监控看到一条慢SQL 耗时1秒多

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SELECT
    mi.item_id,
    mi.item_barcode,
    mi.item_pics,
    mi.item_name,
    mi.item_store,
    mi.item_unit,
    mi.item_specification,
    sc.item_sell_price,sc.item_original_price,sc.specs_remark,
    sc.item_barcode,sc.source as itemType,
    IF(scuh.use_type='a','1','0') use_type,
    scuh.purchase_num AS quantity,
    scuh.card_id
    FROM

    (
    SELECT
    MAX(item_id) AS item_id
    FROM mall_item mi
    GROUP BY mi.item_barcode, mi.item_store
    ) tmp
    JOIN mall_item mi ON tmp.item_id = mi.item_id
    join shop_card sc on mi.item_store=sc.mall_id AND mi.item_barcode=sc.item_barcode
    LEFT JOIN shop_card_use_history scuh ON scuh.purchase_num>0 and scuh.use_type != 'j' and sc.card_id = scuh.card_id
    where scuh.shop_receipt_id = 'ps_1382892968715325440'

优化后

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
SELECT
  i1.item_id,
  i1.item_barcode,
  i1.item_pics,
  i1.item_name,
  i1.item_store,
  i1.item_unit,
  i1.item_specification,
  sc.item_sell_price,
  sc.item_original_price,
  sc.specs_remark,
  sc.item_barcode,
  sc.source AS itemType,

IF (scuh.use_type = 'a', '1', '0') use_type,
 scuh.purchase_num AS quantity,
 scuh.card_id
FROM
  mall_item i1
LEFT OUTER JOIN mall_item i2 ON (
  i1.item_barcode = i2.item_barcode
  AND i1.item_store = i2.item_store
  AND i1.item_id < i2.item_id
)
JOIN shop_card sc ON i1.item_store = sc.mall_id
AND i1.item_barcode = sc.item_barcode
LEFT JOIN shop_card_use_history scuh ON scuh.purchase_num > 0
AND scuh.use_type != 'j'
AND sc.card_id = scuh.card_id
WHERE
  scuh.shop_receipt_id = 'ps_1382892968715325440'
AND i2.item_barcode IS NULL;

优化完只用几十毫秒