使用case when then else
SELECT
sg.s_goods_id,
sg.company_id,
g.goods_id,
g.goods_name,
g.spec,
g.brand_name,
SUM(og.number) AS number,
sm.current_num,
g.goods_certificate_end,
SUM(
CASE MONTH (og.create_time)
WHEN 1 THEN
og.number
ELSE
0
END
) AS one,
SUM(
CASE MONTH (og.create_time)
WHEN 2 THEN
og.number
ELSE
0
END
) AS two,
SUM(
CASE MONTH (og.create_time)
WHEN 3 THEN
og.number
ELSE
0
END
) AS three,
SUM(
CASE MONTH (og.create_time)
WHEN 4 THEN
og.number
ELSE
0
END
) AS four,
SUM(
CASE MONTH (og.create_time)
WHEN 5 THEN
og.number
ELSE
0
END
) AS five,
SUM(
CASE MONTH (og.create_time)
WHEN 6 THEN
og.number
ELSE
0
END
) AS six,
SUM(
CASE MONTH (og.create_time)
WHEN 7 THEN
og.number
ELSE
0
END
) AS seven,
SUM(
CASE MONTH (og.create_time)
WHEN 8 THEN
og.number
ELSE
0
END
) AS eight,
SUM(
CASE MONTH (og.create_time)
WHEN 9 THEN
og.number
ELSE
0
END
) AS nine,
SUM(
CASE MONTH (og.create_time)
WHEN 10 THEN
og.number
ELSE
0
END
) AS ten,
SUM(
CASE MONTH (og.create_time)
WHEN 11 THEN
og.number
ELSE
0
END
) AS eleven,
SUM(
CASE MONTH (og.create_time)
WHEN 12 THEN
og.number
ELSE
0
END
) AS twelve
FROM
`yushi_supplier_goods` `sg`
INNER JOIN `yushi_goods` `g` ON `g`.`goods_id` = `sg`.`goods_id`
INNER JOIN `yushi_order_goods` `og` ON `g`.`goods_id` = og.goods_id
AND og.supplier_id = 69
INNER JOIN `yushi_stock_manage` `sm` ON `g`.`goods_id` = sm.goods_id
AND sm.company_id = 69
WHERE
(
sg.company_id = 69
AND og.create_time > '2019-01-01'
)
GROUP BY
`og`.`goods_id`
ORDER BY
`goods_certificate_end`,
`goods_id` DESC