如何从多个表中查找一个月内的日常集合

i1icjdpr 于 2021-06-21 发布在 Mysql

关注(0)|答案(2)|浏览(375)

Carts Table structure:
id , invoice_id, product_id, quantity , purchase_price, created_at, updated_at 

Invoices Table structure:
id, customer_id, discount, vat, created_at, updated_at

我试着从上面两张表中找出一个月内每天的总收藏量。在这里，每一张代金券的收款可以按以下方式计算

static function totalPrice($id)
    {
        $itemsPrice = 0;
        $prices = DB::table('invoices')
                    ->join('carts','carts.invoice_id','invoices.id')
                    ->select('purchase_price','quantity')
                    ->where('carts.invoice_id','=',$id)
                    ->get();

        $invoice_price = DB::table('invoices')
                    ->select('vat','discount')
                    ->where('invoices.id','=',$id)
                    ->first();

        foreach ($prices as $price) 
        {
            $itemsPrice = $itemsPrice+ $price->purchase_price*$price->quantity;
        }

        $itemsPrice = $itemsPrice - $invoice_price->discount + (($itemsPrice*$invoice_price->vat)/100);

        return $itemsPrice;
    }

但问题是我想通过查询生成器进行计算。
预期输出如下：

Today        Sum
01-01-2018   1200
02-01-2010   1100
03-01-2018   1200
04-01-2018   1030

以此类推。。
它不能提供正确的输出。但我需要用这种方法来解决。谢谢

$monthly_report_chart = DB::table("invoices")
         ->select( DB::raw("DATE_FORMAT(updated_at, '%d/%m/%Y ') as today"),
                          DB::raw("SUM(collected_today) as sum") 
                        )
         ->groupBy(DB::raw('Date(updated_at)'))
         ->whereMonth('updated_at','=',date('m'))
         ->get();

mysql php laravel query-builder

来源：https://stackoverflow.com/questions/51256875/how-to-find-everyday-collections-in-a-month-from-multiple-tables

2条答案

按热度按时间

fslejnso1#

你可以试试这个，虽然没有测试过
子查询（按发票计算价格组）

$sub = DB::table("invoices")
         ->join('carts','carts.invoice_id','invoices.id')
         ->select('vat', 'discount', DB::raw("SUM(purchase_price * quantity) as totalPrice"), 'updated_at')
         ->groupBy('invoices.id')
         ->whereMonth('updated_at','=',date('m'));

使用子查询计算报表

$reports = DB::table( DB::raw("({$sub->toSql()}) as sub") )
            ->select(
                      DB::raw("DATE_FORMAT(updated_at, '%d/%m/%Y ') as today"), 
                      DB::raw('($totalPrice - discount + (($totalPrice * vat)/100)) as sum')
                  )
            ->mergeBindings( $sub->getQuery() );
            ->groupBy(DB::raw('Date(updated_at)'))
            ->get();

读一下

foreach($reports as $report){
    echo $report->today ." " . $report->sum;
}

赞(0）回复(0）举报 2021-06-21

arknldoa2#

您还需要检查年份以获得正确的结果，否则它也会从其他年份获取结果。
$itemsprice将是 SELECT SUM(purchase_price * quantity) AS price FROM carts WHERE carts.invoice_id = invoices.id 我们可以通过以下方法得到这个结果：

$monthly_report_chart = DB::table("invoices")
         ->select( DB::raw("
            DATE_FORMAT(updated_at, '%d/%m/%Y ') AS date,
            SUM(
                    (
                        SELECT
                            SUM(purchase_price * quantity) AS price
                        FROM
                            carts
                        WHERE
                            carts.invoice_id = invoices.id
                    ) 
                    - discount 
                    + (
                        (
                            SELECT
                                SUM(purchase_price * quantity) AS price
                            FROM
                                carts
                            WHERE
                                carts.invoice_id = invoices.id
                        ) * vat / 100
                    )
            ) AS sum"))
         ->whereMonth('updated_at', '2')
         ->whereYear('updated_at', '2018')
         ->groupBy('date')
         ->get();

但是我们可以改进查询，我们不应该在查询中两次获取$itemsprice，所以让我们做一些计算：
如果 x = itemsPrice , y = discount 以及 z = vat 然后

Formula      x - y+((x * z)/100)
is equal to  ((x - y)100 + (x * z))/100
is equal to  (100x - 100y + x*z)/100
is equal to  (x(100+z) - 100y)/100

自 x-y+((xz)/100) 等于 (x(100+z) - 100y)/100 然后查询将被删除。

$monthly_report_chart = DB::table("invoices")
         ->select( DB::raw("
            DATE_FORMAT(updated_at, '%d/%m/%Y ') AS date,
            SUM(
                (
                    (
                        SELECT
                            SUM(purchase_price * quantity) AS price
                        FROM
                            carts
                        WHERE
                            carts.invoice_id = invoices.id
                    ) *(100 + vat) - 100 * discount
                ) / 100
            ) AS sum"))
         ->whereMonth('updated_at', '2')
         ->whereYear('updated_at', '2018')
         ->groupBy('date')
         ->get();

ps：我还没有测试过，所以如果有什么问题请告诉我。

赞(0）回复(0）举报 2021-06-21

我来回答

如何从多个表中查找一个月内的日常集合

2条答案

相关问题

热门标签

最新问答