tp5框架构建子查询

$uid = $user['id'];

$sql1 = Db::table('sd_fund_comm_log')
    ->field('DATE(create_time) AS order_date')
    ->where('uid', $uid)
    ->whereOr('p1', (string)$uid)
    ->whereOr('p2', (string)$uid)
    ->whereOr('p3', (string)$uid)
    ->group('order_date')
    ->buildSql();

$sql2 = Db::table('sd_fund_comm_log')
    ->field('DATE(create_time) AS order_date, COUNT(*) AS my_order_count, SUM(CAST(amount AS DECIMAL(10,2))) AS my_total_amount')
    ->where('uid', $uid)
    ->group('order_date')
    ->buildSql();

$sql3 = Db::table('sd_fund_comm_log')
    ->field('DATE(create_time) AS order_date, COUNT(*) AS sub_order_count, SUM(CAST(amount AS DECIMAL(10,2))) AS sub_total_amount')
    ->where('p1', (string)$uid)
    ->whereOr('p2', (string)$uid)
    ->whereOr('p3', (string)$uid)
    ->group('order_date')
    ->buildSql();

$result = Db::table($sql1 . ' stats')
    ->field([
        'stats.order_date',
        'IFNULL(my.my_order_count, 0) AS my_order_count',
        'IFNULL(my.my_total_amount, 0.00) AS my_total_amount',
        'IFNULL(sub.sub_order_count, 0) AS sub_order_count',
        'IFNULL(sub.sub_total_amount, 0.00) AS sub_total_amount',
    ])
    ->join([$sql2 => 'my'], 'stats.order_date = my.order_date','left')
    ->join([$sql3 => 'sub'], 'stats.order_date = sub.order_date','left')
    ->order('stats.order_date')
    ->select();


原声的sql

SELECT
    stats.order_date,
    IFNULL(my.my_order_count, 0) AS my_order_count,
    IFNULL(my.my_total_amount, 0.00) AS my_total_amount,
    IFNULL(sub.sub_order_count, 0) AS sub_order_count,
    IFNULL(sub.sub_total_amount, 0.00) AS sub_total_amount
FROM
    (
        SELECT DATE(create_time) AS order_date
        FROM sd_fund_comm_log
        WHERE uid = :uid OR p1 = :uidStr OR p2 = :uidStr OR p3 = :uidStr
        GROUP BY DATE(create_time)
    ) AS stats
LEFT JOIN
    (
        SELECT
            DATE(create_time) AS order_date,
            COUNT(*) AS my_order_count,
            SUM(CAST(amount AS DECIMAL(10,2))) AS my_total_amount
        FROM sd_fund_comm_log
        WHERE uid = :uid
        GROUP BY DATE(create_time)
    ) AS my ON stats.order_date = my.order_date
LEFT JOIN
    (
        SELECT
            DATE(create_time) AS order_date,
            COUNT(*) AS sub_order_count,
            SUM(CAST(amount AS DECIMAL(10,2))) AS sub_total_amount
        FROM sd_fund_comm_log
        WHERE p1 = :uidStr OR p2 = :uidStr OR p3 = :uidStr
        GROUP BY DATE(create_time)
    ) AS sub ON stats.order_date = sub.order_date
ORDER BY stats.order_date