但这种思路有问题，实现不了后期店铺拆单功能。物流、发货、评价、售后独立等等功能均无法实现。
但鉴于我们是一个收款账号，有跨店活动、优惠券等功能，付款又需要合并订单，不能拆单。因此我们的需要需要第二种思路来实现；
第二种思路，是mysql判断当前订单如果status=1时合并，但其它状态拆单。实现了合并付款，后期拆单的理想情况
第一种思路代码：
/**
* 订单数据（可搜索翻页）
* 应用场景：我的订单（用户端）
* @param int $status 状态（为0表示所有状态下的数据）
* @param int $shop_id 店铺ID
* @param int $member_id 会员ID
* @param int $keyword 关键词
* @param int $p 页码（查询第几页的数据）
* @return
*/
public function searchCopy($data,$p=1)
{
$shop_id = (int)$data['shop_id'];
$status = (int)$data['status'];
$keyword = $data['keyword'];
$member_id = (int)$data['member_id'];
$where = array();
$where['a.isdelete'] = array('eq', 2);
$where['b.isdelete'] = array('eq', 2);
if($shop_id > 0)
$where['a.shop_id'] = array('eq', $shop_id);
if($member_id > 0)
$where['b.member_id'] = array('eq', $member_id);
//订单状态
if($status>0)
{
if($status == 1)//这里不含已失效
{
$losetime = time()-C("WPF_ORDER_LOSETIME")*3600;
$where['b.addtime'] = array('gt', $losetime);
}
if($status != 6)
$where['a.status'] = array('eq', $status);
else
$where['a.status'] = array('neq', 6);
}
else
$where['a.status'] = array('neq', 6);
//关键词搜索
if($keyword != '0')
{
$whereb['b.sn'] = array('like', "%$keyword%");
$whereb['a.name'] = array('like', "%$keyword%");
$whereb['_logic'] = 'OR';
$where['_complex'] = $whereb;
}
$orderby = 'b.addtime';
$orderway = 'DESC';
$db_prefix = C('DB_PREFIX');//表前缀
// 取总的记录数
$countArr = $this->alias('a')->field("count(DISTINCT a.order_id) as c")->join("LEFT JOIN ".$db_prefix."order as b ON a.order_id=b.id")->where($where)->find();
$count = $countArr['c'];
$perpage = 10;//每页多少条记录
$firstRow = ($p-1)*$perpage;//当前页第一条数据
//$thisData = $this->field("b.id,group_concat(a.id separator ',') as os_ids,b.type,b.sn,b.name,b.addtime,a.status,a.shop_id")->alias('a')->join("LEFT JOIN ".$db_prefix."order as b ON a.order_id=b.id")->where($where)->order("$orderby $orderway")->group('b.id')->limit($firstRow.','.$perpage)->select();
$thisData = $this->field("a.id,b.id as order_id,a.status")->alias('a')->join("LEFT JOIN ".$db_prefix."order as b ON a.order_id=b.id")->where($where)->order("$orderby $orderway")->limit($firstRow.','.$perpage)->group('b.id')->select();
echo $this->getLastSql();
if(is_null($thisData))
$thisData = array();
foreach ($thisData as $k=>$v)
{
//$thisData[$k] = $this->formatDataShop($v,2);
}
$result['data'] = $thisData;
$result['count'] = $count;
$result['pagenum'] = ceil($count/$perpage);
return $result;
}第二种思路代码：/**
* 订单数据（可搜索翻页）
* 应用场景：我的订单（用户端）
* @param int $status 状态（为0表示所有状态下的数据）
* @param int $shop_id 店铺ID
* @param int $member_id 会员ID
* @param int $keyword 关键词
* @param int $p 页码（查询第几页的数据）
* @return
*/
public function search($data,$p=1)
{
$shop_id = (int)$data['shop_id'];
$status = (int)$data['status'];
$keyword = $data['keyword'];
$member_id = (int)$data['member_id'];
$where = array();
$where['a.isdelete'] = array('eq', 2);
$where['b.isdelete'] = array('eq', 2);
if($shop_id > 0)
$where['a.shop_id'] = array('eq', $shop_id);
if($member_id > 0)
$where['b.member_id'] = array('eq', $member_id);
//订单状态
if($status>0)
{
if($status == 1)//这里不含已失效
{
$losetime = time()-C("WPF_ORDER_LOSETIME")*3600;
$where['b.addtime'] = array('gt', $losetime);
}
if($status != 6)
$where['a.status'] = array('eq', $status);
else
$where['a.status'] = array('neq', 6);
}
else
$where['a.status'] = array('neq', 6);
//关键词搜索
if($keyword != '0')
{
$whereb['b.sn'] = array('like', "%$keyword%");
$whereb['a.name'] = array('like', "%$keyword%");
$whereb['_logic'] = 'OR';
$where['_complex'] = $whereb;
}
$orderby = 'b.addtime';
$orderway = 'DESC';
$db_prefix = C('DB_PREFIX');//表前缀
// 取总的记录数
$count = M()
->table("((SELECT *,group_concat(id separator ',') as os_ids FROM wpf_order_shop where status=1 group by order_id) union (select *,id as os_ids from wpf_order_shop where status!=1 )) as a")
->join("LEFT JOIN ".$db_prefix."order as b ON a.order_id=b.id")
->where($where)
->count();
$perpage = 10;//每页多少条记录
$firstRow = ($p-1)*$perpage;//当前页第一条数据
$thisData = M()
->table("((SELECT *,group_concat(id separator ',') as os_ids FROM wpf_order_shop where status=1 group by order_id) union (select *,id as os_ids from wpf_order_shop where status!=1 )) as a")
->join("LEFT JOIN ".$db_prefix."order as b ON a.order_id=b.id")
->field("a.id,b.id as order_id,a.status,b.type,b.sn,b.name,b.addtime,a.shop_id,a.os_ids")
->where($where)
->select();
if(is_null($thisData))
$thisData = array();
foreach ($thisData as $k=>$v)
{
//$thisData[$k] = $this->formatDataShop($v,2);
}
$result['data'] = $thisData;
$result['count'] = $count;
$result['pagenum'] = ceil($count/$perpage);
return $result;
}总结：核心SQL语句：
select * from ((SELECT id,order_id,status FROM wpf_order_shop where status = 1 group by order_id) union (select id,order_id,status from wpf_order_shop where status != 1 )) as a form ....
组装前的数据
第二种思路得到我想要的数据
最佳答案
