本文介绍了一种使用SQL语句统计多个按日期分表的数据的方法，通过动态生成表名并检查表存在性，实现了对近七天数据的有效查询。文章详细展示了如何根据不同交易类型和来源查询各表中特定字段的总和。
说明:由于数据量过大，于是一些类似流水表的记录被分开，按照 orderist_20190806 的规律，每天一张表被分开了，现在要统计这些表的数据，如何实现？
关键点:SQL语句是一种很严谨且逻辑性很强的东西，对于不存在的表，执行查询时，不会给出警告，而是直接报错停止
于是在查询时，我们需要先判断表是否存在，再进行操作
并且链表查询肯定是不行的，数据会多到数据库直接卡死，只能一张一张的查询
上代码
注意:由于多次查询数据库，该段代码会导致SQL语句很多，最多只能进行最近七天查询，否则会很消耗性能！
public function time($time_type)
{
switch ($time_type) {
case 1: //今日
$time_begin = (date('Ymd'));
$time_end = (date('Ymd'));
break;
case 2: //昨日
$time_begin = date('Ymd', strtotime('-1 day'));
$time_end = date('Ymd', strtotime('-1 day'));
break;
case 3: //day-2
$time_begin = date('Ymd', strtotime('-2 day'));
$time_end = date('Ymd', strtotime('-2 day'));
break;
case 4: //day-3
$time_begin = date('Ymd', strtotime('-3 day'));
$time_end = date('Ymd', strtotime('-3 day'));
break;
case 5: //day-4
$time_begin = date('Ymd', strtotime('-4 day'));
$time_end = date('Ymd', strtotime('-4 day'));
break;
case 6: //day-5
$time_begin = date('Ymd', strtotime('-5 day'));
$time_end = date('Ymd', strtotime('-5 day'));
break;
case 7: //day-6
$time_begin = date('Ymd', strtotime('-6 day'));
$time_end = date('Ymd', strtotime('-6 day'));
break;
case 8: //本月
$time_begin = date('Ymd', mktime(0, 0, 0, date('m'), 1, date('Y')));
$time_end = date('Ymd', mktime(23, 59, 59, date('m'), date('t'), date('Y')));
break;
case 9: //本年 年初到现在
$time_begin = date('Ymd', mktime(0, 0, 0, 1, 1, date('Y')));
$time_end = date('Ymd', strtotime(date('Y-m-d') . " 23:59:59"));
break;
case 10: //最近七天
// $date = mktime(0, 0, 0, date('m'), date('d') + 1, date('Y')) - 1;
// $date = date('Y-m-d');
// $w = date('w', strtotime(date('Y-m-d')));
// $week_begin = (date('Y-m-d', strtotime("$date -" . ($w ? $w - 1 : 6) . ' days')));
// $week_end = date('Y-m-d', strtotime("$week_begin +6 days"));
$time_begin = date('Ymd', strtotime('-7 day'));
$time_end = date('Ymd');
break;
}
return ['time_begin' => $time_begin, 'time_end' => $time_end];
}
public function week()
{
for ($k = 1; $k <= 7; $k++) {
$time = $this->time($k);
$db = Basice::connect(); //继承的Basice中的数据库配置
$table_name = "vcflow_" . $time['time_begin'] . "";
$is_table_have = $db->query('SHOW TABLES LIKE ' . "'" . $table_name . "'");
if ($is_table_have) {
//全部获得的 (k 收入)
$complete_get_k[$k] = $db->query("SELECT sum(num) FROM `vcflow_" . $time['time_begin'] . "` WHERE vcid=9999 AND aor=2 AND cat<>22");
//全部支出 (k 支出)
$complete_out_k[$k] = $db->query("SELECT sum(num) FROM `vcflow_" . $time['time_begin'] . "` WHERE vcid=9999 AND aor=1 AND cat<>22");
//全部获得的 (s 收入)
$complete_get_s[$k] = $db->query("SELECT sum(num) FROM `vcflow_" . $time['time_begin'] . "` WHERE vcid=8888 AND aor=2");
//全部支出 (s 支出)
$complete_out_s[$k] = $db->query("SELECT sum(num) FROM `vcflow_" . $time['time_begin'] . "` WHERE vcid=8888 AND aor=1");
//挂卖的 (k 支出) cat=1
$complete_hang_k[$k] = $db->query("SELECT sum(num) FROM `vcflow_" . $time['time_begin'] . "` WHERE vcid=9999 AND aor=1 AND cat=1");
//取消的挂售的 (k 收入) cat=2
$complete_cancel_hang_k[$k] = $db->query("SELECT sum(num) FROM `vcflow_" . $time['time_begin'] . "` WHERE vcid=9999 AND aor=1 AND cat=2");
//挂售 (s 支出) cat=3
$complete_auction_s[$k] = $db->query("SELECT sum(num) FROM `vcflow_" . $time['time_begin'] . "` WHERE vcid=8888 AND aor=1 AND cat=3");
//取消挂售 (s 收入) cat=4
$complete_cancel_auction_s[$k] = $db->query("SELECT sum(num) FROM `vcflow_" . $time['time_begin'] . "` WHERE vcid=8888 AND aor=2 AND cat=4");
//卖出k给求购的 (k 支出) cat=5
$complete_sell_k[$k] = $db->query("SELECT sum(num) FROM `vcflow_" . $time['time_begin'] . "` WHERE vcid=9999 AND aor=1 AND cat=5");
//手动取消卖出k给求购 (k 收入) cat=6
$complete_cancel_sell_k[$k] = $db->query("SELECT sum(num) FROM `vcflow_" . $time['time_begin'] . "` WHERE vcid=9999 AND aor=2 AND cat=6");
//超时取消卖出k个求购 (k 收入) cat=7
$complete_timeout_sell_k[$k] = $db->query("SELECT sum(num) FROM `vcflow_" . $time['time_begin'] . "` WHERE vcid=9999 AND aor=2 AND cat=7");
//通过积分兑换获得的k (k 收入) cat=8
$complete_coin_get_k[$k] = $db->query("SELECT sum(num) FROM `vcflow_" . $time['time_begin'] . "` WHERE vcid=9999 AND aor=2 AND cat=8");
//通过积分兑换支出的k (k 支出) cat=8
$complete_coin_out_k[$k] = $db->query("SELECT sum(num) FROM `vcflow_" . $time['time_begin'] . "` WHERE vcid=9999 AND aor=1 AND cat=8");
//通过积分兑换获得的s (s 收入) cat=8
$complete_coin_get_s[$k] = $db->query("SELECT sum(num) FROM `vcflow_" . $time['time_begin'] . "` WHERE vcid=8888 AND aor=2 AND cat=8");
//通过积分兑换支出的s (s 支出) cat=8
$complete_coin_out_s[$k] = $db->query("SELECT sum(num) FROM `vcflow_" . $time['time_begin'] . "` WHERE vcid=8888 AND aor=1 AND cat=8");
//其他途径返还的k (k 收入) cat=9
$complete_ruling_get_k[$k] = $db->query("SELECT sum(num) FROM `vcflow_" . $time['time_begin'] . "` WHERE vcid=9999 AND aor=2 AND cat=9");
//其他途径返还的s (s 收入) cat=9
$complete_ruling_get_s[$k] = $db->query("SELECT sum(num) FROM `vcflow_" . $time['time_begin'] . "` WHERE vcid=8888 AND aor=2 AND cat=9");
//机器基础 (k 收入) cat=10
$complete_machine_basic_k[$k] = $db->query("SELECT sum(num) FROM `vcflow_" . $time['time_begin'] . "` WHERE vcid=9999 AND aor=2 AND cat=10");
//机器加速 (k 收入) cat=11
$complete_machine_accelerate_k[$k] = $db->query("SELECT sum(num) FROM `vcflow_" . $time['time_begin'] . "` WHERE vcid=9999 AND aor=2 AND cat=11");
//机器加速 (k 支出) cat=12
$complete_machine_accelerateing_k[$k] = $db->query("SELECT sum(num) FROM `vcflow_" . $time['time_begin'] . "` WHERE vcid=9999 AND aor=1 AND cat=12");
//机器减速 (k 收入) cat=13
$complete_machine_decelerate_k[$k] = $db->query("SELECT sum(num) FROM `vcflow_" . $time['time_begin'] . "` WHERE vcid=9999 AND aor=2 AND cat=13");
//激活用户 (k 支出) cat=14
$complete_activate_user_k[$k] = $db->query("SELECT sum(num) FROM `vcflow_" . $time['time_begin'] . "` WHERE vcid=9999 AND aor=1 AND cat=14");
//修改昵称 (k 支出) cat=15
$complete_rename_k[$k] = $db->query("SELECT sum(num) FROM `vcflow_" . $time['time_begin'] . "` WHERE vcid=9999 AND aor=1 AND cat=15");
//抽奖存入 (s 支出) cat=16
$complete_deposit_computerized_s[$k] = $db->query("SELECT sum(num) FROM `vcflow_" . $time['time_begin'] . "` WHERE vcid=8888 AND aor=1 AND cat=16");
//抽奖退回 (s 收入) cat=17
$complete_return_computerized_s[$k] = $db->query("SELECT sum(num) FROM `vcflow_" . $time['time_begin'] . "` WHERE vcid=8888 AND aor=2 AND cat=17");
//其他积分 (s 收入) cat=18
$complete_calculation_income_s[$k] = $db->query("SELECT sum(num) FROM `vcflow_" . $time['time_begin'] . "` WHERE vcid=8888 AND aor=2 AND cat=18");
//分享 (s 收入) cat=19
$complete_share_income_s[$k] = $db->query("SELECT sum(num) FROM `vcflow_" . $time['time_begin'] . "` WHERE vcid=8888 AND aor=2 AND cat=19");
//团队 (s 收入) cat=20
$complete_team_income_s[$k] = $db->query("SELECT sum(num) FROM `vcflow_" . $time['time_begin'] . "` WHERE vcid=8888 AND aor=2 AND cat=20");
//k释放到可用 (k 收入) cat=21
$complete_freed_k[$k] = $db->query("SELECT sum(num) FROM `vcflow_" . $time['time_begin'] . "` WHERE vcid=9999 AND aor=2 AND cat=21");
//冻结k释放 (冻结k 支出) cat=22
$complete_freeze_freed_k[$k] = $db->query("SELECT sum(num) FROM `vcflow_" . $time['time_begin'] . "` WHERE vcid=9999 AND aor=1 AND cat=22");
//冻结k收入 (冻结k 收入) cat=22
$complete_income_freeze_k[$k] = $db->query("SELECT sum(num) FROM `vcflow_" . $time['time_begin'] . "` WHERE vcid=9999 AND aor=2 AND cat=22");
//第三种积分收入 (第三种积分 收入) cat=23
$complete_income_share[$k] = $db->query("SELECT sum(num) FROM `vcflow_" . $time['time_begin'] . "` WHERE cat=23 AND aor=2");
//第三种积分收入 (第三种积分 支出) cat=23
$complete_out_share[$k] = $db->query("SELECT sum(num) FROM `vcflow_" . $time['time_begin'] . "` WHERE cat=23 AND aor=1");
// dump($complete_get_k[$k][0]["sum(num)"]);exit();
$complete[$k] = array();
$complete[$k] = [
'get_k' => $complete_get_k[$k][0]["sum(num)"] //全部获得的 (k 收入)
, 'out_k' => $complete_out_k[$k][0]["sum(num)"] //全部支出 (k 支出)
, 'get_s' => $complete_get_s[$k][0]["sum(num)"] //全部获得的 (s 收入)
, 'out_s' => $complete_out_s[$k][0]["sum(num)"] //全部支出 (s 支出)
, 'hang_k' => $complete_hang_k[$k][0]["sum(num)"] //挂卖的 (k 支出) cat=1
, 'cancel_hang_k' => $complete_cancel_hang_k[$k][0]["sum(num)"] //取消的挂售的 (k 收入) cat=2
, 'auction_s' => $complete_auction_s[$k][0]["sum(num)"] //挂售 (s 支出) cat=3
, 'cancel_auction_s' => $complete_cancel_auction_s[$k][0]["sum(num)"]//取消挂售 (s 收入) cat=4
, 'sell_k' => $complete_sell_k[$k][0]["sum(num)"]//卖出k给求购的 (k 支出) cat=5
, 'cancel_sell_k' => $complete_cancel_sell_k[$k][0]["sum(num)"]//手动取消卖出k给求购 (k 收入) cat=6
, 'timeout_sell_k' => $complete_timeout_sell_k[$k][0]["sum(num)"]//超时取消卖出k个求购 (k 收入) cat=7
, 'coin_get_k' => $complete_coin_get_k[$k][0]["sum(num)"]//通过积分兑换获得的k (k 收入) cat=8
, 'coin_out_k' => $complete_coin_out_k[$k][0]["sum(num)"]//通过积分兑换支出的k (k 支出) cat=8
, 'coin_get_s' => $complete_coin_get_s[$k][0]["sum(num)"]//通过积分兑换获得的s (s 收入) cat=8
, 'coin_out_s' => $complete_coin_out_s[$k][0]["sum(num)"]//通过积分兑换支出的s (s 支出) cat=8
, 'ruling_get_k' => $complete_ruling_get_k[$k][0]["sum(num)"]//其他途径返还的k (k 收入) cat=9
, 'ruling_get_s' => $complete_ruling_get_s[$k][0]["sum(num)"]//其他途径返还的s (s 收入) cat=9
, 'machine_basic_k' => $complete_machine_basic_k[$k][0]["sum(num)"]//机器基础 (k 收入) cat=10
, 'machine_accelerate_k' => $complete_machine_accelerate_k[$k][0]["sum(num)"]//机器加速 (k 收入) cat=11
, 'machine_accelerateing_k' => $complete_machine_accelerateing_k[$k][0]["sum(num)"]//机器加速 (k 支出) cat=12
, 'machine_decelerate_k' => $complete_machine_decelerate_k[$k][0]["sum(num)"]//机器减速 (k 收入) cat=13
, 'activate_user_k' => $complete_activate_user_k[$k][0]["sum(num)"]//激活用户 (k 支出) cat=14
, 'rename_k' => $complete_rename_k[$k][0]["sum(num)"]//修改昵称 (k 支出) cat=15
, 'deposit_computerized_s' => $complete_deposit_computerized_s[$k][0]["sum(num)"]//抽奖存入 (s 支出) cat=16
, 'return_computerized_s' => $complete_return_computerized_s[$k][0]["sum(num)"]//抽奖退回 (s 收入) cat=17
, 'calculation_income_s' => $complete_calculation_income_s[$k][0]["sum(num)"]//其他积分 (s 收入) cat=18
, 'share_income_s' => $complete_share_income_s[$k][0]["sum(num)"]//分享 (s 收入) cat=19
, 'team_income_s' => $complete_team_income_s[$k][0]["sum(num)"]//团队 (s 收入) cat=20
, 'freed_k' => $complete_freed_k[$k][0]["sum(num)"]//k释放到可用 (k 收入) cat=21
, 'freeze_freed_k' => $complete_freeze_freed_k[$k][0]["sum(num)"]//冻结k释放 (冻结k 支出) cat=22
, 'income_freeze_k' => $complete_income_freeze_k[$k][0]["sum(num)"]//冻结k收入 (冻结k 收入) cat=22
, 'income_share' => $complete_income_share[$k][0]["sum(num)"]//第三种积分收入 (第三种积分 收入) cat=23
, 'out_share' => $complete_out_share[$k][0]["sum(num)"]//第三种积分收入 (第三种积分 支出) cat=23
];
} else {
$complete[$k] = array();
$complete[$k] = [
'get_k' => 0 //全部获得的 (k 收入)
, 'out_k' => 0 //全部支出 (k 支出)
, 'get_s' => 0 //全部获得的 (s 收入)
, 'out_s' => 0 //全部支出 (s 支出)
, 'hang_k' => 0 //挂卖的 (k 支出) cat=1
, 'cancel_hang_k' => 0 //取消的挂售的 (k 收入) cat=2
, 'auction_s' => 0 //挂售 (s 支出) cat=3
, 'cancel_auction_s' => 0//取消挂售 (s 收入) cat=4
, 'sell_k' => 0//卖出k给求购的 (k 支出) cat=5
, 'cancel_sell_k' => 0//手动取消卖出k给求购 (k 收入) cat=6
, 'timeout_sell_k' => 0//超时取消卖出k个求购 (k 收入) cat=7
, 'coin_get_k' => 0//通过积分兑换获得的k (k 收入) cat=8
, 'coin_out_k' => 0//通过积分兑换支出的k (k 支出) cat=8
, 'coin_get_s' => 0//通过积分兑换获得的s (s 收入) cat=8
, 'coin_out_s' => 0//通过积分兑换支出的s (s 支出) cat=8
, 'ruling_get_k' => 0//其他途径返还的k (k 收入) cat=9
, 'ruling_get_s' => 0//其他途径返还的s (s 收入) cat=9
, 'machine_basic_k' => 0//机器基础 (k 收入) cat=10
, 'machine_accelerate_k' => 0//机器加速 (k 收入) cat=11
, 'machine_accelerateing_k' => 0//机器加速 (k 支出) cat=12
, 'machine_decelerate_k' => 0//机器减速 (k 收入) cat=13
, 'activate_user_k' => 0//激活用户 (k 支出) cat=14
, 'rename_k' => 0//修改昵称 (k 支出) cat=15
, 'deposit_computerized_s' => 0//抽奖存入 (s 支出) cat=16
, 'return_computerized_s' => 0//抽奖退回 (s 收入) cat=17
, 'calculation_income_s' => 0//其他积分 (s 收入) cat=18
, 'share_income_s' => 0//分享 (s 收入) cat=19
, 'team_income_s' => 0//团队 (s 收入) cat=20
, 'freed_k' => 0//k释放到可用 (k 收入) cat=21
, 'freeze_freed_k' => 0//冻结k释放 (冻结k 支出) cat=22
, 'income_freeze_k' => 0//冻结k收入 (冻结k 收入) cat=22
, 'income_share' => 0//第三种积分收入 (第三种积分 收入) cat=23
, 'out_share' => 0//第三种积分收入 (第三种积分 支出) cat=23
];
}
}
$row = [
'get_k' => 0 //全部获得的 (k 收入)
, 'out_k' => 0 //全部支出 (k 支出)
, 'get_s' => 0 //全部获得的 (s 收入)
, 'out_s' => 0 //全部支出 (s 支出)
, 'hang_k' => 0 //挂卖的 (k 支出) cat=1
, 'cancel_hang_k' => 0 //取消的挂售的 (k 收入) cat=2
, 'auction_s' => 0 //挂售 (s 支出) cat=3
, 'cancel_auction_s' => 0//取消挂售 (s 收入) cat=4
, 'sell_k' => 0//卖出k给求购的 (k 支出) cat=5
, 'cancel_sell_k' => 0//手动取消卖出k给求购 (k 收入) cat=6
, 'timeout_sell_k' => 0//超时取消卖出k个求购 (k 收入) cat=7
, 'coin_get_k' => 0//通过积分兑换获得的k (k 收入) cat=8
, 'coin_out_k' => 0//通过积分兑换支出的k (k 支出) cat=8
, 'coin_get_s' => 0//通过积分兑换获得的s (s 收入) cat=8
, 'coin_out_s' => 0//通过积分兑换支出的s (s 支出) cat=8
, 'ruling_get_k' => 0//其他途径返还的k (k 收入) cat=9
, 'ruling_get_s' => 0//其他途径返还的s (s 收入) cat=9
, 'machine_basic_k' => 0//机器基础 (k 收入) cat=10
, 'machine_accelerate_k' => 0//机器加速 (k 收入) cat=11
, 'machine_accelerateing_k' => 0//机器加速 (k 支出) cat=12
, 'machine_decelerate_k' => 0//机器减速 (k 收入) cat=13
, 'activate_user_k' => 0//激活用户 (k 支出) cat=14
, 'rename_k' => 0//修改昵称 (k 支出) cat=15
, 'deposit_computerized_s' => 0//抽奖存入 (s 支出) cat=16
, 'return_computerized_s' => 0//抽奖退回 (s 收入) cat=17
, 'calculation_income_s' => 0//其他积分 (s 收入) cat=18
, 'share_income_s' => 0//分享 (s 收入) cat=19
, 'team_income_s' => 0//团队 (s 收入) cat=20
, 'freed_k' => 0//k释放到可用 (k 收入) cat=21
, 'freeze_freed_k' => 0//冻结k释放 (冻结k 支出) cat=22
, 'income_freeze_k' => 0//冻结k收入 (冻结k 收入) cat=22
, 'income_share' => 0//第三种积分收入 (第三种积分 收入) cat=23
, 'out_share' => 0//第三种积分收入 (第三种积分 支出) cat=23
];
foreach ($complete as $k => $v) {
$row['get_k'] += $v['get_k'];
$row['out_k'] += $v['out_k'];
$row['get_s'] += $v['get_s'];
$row['out_s'] += $v['out_s'];
$row['hang_k'] += $v['hang_k'];
$row['cancel_hang_k'] += $v['cancel_hang_k'];
$row['auction_s'] += $v['auction_s'];
$row['cancel_auction_s'] += $v['cancel_auction_s'];
$row['sell_k'] += $v['sell_k'];
$row['cancel_sell_k'] += $v['cancel_sell_k'];
$row['timeout_sell_k'] += $v['timeout_sell_k'];
$row['coin_get_k'] += $v['coin_get_k'];
$row['coin_out_k'] += $v['coin_out_k'];
$row['coin_get_s'] += $v['coin_get_s'];
$row['coin_out_s'] += $v['coin_out_s'];
$row['ruling_get_k'] += $v['ruling_get_k'];
$row['ruling_get_s'] += $v['ruling_get_s'];
$row['machine_basic_k'] += $v['machine_basic_k'];
$row['machine_accelerate_k'] += $v['machine_accelerate_k'];
$row['machine_accelerateing_k'] += $v['machine_accelerateing_k'];
$row['machine_decelerate_k'] += $v['machine_decelerate_k'];
$row['activate_user_k'] += $v['activate_user_k'];
$row['rename_k'] += $v['rename_k'];
$row['deposit_computerized_s'] += $v['deposit_computerized_s'];
$row['return_computerized_s'] += $v['return_computerized_s'];
$row['calculation_income_s'] += $v['calculation_income_s'];
$row['share_income_s'] += $v['share_income_s'];
$row['team_income_s'] += $v['team_income_s'];
$row['freed_k'] += $v['freed_k'];
$row['freeze_freed_k'] += $v['freeze_freed_k'];
$row['income_freeze_k'] += $v['income_freeze_k'];
$row['income_share'] += $v['income_share'];
$row['out_share'] += $v['out_share'];
}
$this->assign('complete', $row);
return $this->fetch('day');
}
扫一扫
282
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
