遇到的问题：

  • union后直接排序或分页报错 Incorrect usage of UNION and ORDER BY?
  • fetchSql得到的查询语句是没有”()”括起来的，
  • buildSql 得到的查询语句有”()”括起来的；

代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
$matField = "*"   // 查询的字段s
$matSql = Db::name('product')
    ->alias(['product' => 'p', 'match_goods' => 'b', 'match_goods_son' => 'c', 'area' => 'd'])
    ->field($matField)
    ->join('match_goods', 'b.product_id = p.product_id')
    ->join('match_goods_son', 'c.match_goods_id = b.match_goods_id')
    ->join('area', 'p.province = d.id')
    ->where($where)
    ->where(['p.type' => 3, 'p.status' => 2])
    ->buildSql();
$actField = '*' //字段
$data = Db::field($actField)
    ->name('product')
    ->alias(['product' => 'p', 'activity_goods' => 'b', 'activity_goods_son' => 'c', 'area' => 'd'])
    ->join('activity_goods', 'b.product_id = p.product_id')
    ->join('activity_goods_son', 'c.activity_goods_id = b.activity_goods_id')
    ->join('area', 'p.province = d.id')
    ->where($where)
    ->where(['p.type' => 2, 'p.status' => 2])
    ->union($matSql, true)
    ->select(false);
#注意：$matField 和$actField 字段名称要一致，且顺序也要一致
$sql = "($data)";
$res = Db::table($sql.' as a')->field('xx') // 这里xx与$matField 和$actField 字段名称要一致
    ->order('a.sort DESC, a.sign_start_time DESC')
    ->group('a.product_id')
    ->page(1, 10)
    ->select()->toArray();

关联查询(JOIN)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
$model->alias('a')
      ->join('admin_users u','a.admin_id = u.id','LEFT')
      ->whereIn('u.type',[1,2]);

//子查询(用group 把用户分组，统计签到表中每个用户签到的次数)
$activitySign = Db::name('activity_sign')
      ->field('member_id,count(member_id) sign_count')
      ->group('member_id')
      ->buildSql();
//子查询(用group 把用户分组，统计签到表中每个用户报名的次数)
$activityPar = Db::name('activity_participant')
      ->field('member_id,count(member_id) count')
      ->group('member_id')
      ->buildSql();
//用户主表(子查询中统计的值，可以直接在field中取出，并不需要再次统计)
$list = Db::name('member')
      ->alias('m')
      ->field('m.member_id,sign_count,count,m.member_nickname')
      ->join([$activitySign=> 'as'], 'm.member_id = as.member_id','left')
      ->join([$activityPar=> 'ap'], 'm.member_id = ap.member_id','left')
      ->paginate();

SQL中UNION使用实例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT id,name,age,classid,score
FROM tb_Student
WHERE age<=20
UNION ALL
SELECT id,name,age,classid,score
FROM tb_Student
WHERE score<90

/* 查询结果中存在重复的记录，要想查询结果中不存在重复的记录,仅仅去掉了ALL关键字而已 */

SELECT id,name,age,classid,score
FROM tb_Student
WHERE age<=20
UNION
SELECT id,name,age,classid,score
FROM tb_Student
WHERE score<90