mysql查询之按年、月分组

在mysql中，数据表的created_at字段类型是timestamp，使用到了date_format()。

现在有以下几个查询场景

一、按照创建时间分组，获取月份、周

SELECT DATE_FORMAT(created_at,'%Y%u')  AS  weeks  AS  count FROM role GROUP BY weeks;

SELECT DATE_FORMAT(create_time,'%Y%m')   AS  months,COUNT(id)   AS  count  FROM role GROUP BY months;

select count(*) as aggregate from `projects` where date_format(passed_at, '%Y') in (2021, 2020)

二、按照年份分组并统计

select sum(mymoney) as totalmoney, count(*) as sheets from mytable group by date_format(col, '%Y');

select sum(mymoney) as totalmoney, count(*) as sheets from mytable group by date_format(col, '%Y-%m');

三、使用laravel框架获取全部年份和月份

$filter_data = $project->selectRaw("date_format(passed_at, '%Y') as year,
                date_format(passed_at, '%m') as month,
                date_format(passed_at, '%u') as week,
                project_type,ownership_body,business_team,
                business_principal_name,income_team,
                income_group,income_principal_name,
                project_manager_name,
                client_archive_property")
            ->groupBy('year',
                'month',
                'week',
                'project_type',
                'ownership_body',
                'business_team',
                'business_principal_name',
                'income_team',
                'income_group',
                'income_principal_name',
                'project_manager_name',
                'client_archive_property')
            ->get()
            ->toArray();

四、使用in查询，获取全部的年份等字段，并去重

$filter_data = $signDetail->join('projects', 'project_sign_details.project_number', '=', '')
            ->selectRaw("
                date_format(signed_at, '%Y') as year,
                date_format(signed_at, '%m') as month,
                projects.project_type,
                projects.business_team,
                projects.business_principal_name,
                projects.client_archive_property
                ")
            ->groupBy(
                'year',
                'month',
                'project_type',
                'business_team',
                'business_principal_name',
                'client_archive_property'
            )
            ->get()
            ->toArray();