SQL Group By 复杂查询
MySql 5.7以前支持 group by 查询的字段不在 group by 的字段中 默认选第一行 而5.7以后不支持这样的写法 同事就写出了以下的代码
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
33
34
35
36
|
SELECT
max(mi.item_name),
max(mi.item_unit),
max(mi.item_specification),
max(mi.item_pics),
sc.item_barcode,
IF(scuh.use_type = '3', '1', '0') use_type,
SUM(scuh.purchase_num) AS quantity
FROM
shop_card_use_history scuh
LEFT JOIN shop_card sc ON scuh.card_id = sc.card_id
LEFT JOIN
(
SELECT
mi.item_barcode,
mi.item_pics,
item_name,
item_store,
mi.item_unit,
mi.item_specification
FROM mall_item mi
WHERE mi.item_id =
(
SELECT
item_id
FROM mall_item mi1
WHERE
mi.item_barcode = mi1.item_barcode AND mi.item_store = mi1.item_store
ORDER BY mi1.item_status ASC, IFNULL(mi1.update_time, NOW()) DESC
LIMIT 1
)
) mi ON sc.item_barcode = mi.item_barcode AND scuh.mall_id = mi.item_store
WHERE scuh.shop_receipt_id = "ps_31707080856260608"
GROUP BY sc.item_barcode, scuh.use_type, scuh.mall_id
ORDER BY quantity DESC
|
应该把 group by 放在里层 外层去关联只需要一行数据的其他表
修改成正常 使用group by的写法
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
| SELECT
b.item_name,
b.item_unit,
b.item_specification,
b.item_pics,
a.*
FROM
(
SELECT
scuh.item_barcode,
sc.item_id,
scuh.use_type,
sum(scuh.purchase_num)
FROM
shop_card_use_history scuh
LEFT JOIN shop_card sc ON scuh.card_id = sc.card_id
WHERE
scuh.shop_receipt_id = "ps_31707080856260608"
GROUP BY
scuh.item_barcode,
sc.item_id,
scuh.use_type
) a
left join mall_item b on(a.item_id = b.item_id)
where 1=1
|
再修改 查询最新商品的逻辑 原有的逻辑太复杂 涉及到上架下架 逻辑删除之类的 实际上 item_id 最大的版本 一般就是最新的数据
实在不行 java中处理商品信息也可以
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
| SELECT
b.item_name,
b.item_unit,
b.item_specification,
b.item_pics,
a.*
FROM
(
SELECT
scuh.item_barcode,
scuh.use_type,
sum( scuh.purchase_num )
FROM
shop_card_use_history scuh
LEFT JOIN shop_card sc ON scuh.card_id = sc.card_id
WHERE
scuh.shop_receipt_id = "ps_31707080856260608"
GROUP BY
scuh.item_barcode,
scuh.use_type
) a
LEFT JOIN (
SELECT
mb.*
FROM
( SELECT item_barcode, item_store, max( item_id ) AS item_id FROM mall_item GROUP BY item_barcode, item_store ) ma
LEFT JOIN mall_item mb ON ( ma.item_id = mb.item_id )
) b ON ( a.item_barcode = b.item_barcode )
WHERE
1 = 1
|