By

文章目录
  1. 1. 正文
  2. 2. 参考文献

PS：SQL进行复杂条件运算，连表，内连，交集，并集等。

正文

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

//设置查询区间为一天
$unix_end_time = $unix_start_time+86400;

//查询当天下单并且已购买的用户
$condition1 = "SELECT mobile,total_amount FROM $this->_order_table  WHERE create_time > $unix_start_time AND create_time <=$unix_end_time AND status = 4";

//查询当天下单并且未购买的用户
$condition2 = "SELECT mobile,pid,total_amount FROM $this->_order_table  WHERE create_time > $unix_start_time AND create_time <=$unix_end_time AND status != 4 " ;

//取当天下单并且已购买和当天下单未购买的用户交集,得到同一手机号重复下单用户
$condition3 = "SELECT table2.mobile FROM ($condition1) AS table1 INNER JOIN ($condition2)AS table2 ON table1.mobile = table2.mobile";

//取$condition2 和 $condition3 的差集,得到下单未购买的发券精准用户,并取所下单金额的最大值
$condition = "SELECT table3.mobile,pid,MAX(table3.total_amount) FROM ($condition2) AS table3 WHERE table3.mobile NOT IN ($condition3) GROUP BY table3.mobile";

$result = $this->_db->query($condition);

参考文献

文章目录
  1. 1. 正文
  2. 2. 参考文献