教育 - 合并 SQL 查询(全部联盟示例)

Question

我在编程时正在努力提高 SQL 水平，并希望将以下 3 个 SQL 查询/代码合并到一个 SQL 调用中 - 可能吗？（我意识到它不会 100% 格式化，因为我有它专门monthly_totals但如果这也有可能的话......？

注意：下面提供的代码应该可以让您了解我的数据库的结构以及我想要完成的任务。

$ret = array(
    'raw' => array(),
    'total' => '0.00',
    'monthly_totals' => array()
);
if (!empty($sales_rep_id)) {
    $orders = $this->query("
            SELECT
                id,
                invoice_no,
                CONCAT(firstname, ' ', lastname) as name,
                affiliate_id,
                commission,
                YEAR(created) as created_year,
                MONTH(created) as created_month,
                created
            FROM orders
            WHERE 
                active = 1 AND
                affiliate_id = {$sales_rep_id}
            ORDER BY MONTH(created) DESC
                    ");
    if (!empty($orders)) {
        $total = $this->query("
                SELECT SUM(commission) as commission_total
                FROM orders
                WHERE
                    active = 1 AND
                    affiliate_id = {$sales_rep_id} 
               ");
        $raw_monthly_totals = $this->query("
            SELECT
                MONTH(created) as month_no,
                SUM(commission) as monthly_commission
            FROM orders
            WHERE 
                active = 1 AND
                affiliate_id = {$sales_rep_id}
            GROUP BY MONTH(created)
                ");
        $monthly_totals = array();
        foreach ($raw_monthly_totals as $tot) {
            $monthly_totals[$tot['month_no']] = $tot['monthly_commission'];
        }
        $ret['raw'] = $orders;
        $ret['total'] = $total[0]['commission_total'];
        $ret['monthly_totals'] = $monthly_totals;
    }
}
return $ret;

电流输出

array(4) {
  ["raw"]=>
  array(5) {
    [0]=>
    array(8) {
      ["id"]=>
      string(4) "5335"
      ["invoice_no"]=>
      string(15) "5335-1395260183"
      ["name"]=>
      string(10) "First Last"
      ["affiliate_id"]=>
      string(1) "6"
      ["commission"]=>
      string(6) "100.88"
      ["created_year"]=>
      string(4) "2014"
      ["created_month"]=>
      string(1) "3"
      ["created"]=>
      string(19) "2014-03-19 14:16:23"
    }
    [1]=>
    array(8) {
      ["id"]=>
      string(4) "5373"
      ["invoice_no"]=>
      string(15) "5373-1396031594"
      ["name"]=>
      string(10) "First Last"
      ["affiliate_id"]=>
      string(1) "6"
      ["commission"]=>
      string(6) "294.27"
      ["created_year"]=>
      string(4) "2014"
      ["created_month"]=>
      string(1) "3"
      ["created"]=>
      string(19) "2014-03-28 12:33:14"
    }
    [2]=>
    array(8) {
      ["id"]=>
      string(4) "5374"
      ["invoice_no"]=>
      string(15) "5374-1396043777"
      ["name"]=>
      string(17) "First M. Last"
      ["affiliate_id"]=>
      string(1) "6"
      ["commission"]=>
      string(6) "122.16"
      ["created_year"]=>
      string(4) "2014"
      ["created_month"]=>
      string(1) "3"
      ["created"]=>
      string(19) "2014-03-28 15:56:17"
    }
    [3]=>
    array(8) {
      ["id"]=>
      string(4) "5378"
      ["invoice_no"]=>
      string(15) "5378-1396044619"
      ["name"]=>
      string(10) "First Last"
      ["affiliate_id"]=>
      string(1) "6"
      ["commission"]=>
      string(6) "100.88"
      ["created_year"]=>
      string(4) "2014"
      ["created_month"]=>
      string(1) "3"
      ["created"]=>
      string(19) "2014-03-28 16:10:19"
    }
    [4]=>
    array(8) {
      ["id"]=>
      string(4) "5372"
      ["invoice_no"]=>
      string(15) "5372-1396031586"
      ["name"]=>
      string(10) "First Last"
      ["affiliate_id"]=>
      string(1) "6"
      ["commission"]=>
      string(6) "294.27"
      ["created_year"]=>
      string(4) "2014"
      ["created_month"]=>
      string(1) "2"
      ["created"]=>
      string(19) "2014-02-28 12:33:06"
    }
  }
  ["total"]=>
  string(6) "912.46"
  ["pending"]=>
  string(4) "0.00"
  ["monthly_totals"]=>
  array(2) {
    [2]=>
    string(6) "294.27"
    [3]=>
    string(6) "618.19"
  }
}

Answer 1

当然可以。我添加了一个 QueryDesc 列来区分查询。

$ret = array(
    'raw' => array(),
    'total' => '0.00',
    'pending' => '0.00',
    'monthly_totals' => array()
);
if (!empty($sales_rep_id)) {
    $raw_query_data = $this->query("
        SELECT
            'detail' query_desc,
            id,
            invoice_no,
            CONCAT(firstname, ' ', lastname) as name,
            affiliate_id,
            commission,
            YEAR(created) as created_year,
            MONTH(created) as created_month,
            created
        FROM orders
        WHERE 
            active = 1 AND
            affiliate_id = {$sales_rep_id}
        UNION ALL
        SELECT
            'total' query_desc,
            NULL id,
            NULL invoice_no,
            NULL name,
            NULL affiliate_id,
            SUM(commission) as commission_total,
            NULL created_year,
            NULL created_month,
            NULL created                    
        FROM orders
        WHERE
            active = 1 AND
            affiliate_id = {$sales_rep_id} 
        UNION ALL
        SELECT
            'monthly_total' query_desc,
            NULL id,
            NULL invoice_no,
            NULL name,
            NULL affiliate_id,
            SUM(commission) as commission_total,
            NULL created_year,
            MONTH(created) as created_month,
            NULL created
        FROM orders
        WHERE 
            active = 1 AND
            affiliate_id = {$sales_rep_id}
        GROUP BY MONTH(created)
        ORDER BY created_month DESC
        ");

    foreach ($raw_query_data as $data) {
        switch ($data['query_desc']) {
            case 'detail':
                unset($data['query_desc']);
                $ret['raw'][] = $data;
                break;
            case 'monthly_total':
                $ret['monthly_totals'][$data['created_month']] = $data['commission'];
                break;
            case 'total':
                $ret['total'] = $data['commission'];
                break;
        }
    }
}
return $ret;

输出 [参考]

array(8) {
  [0]=>
  array(9) {
    ["query_desc"]=>
    string(6) "detail"
    ["id"]=>
    string(4) "5335"
    ["invoice_no"]=>
    string(15) "5335-1395260183"
    ["name"]=>
    string(10) "First Last"
    ["affiliate_id"]=>
    string(1) "6"
    ["commission"]=>
    string(6) "100.88"
    ["created_year"]=>
    string(4) "2014"
    ["created_month"]=>
    string(1) "3"
    ["created"]=>
    string(19) "2014-03-19 14:16:23"
  }
  [1]=>
  array(9) {
    ["query_desc"]=>
    string(13) "monthly_total"
    ["id"]=>
    NULL
    ["invoice_no"]=>
    NULL
    ["name"]=>
    NULL
    ["affiliate_id"]=>
    NULL
    ["commission"]=>
    string(6) "618.19"
    ["created_year"]=>
    NULL
    ["created_month"]=>
    string(1) "3"
    ["created"]=>
    NULL
  }
  [2]=>
  array(9) {
    ["query_desc"]=>
    string(6) "detail"
    ["id"]=>
    string(4) "5378"
    ["invoice_no"]=>
    string(15) "5378-1396044619"
    ["name"]=>
    string(10) "First Last"
    ["affiliate_id"]=>
    string(1) "6"
    ["commission"]=>
    string(6) "100.88"
    ["created_year"]=>
    string(4) "2014"
    ["created_month"]=>
    string(1) "3"
    ["created"]=>
    string(19) "2014-03-28 16:10:19"
  }
  [3]=>
  array(9) {
    ["query_desc"]=>
    string(6) "detail"
    ["id"]=>
    string(4) "5374"
    ["invoice_no"]=>
    string(15) "5374-1396043777"
    ["name"]=>
    string(17) "First M. Last"
    ["affiliate_id"]=>
    string(1) "6"
    ["commission"]=>
    string(6) "122.16"
    ["created_year"]=>
    string(4) "2014"
    ["created_month"]=>
    string(1) "3"
    ["created"]=>
    string(19) "2014-03-28 15:56:17"
  }
  [4]=>
  array(9) {
    ["query_desc"]=>
    string(6) "detail"
    ["id"]=>
    string(4) "5373"
    ["invoice_no"]=>
    string(15) "5373-1396031594"
    ["name"]=>
    string(10) "First Last"
    ["affiliate_id"]=>
    string(1) "6"
    ["commission"]=>
    string(6) "294.27"
    ["created_year"]=>
    string(4) "2014"
    ["created_month"]=>
    string(1) "3"
    ["created"]=>
    string(19) "2014-03-28 12:33:14"
  }
  [5]=>
  array(9) {
    ["query_desc"]=>
    string(13) "monthly_total"
    ["id"]=>
    NULL
    ["invoice_no"]=>
    NULL
    ["name"]=>
    NULL
    ["affiliate_id"]=>
    NULL
    ["commission"]=>
    string(6) "294.27"
    ["created_year"]=>
    NULL
    ["created_month"]=>
    string(1) "2"
    ["created"]=>
    NULL
  }
  [6]=>
  array(9) {
    ["query_desc"]=>
    string(6) "detail"
    ["id"]=>
    string(4) "5372"
    ["invoice_no"]=>
    string(15) "5372-1396031586"
    ["name"]=>
    string(10) "First Last"
    ["affiliate_id"]=>
    string(1) "6"
    ["commission"]=>
    string(6) "294.27"
    ["created_year"]=>
    string(4) "2014"
    ["created_month"]=>
    string(1) "2"
    ["created"]=>
    string(19) "2014-02-28 12:33:06"
  }
  [7]=>
  array(9) {
    ["query_desc"]=>
    string(5) "total"
    ["id"]=>
    NULL
    ["invoice_no"]=>
    NULL
    ["name"]=>
    NULL
    ["affiliate_id"]=>
    NULL
    ["commission"]=>
    string(6) "912.46"
    ["created_year"]=>
    NULL
    ["created_month"]=>
    NULL
    ["created"]=>
    NULL
  }
}