时间戳

首页 > 解决方案 > 如何获取周和日的总金额sql查询

sql - 如何获取周和日的总金额sql查询

问题描述

此查询仅在我的 Laravel 中显示月份

$personalSale = DB::select(DB::raw("SELECT a.account_id, sum(c.purchase_amount) AS totAmount
        FROM dealer_infos a
        INNER JOIN user_infos b ON a.account_id = b.referrer_id
        INNER JOIN purchases c ON b.user_id = c.user_id
        WHERE  
        (
        month(str_to_date(c.purchase_date, '%d/%m/%Y')) =  MONTH(CURDATE()) AND 
        year(str_to_date(c.purchase_date, '%d/%m/%Y')) =  year(CURDATE())
        )
        GROUP BY a.account_id 
        "));

标签： sqllaravel

解决方案

您目前按月限制它：

month(str_to_date(c.purchase_date, '%d/%m/%Y')) =  MONTH(CURDATE()) and
year(str_to_date(c.purchase_date, '%d/%m/%Y')) =  year(CURDATE())

以下是一周的总和：

week(str_to_date(c.purchase_date, '%d/%m/%Y')) =  week(CURDATE()) and
year(str_to_date(c.purchase_date, '%d/%m/%Y')) =  year(CURDATE())

总天数：

str_to_date(c.purchase_date, '%d/%m/%Y') = CURDATE()

推荐阅读