SQL关于LEFT JOIN + SUM + IFNULL + GROUP BY + ORDER BY + CASE WHEN 的一些测试
测试的表结构如下:
测试目的,获得具有特定条件下的计算。
题1:根据订单表(order_info)、订单商品表(order_goods)列出商品卖的数量
测试语句:
SELECT g.goods_name , IFNULL(SUM(og.goods_num),0) AS sell_num
FROM goods AS g
LEFT JOIN order_goods AS og ON g.goods_id = og.goods_id
LEFT JOIN order_info AS oi ON oi.order_info_id = og.order_id
GROUP BY g.goods_id
测试结果:
题2:根据订单表(order_info)、订单商品表(order_goods)列出商品卖的数量,且列出该类别。
测试语句:
SELECT g.goods_name ,c.cat_name , IFNULL(SUM(og.goods_num),0) AS sell_num
FROM goods AS g
LEFT JOIN order_goods AS og ON g.goods_id = og.goods_id
LEFT JOIN order_info AS oi ON oi.order_info_id = og.order_id
LEFT JOIN cate AS c ON c.cat_id = g.cat_id
GROUP BY g.goods_id
测试结果:
题3:根据订单表(order_info)、订单商品表(order_goods)、指定类别ID(cat_id =1),列出商品卖的数量,且列出该类别。
测试语句:
SELECT g.goods_name ,c.cat_name , IFNULL(SUM(og.goods_num),0) AS sell_num
FROM goods AS g
LEFT JOIN order_goods AS og ON g.goods_id = og.goods_id
LEFT JOIN order_info AS oi ON oi.order_info_id = og.order_id
LEFT JOIN cate AS c ON c.cat_id = g.cat_id
WHERE c.cat_id = 1
GROUP BY g.goods_id
测试结果:
题4:根据订单表(order_info)、订单商品表(order_goods)、指定类别ID(cat_id =1),列出商品卖的数量,且列出该类别。没有卖出的也同样显示。
测试语句:(以下是我项目中用到的,用到case when then else end,数据也不放出来了,可供参考使用而已)
SELECT g . * , c.cat_name, SUM(
CASE WHEN (
oi.order_status = '7'
OR oi.order_status = '5'
)
AND (
oi.pay_status = '2'
)
AND (
oi.shipping_status = '2'
OR oi.shipping_status = '1'
)
THEN og.goods_number
ELSE 0
END ) AS sell_num
FROM `tdm_goods` AS g
LEFT JOIN `tdm_category` AS c ON g.cat_id = c.cat_id
LEFT JOIN `tdm_order_goods` AS og ON g.goods_id = og.goods_id
LEFT JOIN `tdm_order_info` AS oi ON oi.order_id = og.order_id
WHERE 1
AND g.brand_id = '65'
AND g.is_delete =0
AND g.is_on_sale =1
GROUP BY g.goods_id
ORDER BY sell_num DESC
以下是构建表的结构,需要测试的可以直接导入SQL测试。
--
-- 数据库: `test`
--
-- --------------------------------------------------------
--
-- 表的结构 `cate`
--
CREATE TABLE IF NOT EXISTS `cate` (
`cat_id` int(11) NOT NULL AUTO_INCREMENT,
`cat_name` varchar(11) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`cat_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
--
-- 转存表中的数据 `cate`
--
INSERT INTO `cate` (`cat_id`, `cat_name`) VALUES
(1, '分类1'),
(2, '分类2');
-- --------------------------------------------------------
--
-- 表的结构 `goods`
--
CREATE TABLE IF NOT EXISTS `goods` (
`goods_id` int(11) NOT NULL AUTO_INCREMENT,
`goods_name` varchar(11) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`cat_id` int(11) NOT NULL,
PRIMARY KEY (`goods_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
--
-- 转存表中的数据 `goods`
--
INSERT INTO `goods` (`goods_id`, `goods_name`, `cat_id`) VALUES
(2, '书', 1),
(3, '手机', 2),
(4, '水壶', 1),
(5, '没人买', 2);
-- --------------------------------------------------------
--
-- 表的结构 `order_goods`
--
CREATE TABLE IF NOT EXISTS `order_goods` (
`order_goods_id` int(11) NOT NULL AUTO_INCREMENT,
`order_id` int(11) NOT NULL,
`goods_id` int(11) NOT NULL,
`goods_num` int(11) NOT NULL,
PRIMARY KEY (`order_goods_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
--
-- 转存表中的数据 `order_goods`
--
INSERT INTO `order_goods` (`order_goods_id`, `order_id`, `goods_id`, `goods_num`) VALUES
(1, 2, 2, 3),
(2, 2, 3, 2),
(3, 3, 4, 1),
(4, 3, 4, 1),
(5, 4, 4, 4);
-- --------------------------------------------------------
--
-- 表的结构 `order_info`
--
CREATE TABLE IF NOT EXISTS `order_info` (
`order_info_id` int(11) NOT NULL AUTO_INCREMENT,
`test2` varchar(11) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`order_info_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
--
-- 转存表中的数据 `order_info`
--
INSERT INTO `order_info` (`order_info_id`, `test2`) VALUES
(2, '测试1'),
(3, '测试2'),
(4, '测试3');