thinkphp Db语句 mysql 时间戳转化成年月日列出

发布于：2022-01-04 16:03:04

时间戳以年月日列表列出

$subQuery = Db::name('serivce_record')
    ->field("serid,aged_id,staff_name,FROM_UNIXTIME(ruhu_time,'%Y-%m-%d') as ruhu_date,fullname as aged_name,fuwu_length")
    ->where($condition)
    ->buildSql();

统计数据

$datalist = Db::name('user')
    ->field("fullname as aged_name,IF(sex=1,'男','女') as sex,birthday,TIMESTAMPDIFF(YEAR,birthday,CURDATE()) AS age")
    ->where('company_id',$this->admin_company_id)
    ->where("MONTH(birthday) = ".$month)
    ->order('birthday')
    ->select()
    ->toArray();

统计数据

$bed_count = Db::name('user_bed')
    ->field("count(IF(aged_id<1,1,null)) as empty_beds,count(1) as beds_count,count(IF(aged_id>0,1,null)) as in_beds")
    ->where('company_id',$this->admin_company_id)
    ->find();

统计人数据

 $dbprefix=env('database.prefix', '');
        #服务对象年龄段分布统计
        $year1 = date('Y-m-d', strtotime("-60year", time()));#60以下
        $year2 = date('Y-m-d', strtotime("-65year", time()));#
        $year3 = date('Y-m-d', strtotime("-70year", time()));#
        $year4 = date('Y-m-d', strtotime("-75year", time()));#
        $year5 = date('Y-m-d', strtotime("-80year", time()));#
        $year6 = date('Y-m-d', strtotime("-90year", time()));#
        $year7 = date('Y-m-d', strtotime("-100year", time()));#
        $sql = <<<EOT
        SELECT
            nld AS '年龄段',
            count( * ) AS '人数',
            CONVERT(nld,SIGNED) as '排序' 
        FROM
            (
            SELECT
            CASE
                WHEN
                    birthday is NULL THEN
                        '未知'     
                WHEN
                    birthday>'{$year1}' THEN
                        '59岁以下' 
         
            WHEN birthday>'{$year2}' AND birthday<='{$year1}' THEN
               '60-64岁' 
               
            WHEN birthday>'{$year3}' AND birthday<='{$year2}' THEN
               '65-69岁'
               
            WHEN birthday>'{$year4}' AND birthday<='{$year3}' THEN
               '70-74岁'
               
             WHEN birthday>'{$year5}' AND birthday<='{$year4}' THEN
               '75-79岁'

            WHEN birthday>'{$year6}' AND birthday<='{$year5}' THEN
               '80-89岁'
            
            WHEN birthday>'{$year7}' AND birthday<='{$year6}' THEN
               '90-99岁'
            WHEN birthday <='{$year7}' THEN
               '100岁以上'
                  END AS nld 
            FROM
               {$dbprefix}aged
            where company_id=:companyid and fw_status=:fwstatus
            ) a 
         GROUP BY
            nld

EOT;
        $aged_nldarr=Db::query($sql, ['companyid' => $company_id,'fwstatus'=>JUJIA_FW_STATUS_ZQ]);

使用buildSql构造子查询

$subQuery = Db::table('think_user')
    ->field('id,name')
    ->where('id', '>', 10)
    ->buildSql();

生成的subQuery结果为：

( SELECT `id`,`name` FROM `think_user` WHERE `id` > 10 )

然后使用子查询构造新的查询：

Db::table($subQuery . ' a')
    ->where('a.name', 'like', 'thinkphp')
    ->order('id', 'desc')
    ->select();

参考 https://www.kancloud.cn/manual/thinkphp6_0/1037569