thinkphp mysql查询 中一些复制查询写法整理

按天统计的查询 案例

$orderList = Order::where('paytime', 'between time', [$starttime, $endtime])
            ->field('paytime, status, SUM(payamount) AS amount, MIN(paytime) AS min_paytime, MAX(paytime) AS max_paytime, 
            DATE_FORMAT(FROM_UNIXTIME(createtime), "' . $format . '") AS pay_date')
            ->group('pay_date')
            ->select();

获取今天数据

Company::whereTime('create_time','today')->count()

获取昨天数据

Company::whereTime('create_time','-1 day')->whereTime('create_time','<','today')->count()

mysql中如何进行先排序再分组统计操作?

//本示例演示将订单数据按时间降序,然后在根据类名ID进行分组统计总金额/总订单量/最近一次交易额等
$subQuery = Db::name('crorder')->order('create_time desc')->buildSql();
        $list = Db::table($subQuery.' a')->field('a.cr_id,a.create_time,a.user_id,a.money,SUM(a.money) as sum_money,COUNT(a.cr_id) as cr_count')->group('cr_id')->select();

MySQL以逗号隔开的字符串查询方式整理

1 单个值查询使用函数进行处理，FIND_IN_SET()实用

SELECT * FROM `by_info` WHERE FIND_IN_SET('7',type_id);

2 多个值模糊查询，使用正则，此方式相当于 ( LIKE '%1%' OR LIKE '%2%' OR LIKE '%5%' )

SELECT * FROM `by_info` WHERE type_id REGEXP '(1|2|5)';

3 多个值匹配查询，使用正则

SELECT * FROM `by_info` WHERE type_id REGEXP '(^|,)(1|12|5|9)(,|$)';