求一条SQL语句

Question

一条sql搞不定 二次购买人数 二次购买订单数是一个意思吗 

Answer 1

是一个意思。计算出来的数值一样。大哥可以分多条SQL的。 数据我自己导出来匹配

Answer 2

贴建表及插入记录的SQL，及要求结果出来看看

Answer 3

[code=sql]
CREATE TABLE `e_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',
  `order_status` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '订单状态',
  `shipping_status` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '发货状态',
  `pay_status` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '支付状态',
  `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` varchar(255) NOT NULL DEFAULT '',
  `shipping_id` tinyint(3) NOT NULL DEFAULT '0' COMMENT '快递ID',
  `shipping_name` varchar(120) NOT NULL DEFAULT '' COMMENT '快递名称',
  `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' COMMENT '商品总金额',
  `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' COMMENT ,
  `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',
  `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` mediumint(8) 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,
  `alipay_send` tinyint(1) DEFAULT NULL ,
  `send_code` tinyint(1) DEFAULT '0' ,
  `shouhuo_type` tinyint(4) NOT NULL DEFAULT '0',
  `tuikuan_type` tinyint(4) DEFAULT '0' ,
  `is_comment` tinyint(4) DEFAULT '0' ,
  `invite_uid` int(11) DEFAULT '0' ,
  `type_id` int(11) DEFAULT '0' ,
  `is_fanxian` tinyint(1) DEFAULT '0' ,
  `lvxing_hongbao` decimal(10,2) DEFAULT '0.00' COMMENT '使用旅行红包金额',
  `trade_no` varchar(50) DEFAULT NULL ,
  `extra_fb` int(11) DEFAULT NULL,
  `fb` decimal(10,2) DEFAULT NULL,
  `is_show` tinyint(1) NOT NULL DEFAULT '1',
  `is_qq_share` tinyint(1) unsigned DEFAULT '0' ,
  `is_sina_share` tinyint(1) unsigned DEFAULT '0' ,
  `real_pay_money` decimal(10,2) DEFAULT '0.00' ,
  `receipt_time` int(10) DEFAULT NULL , 
  `frozen_tkmoney` decimal(10,2) DEFAULT '0.00' ,
  PRIMARY KEY (`order_id`,`shouhuo_type`),
  UNIQUE KEY `order_sn` (`order_sn`),
  KEY `user_id` (`user_id`),
  KEY `order_status` (`order_status`),
  KEY `shipping_status` (`shipping_status`),
  KEY `pay_status` (`pay_status`),
  KEY `shipping_id` (`shipping_id`),
  KEY `pay_id` (`pay_id`),
  KEY `extension_code` (`extension_code`,`extension_id`),
  KEY `agency_id` (`agency_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Answer 4

INSERT INTO `e_order_info` VALUES 
(105,'2012092570548',12,1,0,2,'',0,0,0,0,'','','','','','','','',1,'邮政快递包裹',8,'银联在线支付','','','','','','','',1198.00,0.00,0.00,0.00,0.00,0.00,1198.00,0.00,0,0.00,0.00,0.00,0,'',1348545294,1348545671,1348545671,0,0,0,0,'','group_buy',97,'','',0,'',0.00,0,0,0.00,NULL,1,0,0,0,0,0,0,0.00,NULL,NULL,NULL,1,0,0,0.00,NULL,0.00),
(106,'2012092626807',16,1,0,0,'',0,0,0,0,'','','','','','','','',1,'邮政快递包裹',6,'网银在线','','','','','','','',598.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0,0.00,0.00,598.00,0,'',1348594620,0,1348594620,0,0,0,0,'','group_buy',71,'','',0,'',0.00,0,0,0.00,NULL,1,0,0,0,0,0,0,0.00,NULL,NULL,NULL,1,0,0,0.00,NULL,0.00),
(107,'2012092682787',16,1,0,0,'',0,0,0,0,'','','','','','','','',1,'邮政快递包裹',6,'网银在线','','','','','','','',598.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0,0.00,0.00,598.00,0,'',1348594765,0,1348594765,0,0,0,0,'','group_buy',71,'','',0,'',0.00,0,0,0.00,NULL,1,0,0,0,0,0,0,0.00,NULL,NULL,NULL,1,0,0,0.00,NULL,0.00),
(108,'2012092644050',16,1,0,2,'',0,0,0,0,'','','','','','','','添加',1,'邮政快递包裹',4,'支付宝','','','','','','','',598.00,0.00,0.00,0.00,0.00,0.00,598.00,0.00,0,0.00,0.00,0.00,0,'',1348594844,1348595783,1348595783,0,0,0,0,'','group_buy',71,'','',0,'',0.00,0,0,0.00,NULL,1,0,0,0,0,0,0,0.00,NULL,NULL,NULL,1,0,0,0.00,NULL,0.00),
(109,'2012092619434',19,3,0,0,'',0,0,0,0,'','','','','','','','',1,'邮政快递包裹',8,'银联在线支付','','','','','','','',490.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0,0.00,0.00,490.00,0,'',1348600424,0,0,0,0,0,0,'','group_buy',99,'','',0,'',0.00,0,0,0.00,NULL,2,0,0,0,0,0,0,0.00,NULL,NULL,NULL,1,0,0,0.00,NULL,0.00),
(110,'2012092626918',19,3,0,0,'',0,0,0,0,'','','','','','','','',1,'邮政快递包裹',8,'银联在线支付','','','','','','','',358.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0,0.00,0.00,358.00,0,'',1348600541,0,0,0,0,0,0,'','group_buy',105,'','',0,'',0.00,0,0,0.00,NULL,2,0,0,0,0,0,0,0.00,NULL,NULL,NULL,1,0,0,0.00,NULL,0.00),
(111,'2012092619022',19,1,1,2,'',0,0,0,0,'','','','','','','','',1,'邮政快递包裹',8,'银联在线支付','','','','','','','',358.00,0.00,0.00,0.00,0.00,0.00,358.00,0.00,0,0.00,0.00,0.00,0,'',1348604001,1348604058,1348604058,0,0,0,0,'','group_buy',105,'','',0,'',0.00,0,0,0.00,NULL,1,1,0,1,0,0,1,0.00,NULL,NULL,NULL,1,0,0,0.00,1377844035,0.00),
(112,'2012092602272',20,1,1,2,'',0,0,0,0,'','','','','','','','',1,'邮政快递包裹',4,'支付宝','','','','','','','',980.00,0.00,0.00,0.00,0.00,0.00,980.00,0.00,0,0.00,0.00,0.00,0,'',1348607269,1348687279,1348687279,0,0,0,0,'','group_buy',99,'','',0,'',0.00,0,0,0.00,NULL,1,1,0,0,0,0,0,0.00,NULL,NULL,NULL,1,0,0,0.00,NULL,0.00),
(115,'2012092666062',21,1,0,2,'',0,0,0,0,'','','','','','','','添加',1,'邮政快递包裹',4,'支付宝','','','','','','','',366.00,0.00,0.00,0.00,0.00,0.00,366.00,0.00,0,0.00,0.00,0.00,0,'',1348612115,1348612155,1348612155,0,0,0,0,'','group_buy',117,'','',0,'',0.00,0,0,0.00,NULL,2,0,0,0,0,0,0,0.00,NULL,NULL,NULL,1,0,0,0.00,NULL,0.00),
(117,'2012092761736',30,1,0,2,'',0,0,0,0,'','','','','','','','',1,'邮政快递包裹',4,'支付宝','','','','','','','',678.00,0.00,0.00,0.00,0.00,0.00,678.00,0.00,0,0.00,0.00,0.00,0,'',1348683089,1348683258,1348683258,0,0,0,0,'','group_buy',85,'','',0,'',0.00,0,0,0.00,NULL,2,1,1,0,0,0,0,0.00,NULL,NULL,NULL,1,0,0,0.00,NULL,0.00),
(118,'2012092765642',33,0,0,0,'',0,0,0,0,'','','','','','','','',1,'邮政快递包裹',4,'支付宝','','','','','','','',499.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0,0.00,0.00,499.00,0,'',1348696719,0,0,0,0,0,0,'','group_buy',100,'','',0,'',0.00,0,0,0.00,NULL,2,0,0,0,0,0,0,0.00,NULL,NULL,NULL,1,0,0,0.00,NULL,0.00),
(119,'2012092711665',33,1,0,2,'',0,0,0,0,'','','','','','','','',1,'邮政快递包裹',4,'支付宝','','','','','','','',998.00,0.00,0.00,0.00,0.00,0.00,998.00,0.00,0,0.00,0.00,0.00,0,'',1348696771,1348697018,1348697018,0,0,0,0,'','group_buy',100,'','',0,'',0.00,0,0,0.00,NULL,2,0,0,0,0,0,0,0.00,NULL,NULL,NULL,1,0,0,0.00,NULL,0.00),
(6956,'2013062161233',108599,0,0,0,'',0,0,0,0,'','','','','','','','',1,'邮政快递包裹',8,'银联在线支付','','','','','','','',980.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0,0.00,0.00,980.00,0,'',1371779110,0,0,0,0,0,0,'','group_buy',904,'','',0,'',0.00,0,0,0.00,NULL,0,0,0,0,0,0,0,0.00,NULL,NULL,NULL,1,0,0,0.00,NULL,0.00),
(134,'2012100839239',82,1,1,2,'',0,0,0,0,'','','','','','','','',1,'邮政快递包裹',8,'银联在线支付','','','','','','','',3150.00,0.00,0.00,0.00,0.00,0.00,3150.00,0.00,0,0.00,0.00,0.00,0,'',1349673257,1349674619,1349674619,0,0,0,0,'','group_buy',114,'','',0,'',0.00,0,0,0.00,NULL,1,1,0,1,0,0,0,0.00,NULL,NULL,NULL,1,0,0,0.00,NULL,0.00),
(133,'2012100826475',80,2,0,0,'',0,0,0,0,'','','','','','','','',1,'邮政快递包裹',4,'支付宝','','','','','','','',458.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0,0.00,0.00,458.00,0,'',1349661995,0,0,0,0,0,0,'','group_buy',76,'','',0,'',0.00,0,0,0.00,NULL,0,0,0,0,0,0,0,0.00,NULL,NULL,NULL,1,0,0,0.00,NULL,0.00),
(6955,'2013062163212',73,2,0,0,'',0,0,0,0,'','','','','','','','',1,'邮政快递包裹',8,'银联在线支付','','','','','','','',1999.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0,0.00,0.00,1999.00,0,'',1371778861,0,0,0,0,0,0,'','group_buy',788,'','',0,'',0.00,0,0,0.00,NULL,0,0,0,0,0,0,0,0.00,NULL,NULL,0.00,1,0,0,0.00,NULL,0.00),
(122,'2012093090496',53,1,1,0,'',0,0,0,0,'','','','','','','','',1,'邮政快递包裹',4,'支付宝','','','','','','','',450.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0,0.00,0.00,450.00,0,'',1348969072,0,1348969072,0,0,0,0,'','group_buy',114,'','',0,'',0.00,0,0,0.00,NULL,1,0,0,0,0,0,0,0.00,NULL,NULL,NULL,1,0,0,0.00,NULL,0.00),
(123,'2012100110245',54,1,1,0,'',0,0,0,0,'','','','','','','','',1,'邮政快递包裹',4,'支付宝','','','','','','','',598.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0,0.00,0.00,598.00,0,'',1349053237,0,1349053237,0,0,0,0,'','group_buy',71,'','',0,'',0.00,0,0,0.00,NULL,1,0,0,0,0,0,0,0.00,NULL,NULL,NULL,1,0,0,0.00,NULL,0.00);
[/code]想得到如下结果
第一个问题<table>
<tr><td>年月</td><td>二次购买人数</td><td>二次购买订单数</td><td>二次购买总金额</td><td>本月全部购买人数</td><td>全部订单数</td><td>本月销售总金额</td></tr>
<tr><td>2013-01</td><td>4</td><td>22</td><td>555.00</td><td>46</td><td>124</td><td>58374.00</td></tr>
</table>第二个问题        
二次购买数据
<table>
<tr><td>UID</td><td>第一次购买时间</td><td>金额</td><td>第二次购买时间</td><td>金额</td><td>第N次购买时间</td><td>金额</td></tr>
<tr><td>13</td><td>2012-09-12</td><td>22</td><td>2012-11-04</td><td>461.00</td><td>2012-12-03</td><td>574.00</td></tr>
</table>
其中user_id 用户ID  add_time 购买时间  pay_status=2 下单成功  goods_amount 商品金额