56,677
社区成员
发帖
与我相关
我的任务
分享
SELECT
C.cat_name AS cat_name,
SUM(OG.goods_number) AS total_sell_num,
SUM(OI.order_amount) AS total_sell_amount
FROM `zhaipin_category` AS C
RIGHT JOIN `zhaipin_goods` AS G ON C.cat_id = G.cat_id
RIGHT JOIN `zhaipin_order_goods` AS OG ON OG.goods_id=G.goods_id
LEFT JOIN `zhaipin_order_info` AS OI ON OI.order_id=OG.order_id
AND OI.pay_status = 2
AND shipping_time >= ('1325318400'-8*60*60)
AND shipping_time <= ('1338242092'-8*60*60)
GROUP BY C.cat_id
最后执行的代码是这样的
SELECT C.cat_name AS cat_name,SUM(OG.goods_number) AS total_sell_num,SUM(OI.order_amount) AS total_sell_amount FROM `zhaipin_category` AS C RIGHT JOIN `zhaipin_goods` AS G ON C.cat_id = G.cat_id RIGHT JOIN `zhaipin_order_goods` AS OG ON OG.goods_id=G.goods_id LEFT JOIN `zhaipin_order_info` AS OI ON OI.order_id=OG.order_id WHERE 1=1 AND OI.pay_status = 2 AND shipping_time >= '1325289600' AND shipping_time <= '1338220353' GROUP BY C.cat_id
CREATE TABLE `zhaipin_order_goods` (
`rec_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`order_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
`goods_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
`goods_name` varchar(120) NOT NULL DEFAULT '',
`goods_sn` varchar(60) NOT NULL DEFAULT '',
`goods_number` int(8) unsigned NOT NULL DEFAULT '1',
`market_price` decimal(10,2) NOT NULL DEFAULT '0.00',
`goods_price` decimal(10,2) NOT NULL DEFAULT '0.00',
`goods_attr` text NOT NULL,
`goods_ttr` float DEFAULT NULL,
`send_number` int(8) unsigned NOT NULL DEFAULT '0',
`is_real` tinyint(1) unsigned NOT NULL DEFAULT '0',
`extension_code` varchar(30) NOT NULL DEFAULT '',
`parent_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
`is_gift` smallint(5) unsigned NOT NULL DEFAULT '0',
`goods_attr_id` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`rec_id`),
KEY `order_id` (`order_id`),
KEY `goods_id` (`goods_id`),
KEY `goods_sn` (`goods_sn`),
KEY `goods_sn_2` (`goods_sn`,`order_id`),
KEY `order_id_2` (`order_id`,`goods_sn`)
) ENGINE=InnoDB AUTO_INCREMENT=789879 DEFAULT CHARSET=utf8
CREATE TABLE `zhaipin_goods` (
`goods_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`cat_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`goods_sn` varchar(60) NOT NULL DEFAULT '',
`goods_name` varchar(120) NOT NULL DEFAULT '',
`goods_name_style` varchar(60) NOT NULL DEFAULT '+',
`click_count` int(10) unsigned NOT NULL DEFAULT '0',
`brand_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`provider_name` varchar(100) NOT NULL DEFAULT '',
`goods_number` int(11) unsigned NOT NULL DEFAULT '0',
`goods_weight` decimal(10,3) unsigned NOT NULL DEFAULT '0.000',
`market_price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
`shop_price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
`promote_price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
`promote_start_date` int(11) unsigned NOT NULL DEFAULT '0',
`promote_end_date` int(11) unsigned NOT NULL DEFAULT '0',
`warn_number` tinyint(3) unsigned NOT NULL DEFAULT '1',
`keywords` varchar(255) NOT NULL DEFAULT '',
`goods_brief` varchar(255) NOT NULL DEFAULT '',
`goods_desc` text NOT NULL,
`goods_thumb` varchar(255) NOT NULL DEFAULT '',
`goods_img` varchar(255) NOT NULL DEFAULT '',
`original_img` varchar(255) NOT NULL DEFAULT '',
`is_real` tinyint(3) unsigned NOT NULL DEFAULT '1',
`extension_code` varchar(30) NOT NULL DEFAULT '',
`is_on_sale` tinyint(1) unsigned NOT NULL DEFAULT '1',
`is_alone_sale` tinyint(1) unsigned NOT NULL DEFAULT '1',
`integral` int(10) unsigned NOT NULL DEFAULT '0',
`add_time` int(10) unsigned NOT NULL DEFAULT '0',
`sort_order` smallint(4) unsigned NOT NULL DEFAULT '100',
`is_delete` tinyint(1) unsigned NOT NULL DEFAULT '0',
`is_best` tinyint(1) unsigned NOT NULL DEFAULT '0',
`is_new` tinyint(1) unsigned NOT NULL DEFAULT '0',
`is_hot` tinyint(1) unsigned NOT NULL DEFAULT '0',
`is_promote` tinyint(1) unsigned NOT NULL DEFAULT '0',
`bonus_type_id` tinyint(3) unsigned NOT NULL DEFAULT '0',
`last_update` int(10) unsigned NOT NULL DEFAULT '0',
`goods_type` smallint(5) unsigned NOT NULL DEFAULT '0',
`seller_note` varchar(255) NOT NULL DEFAULT '',
`give_integral` int(11) NOT NULL DEFAULT '-1',
`rank_integral` int(11) NOT NULL DEFAULT '-1',
`suppliers_id` smallint(5) unsigned DEFAULT NULL,
`is_check` tinyint(1) unsigned DEFAULT NULL,
`goods_ppjs` text,
`goods_rhzf` text,
`goods_psfs` text,
`goods_fpbx` text,
`is_shipping` tinyint(1) unsigned NOT NULL DEFAULT '0',
`good_con` int(11) NOT NULL DEFAULT '0',
`spsp` varchar(255) DEFAULT NULL,
`goods_proxy_type` int(1) NOT NULL DEFAULT '0' COMMENT '商品代理类型:0普通商品|1代理商品|',
PRIMARY KEY (`goods_id`),
KEY `goods_sn` (`goods_sn`),
KEY `brand_id` (`brand_id`),
KEY `sort_order` (`sort_order`),
KEY `promote_start_date` (`promote_start_date`),
KEY `good_con` (`good_con`),
KEY `misc` (`is_delete`,`is_on_sale`,`is_alone_sale`,`is_promote`,`is_hot`,`is_real`,`cat_id`),
KEY `goods_proxy_type` (`goods_proxy_type`),
KEY `cat_id` (`cat_id`,`is_delete`,`is_on_sale`)
) ENGINE=InnoDB AUTO_INCREMENT=5396486 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
CREATE TABLE `zhaipin_order_goods` (
`rec_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`order_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
`goods_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
`goods_name` varchar(120) NOT NULL DEFAULT '',
`goods_sn` varchar(60) NOT NULL DEFAULT '',
`goods_number` int(8) unsigned NOT NULL DEFAULT '1',
`market_price` decimal(10,2) NOT NULL DEFAULT '0.00',
`goods_price` decimal(10,2) NOT NULL DEFAULT '0.00',
`goods_attr` text NOT NULL,
`goods_ttr` float DEFAULT NULL,
`send_number` int(8) unsigned NOT NULL DEFAULT '0',
`is_real` tinyint(1) unsigned NOT NULL DEFAULT '0',
`extension_code` varchar(30) NOT NULL DEFAULT '',
`parent_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
`is_gift` smallint(5) unsigned NOT NULL DEFAULT '0',
`goods_attr_id` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`rec_id`),
KEY `order_id` (`order_id`),
KEY `goods_id` (`goods_id`),
KEY `goods_sn` (`goods_sn`),
KEY `goods_sn_2` (`goods_sn`,`order_id`),
KEY `order_id_2` (`order_id`,`goods_sn`)
) ENGINE=InnoDB AUTO_INCREMENT=789879 DEFAULT CHARSET=utf8
CREATE TABLE `zhaipin_order_info` (
`order_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`order_sn` varchar(20) NOT NULL DEFAULT '',
`user_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
`suser_id` mediumint(8) unsigned NOT NULL,
`order_status` tinyint(1) unsigned NOT NULL DEFAULT '0',
`shipping_status` tinyint(1) unsigned NOT NULL DEFAULT '0',
`pay_status` tinyint(1) unsigned NOT NULL DEFAULT '0',
`consignee` varchar(60) NOT NULL DEFAULT '',
`country` smallint(5) unsigned NOT NULL DEFAULT '0',
`province` smallint(5) unsigned NOT NULL DEFAULT '0',
`city` smallint(5) unsigned NOT NULL DEFAULT '0',
`district` smallint(5) unsigned NOT NULL DEFAULT '0',
`address` varchar(255) NOT NULL DEFAULT '',
`zipcode` varchar(60) NOT NULL DEFAULT '',
`tel` varchar(60) NOT NULL DEFAULT '',
`mobile` varchar(60) NOT NULL DEFAULT '',
`email` varchar(60) NOT NULL DEFAULT '',
`best_time` varchar(120) NOT NULL DEFAULT '',
`sign_building` varchar(120) NOT NULL DEFAULT '',
`postscript` text NOT NULL,
`shipping_id` tinyint(3) NOT NULL DEFAULT '0',
`shipping_name` varchar(120) NOT NULL DEFAULT '',
`pay_id` tinyint(3) NOT NULL DEFAULT '0',
`pay_name` varchar(120) NOT NULL DEFAULT '',
`how_oos` varchar(120) NOT NULL DEFAULT '',
`how_surplus` varchar(120) NOT NULL DEFAULT '',
`pack_name` varchar(120) NOT NULL DEFAULT '',
`card_name` varchar(120) NOT NULL DEFAULT '',
`card_message` varchar(255) NOT NULL DEFAULT '',
`inv_payee` varchar(120) NOT NULL DEFAULT '',
`inv_content` varchar(120) NOT NULL DEFAULT '',
`goods_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
`shipping_fee` decimal(10,2) NOT NULL DEFAULT '0.00',
`insure_fee` decimal(10,2) NOT NULL DEFAULT '0.00',
`pay_fee` decimal(10,2) NOT NULL DEFAULT '0.00',
`pack_fee` decimal(10,2) NOT NULL DEFAULT '0.00',
`card_fee` decimal(10,2) NOT NULL DEFAULT '0.00',
`money_paid` decimal(10,2) NOT NULL DEFAULT '0.00',
`surplus` decimal(10,2) NOT NULL DEFAULT '0.00',
`integral` int(10) unsigned NOT NULL DEFAULT '0',
`integral_money` decimal(10,2) NOT NULL DEFAULT '0.00',
`bonus` decimal(10,2) NOT NULL DEFAULT '0.00',
`order_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
`from_ad` smallint(5) NOT NULL DEFAULT '0',
`referer` varchar(255) NOT NULL DEFAULT '',
`add_time` int(10) unsigned NOT NULL DEFAULT '0',
`confirm_time` int(10) unsigned NOT NULL DEFAULT '0',
`pay_time` int(10) unsigned NOT NULL DEFAULT '0',
`received` date DEFAULT NULL,
`shipping_time` int(10) unsigned NOT NULL DEFAULT '0',
`pack_id` tinyint(3) unsigned NOT NULL DEFAULT '0',
`card_id` tinyint(3) unsigned NOT NULL DEFAULT '0',
`bonus_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`invoice_no` varchar(255) NOT NULL DEFAULT '',
`extension_code` varchar(30) NOT NULL DEFAULT '',
`extension_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
`to_buyer` varchar(255) NOT NULL DEFAULT '',
`pay_note` varchar(255) NOT NULL DEFAULT '',
`agency_id` smallint(5) unsigned NOT NULL,
`inv_type` varchar(60) NOT NULL,
`tax` decimal(10,2) NOT NULL,
`is_separate` tinyint(1) NOT NULL DEFAULT '0',
`parent_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
`discount` decimal(10,2) NOT NULL,
`order_type` int(2) DEFAULT '0' COMMENT '订单类型:6网购,7代购,8百业联盟,9诚信商家',
PRIMARY KEY (`order_id`),
UNIQUE KEY `order_sn` (`order_sn`),
KEY `user_id` (`user_id`),
KEY `pay_time` (`pay_time`),
KEY `shipping_time` (`shipping_time`),
KEY `add_time` (`add_time`),
KEY `consignee` (`consignee`),
KEY `received` (`received`),
KEY `suser_id` (`suser_id`),
KEY `from_ad` (`from_ad`),
KEY `misc` (`order_status`,`shipping_status`,`pay_status`,`pay_id`),
KEY `order_type` (`order_type`)
) ENGINE=InnoDB AUTO_INCREMENT=3466951 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
CREATE TABLE `zhaipin_order_goods` (
`rec_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`order_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
`goods_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
`goods_name` varchar(120) NOT NULL DEFAULT '',
`goods_sn` varchar(60) NOT NULL DEFAULT '',
`goods_number` int(8) unsigned NOT NULL DEFAULT '1',
`market_price` decimal(10,2) NOT NULL DEFAULT '0.00',
`goods_price` decimal(10,2) NOT NULL DEFAULT '0.00',
`goods_attr` text NOT NULL,
`goods_ttr` float DEFAULT NULL,
`send_number` int(8) unsigned NOT NULL DEFAULT '0',
`is_real` tinyint(1) unsigned NOT NULL DEFAULT '0',
`extension_code` varchar(30) NOT NULL DEFAULT '',
`parent_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
`is_gift` smallint(5) unsigned NOT NULL DEFAULT '0',
`goods_attr_id` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`rec_id`),
KEY `order_id` (`order_id`),
KEY `goods_id` (`goods_id`),
KEY `goods_sn` (`goods_sn`),
KEY `goods_sn_2` (`goods_sn`,`order_id`),
KEY `order_id_2` (`order_id`,`goods_sn`)
) ENGINE=InnoDB AUTO_INCREMENT=789879 DEFAULT CHARSET=utf8
+----+-------------+-------+--------+-----------------------+---------+---------+--------------------+--------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-----------------------+---------+---------+--------------------+--------+---------------------------------+
| 1 | SIMPLE | OG | ALL | NULL | NULL | NULL | NULL | 726544 | Using temporary; Using filesort |
| 1 | SIMPLE | G | eq_ref | PRIMARY | PRIMARY | 4 | dumpdb.OG.goods_id | 1 | |
| 1 | SIMPLE | C | eq_ref | PRIMARY | PRIMARY | 2 | dumpdb.G.cat_id | 1 | |
| 1 | SIMPLE | OI | eq_ref | PRIMARY,shipping_time | PRIMARY | 3 | dumpdb.OG.order_id | 1 | |
+----+-------------+-------+--------+-----------------------+---------+---------+--------------------+--------+---------------------------------+
4 rows in set (0.07 sec)