image.png

    Navicat 查询构造器

    image.png
    SELECT
    FROM_UNIXTIME(jointime,’%Y-%m-%d’) jointime,a.username,b.nickname,
    COUNT(a.id),b.id as admin_id
    FROM oil_user a LEFT JOIN oil_admin b
    ON a.channel_id=b.id
    WHERE jointime BETWEEN 1558713600 AND 1559318399
    AND channel_id IN (49)
    GROUP BY FROM_UNIXTIME(jointime , ‘%Y%m%d’),channel_id ORDER BY jointime

    Thinkphp查询语句

    $list = Db::name(‘user’)->fetchSql() ->alias(‘a’) ->field(‘FROM_UNIXTIME(jointime,\’%Y-%m-%d\’) jointime,a.username,b.nickname,COUNT(a.id),b.id as admin_id’) ->where($where) ->where($channel) ->group(“ FROM_UNIXTIME(jointime , ‘%Y%m%d’),channel_id”) ->join(‘admin b’,’a.channel_id=b.id’,’left’) ->order(‘jointime’) ->select();