SQL如何完成多張表的報(bào)表?

思路: 先查出注冊(cè)用記的類型, 然后再用戶類型的數(shù)組再循環(huán)訂單中所有的商品.

//語句可查出第一列(用戶類型) 最后一列(金額)
select m.uid,a.id,
                    sum((select 
                        sum((select sum(p.num * p.price) from {$pfix}d p where p.oid = o.id)
                    ) as user_amount
                    from {$pfix}c o where o.uid = m.uid)) as user_order_total
                    from {$pfix}b m 
                    JOIN {$pfix}a a ON m.aid = a.id
                    group by a.id

//查出所以訂單的商品類型
select product_id,product_name from {$pfix}d group by product_id

//循環(huán)出上列表格
        foreach ($data as $key => $value) { //AND o.create_time > 1516204800 下單時(shí)間條件
            $userSql = "select o.id from {$pfix}b m 
                        RIGHT JOIN {$pfix}c o ON o.uid = m.uid where m.aid= {$value ['id']} {$where}
            "; //查出用戶類型下所有用戶訂單
            $userData = $model->query($userSql);
            if(!empty($userData)) {
                
                $userWhere = implode(',', array_column($userData, 'id'));
                foreach ($productData as $k => $v) {
                    $amountSql = "select sum(num) as amount_total from {$pfix}d where product_id = {$v ['product_id']} AND oid IN({$userWhere})";
                    $amount = $model->query($amountSql);
                    $amount = $amount [0]['amount_total'];
                    $data [$key]['product'][$v ['product_id']] = array('amount_total' => $amount);
                }
                dump($productData);
            }
        }

用pivot。
Oracle官方文檔